By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

Year, Month, Day functions

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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?
Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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.

Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.