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

[Mark Rafn]

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?

 

[Owen Jacobson]

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?

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]

My understanding has always been "if the DB is using a timezone other than

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!

 

[Roedy Green]

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.

 

[Mark Rafn]

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 Thornton]

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]

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 Thornton]

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.

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