Year, Month, Day functions

[Tim]

Hello Everyone

I'm sure that there is a simple answer to this but I was wondering if
there was an elegant way to change the definition of the functions like
year, month and day such that they are effectively run for a different
time zone without changing the server time/timezone. When you run say:

select day(transaction_time),
count(*)
from transaction
where transaction_time >= current timestamp - 10 days

it will use the current timezone. So if we have two machines one in
London and one in NY and we run the same query on each server we'd get
different results due to NY time being 5 hours behind London time. Or
as in our case we have a single server handling clients in multiple
timezones each of which want to have reports come out in their local
time.

One way to do this is some time arithmetic before passing it on to the
date functions (i.e. day(transaction_time - 5 hours) but that wouldn't
let DB2 use the indexes defined on transaction_time assuming there was
one IIRC.

Has anyone got any ideas how this can be done?

thanks in advance
tim

[Gert van der Kooij]

In article <11**********************@g47g2000cwa.googlegroups .com>,
Tim (ku******@gmail.com) says...

Hello Everyone

I'm sure that there is a simple answer to this but I was wondering if
there was an elegant way to change the definition of the functions like
year, month and day such that they are effectively run for a different
time zone without changing the server time/timezone. When you run say:

select day(transaction_time),
count(*)
from transaction
where transaction_time >= current timestamp - 10 days

it will use the current timezone. So if we have two machines one in
London and one in NY and we run the same query on each server we'd get
different results due to NY time being 5 hours behind London time. Or
as in our case we have a single server handling clients in multiple
timezones each of which want to have reports come out in their local
time.

One way to do this is some time arithmetic before passing it on to the
date functions (i.e. day(transaction_time - 5 hours) but that wouldn't
let DB2 use the indexes defined on transaction_time assuming there was
one IIRC.

Has anyone got any ideas how this can be done?

thanks in advance
tim

Maybe you can do some arithmetic using the CURRENT TIMEZONE value?

[Tim]

Hi Gert

What did you have in mind for this? CURRENT TIMEZONE isn't something
that can be updated and I would've thought that doing column level
arithmetic would prevent DB2 from using indexes on that column? I seem
to recall using column functions having this effect.

thanks
tim

[Brian Tkatch]

I would say the database design does not match the requirements. The
requirements are global consistency, where the design is for
region-based timings. To me, the answer would be to store times in GMT,
so the query would return the same result set no matter where it has
been executed. It would then be up to the application to tranlate it
into local time.

OTOH, perhaps a COLUMN could be ADDed to the TABLE defined as GENERATED
ALWAYS AS day(transaction_time - 5 hours). An INDEX would then be added
to the GENERATED COLUMN.

If no changes are to be made, the query could be changed to use a
BETWEEN operator, without using DAY() at all.

[Gert van der Kooij]

In article <11**********************@g49g2000cwa.googlegroups .com>,
Tim (ku******@gmail.com) says...

Hi Gert

What did you have in mind for this? CURRENT TIMEZONE isn't something
that can be updated and I would've thought that doing column level
arithmetic would prevent DB2 from using indexes on that column? I seem
to recall using column functions having this effect.

thanks
tim

Hi Tim,
You can use the CURRENT TIMEZONE variable to insert the GMT (or UTC
as it is being called now) into the database. Offcourse you also need
to take care of adding it when retrieving the date again but it makes
it a lot easier to compare them.

kind regars, Gert