Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsWhite Papers
Discussion GroupsFirst AidDatabasesJavaBeansGUIJava 3DVirtual MachineCORBASecurityToolsGeneral
Java DirectoryOpen Source ProjectsSample Book ChaptersUser GroupsWeb Resources
Related Topics
Databases.NETMore Topics ...

Java Forum / General / December 2007

Tip: Looking for answers? Try searching our database.

JDBC - when do I need a Calendar for get/set Timestamp?

Thread view: 
Mark Rafn - 27 Dec 2007 21:23 GMT
For something I've been doing for so long, this is an embarassing question to
ask.  The getters on ResultSet and setters on PreparedStatement for
time-related types can take an optional Calendar.  

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 this
is needed.

1) Is this the correct rule?

2) Why is this necessary?  Shouldn't there be a Connection-level setting (or
better, the driver should detect it) for this, rather than making each call
decide what timezone the DB might be in?
--
Mark Rafn    dagon@dagon.net    <http://www.dagon.net/>
Owen Jacobson - 27 Dec 2007 21:39 GMT
> For something I've been doing for so long, this is an embarassing question to
> ask.  The getters on ResultSet and setters on PreparedStatement for
[quoted text clipped - 10 lines]
> better, the driver should detect it) for this, rather than making each call
> decide what timezone the DB might be in?

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.  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.

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 omit the Calendar option, the Date passed will be stored in the
current default timezone (and the example above would store 2008-01-01
07:00:00 instead).

-o
Mark Rafn - 27 Dec 2007 22:42 GMT
>> 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/>
Roedy Green - 28 Dec 2007 13:53 GMT
>2) Why is this necessary?

Recall the ancient roots of SQL.  I goes back at least to the 70s.
This was back in the days when servers ran on local time and all
terminals displayed server time.

The database then did not concern itself with timezones.  It just used
local time.

It sounds like this is a kludge to avoid any translation of the
timestamp.

If we were designing this today, the database would store all
timestamps in UTC, and when you were preparing a display,  the
user-timezone would automatically be used to adjust all dates.

As part of login, you would get the timezone, language, encoding etc
the user prefers, perhaps part of the login database.

Signature

Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com

Mark Rafn - 28 Dec 2007 17:20 GMT
>Recall the ancient roots of SQL.  I goes back at least to the 70s.
>This was back in the days when servers ran on local time and all
>terminals displayed server time.

Yes, I was there :)

>The database then did not concern itself with timezones.  It just used
>local time.  It sounds like this is a kludge to avoid any translation of the
>timestamp.

My confusion is that this kludge seems worse than it needs to be.  There
_IS_ a right thing to do, and I want to know why JDBC drivers don't do it.
Because if I don't understand that, I'm tempted to "fix" it in my
infrastructure layer, and I'll do it wrong if there's a good reason I'm
not aware of for this kludge.

The driver could know the server timezone, and translate Java's point-in-time
semantics into server wallclock-time semantics with complete transparency.
And it doesn't!  It makes me TELL it the timezone with each call because the
default is ALWAYS wrong (when the JVM and DB are in different timezones).
--
Mark Rafn    dagon@dagon.net    <http://www.dagon.net/>
Mark Thornton - 28 Dec 2007 17:51 GMT
> The driver could know the server timezone, and translate Java's point-in-time
> semantics into server wallclock-time semantics with complete transparency.
> And it doesn't!  It makes me TELL it the timezone with each call because the
> default is ALWAYS wrong (when the JVM and DB are in different timezones).

There are no doubt servers which do not provide a way for a client to
determine the timezone of the server. I don't think there is a generic
way to do this from ODBC for example, so requiring knowledge of the
server's timezone would make a generic JDBC/ODBC bridge impossible.

Mark Thornton
Mark Rafn - 28 Dec 2007 17:59 GMT
>> The driver could know the server timezone, and translate Java's
>> point-in-time semantics into server wallclock-time semantics with
>> complete transparency.

>There are no doubt servers which do not provide a way for a client to
>determine the timezone of the server. I don't think there is a generic
>way to do this from ODBC for example, so requiring knowledge of the
>server's timezone would make a generic JDBC/ODBC bridge impossible.

Even in that case, the API could allow a driver property to be set by the
developer/deployer.  This would be a massive improvement over setting it in
the getters and setters via a Calendar that exists only to provide a TimeZone.
--
Mark Rafn    dagon@dagon.net    <http://www.dagon.net/>
Mark Thornton - 28 Dec 2007 20:46 GMT
>>> The driver could know the server timezone, and translate Java's
>>> point-in-time semantics into server wallclock-time semantics with
[quoted text clipped - 10 lines]
> --
> Mark Rafn    dagon@dagon.net    <http://www.dagon.net/>  

When databases don't record timezones with date/times, the implied
timezone may be different from table to table or even column to column.
I.e. some columns may represent date/time in UTC while others are in
local wall time (for some local). I know databases like this.

Mark Thornton


Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.