>> My understanding has always been "if the DB is using a timezone other than
>> your VM default, you must specify a Calendar that has the DB's timezone".
>> This seems to work, but it seems kludgy and I am unable to figure out WHY
>There are some fairly high-profile database systems out there that do
>not store any timezone information in timestamp columns. It's not
>required by the SQL standard.
Agreed, but JDBC's methodology seems bizarre even so.
>The Calendar passed to get/setDate is
>used to determine the timezone of the stored date, not the timezone of
>the app or the timezone of the DB server's system clock, for cases
>where all dates are stored using some fixed timezone that may not
>correspond to the app's default timezone.
That may be what it was intended for, but that's not what it does. It's possible that this is just a broken part of JDBC, but it seems more likely that I'm misunderstanding something.
The DB doesn't store the TZ of that calendar, the driver just translates the
stored datestamp by (Calendar TZ - Server TZ). The server doesn't store
timezone but it HAS a global timezone. Using a Calendar with a TZ other
than the server's timezone gives you date columns that cannot be compared to
sysdate and friends.
>For example, you may have mandated that DB dates are always in UTC, in
>which case if you want to store 2008-01-01 07:00:00 -0500 in the
>database you would pass in a Date representing that time and a
>calendar whose timezone was UTC, and allow the driver to adjust
>appropriately. The driver or the DB would be required to store
>2008-01-01 12:00:00 as its (timezoneless) timestamp.
If you do this, then at 9am in New York on Jan 1 2008, you will
select FOO from BAR where sysdate < TIMESTAMPCOL
and be very surprised that this row appears. You wanted 7am but got noon!
--
Mark Rafn dagon@dagon.net <http://www.dagon.net/>