Moving a Derby database across timezones

[Alan Burlison <Al...@sun.com>]

I have been looking at the derby documentation for TIMESTAMP, and it's 
pretty confusing when it comet to try to figure what will happen if I 
move a database onto a machine in a different timezone.

http://db.apache.org/derby/papers/JDBCImplementation.html#Derby+SQL+TIMESTAMP

----------
Derby’s SQL TIMESTAMP type represents a time of day in the form 
yyyy-mm-dd hh:mm:ss.fffffffff (nanosecond granularity) with no 
associated time zone information.

java.sql.Timestamp

A JDBC Timestamp (java.sql.Timestamp) by definition represents a point 
in time, with nanosecond resolution, in a given time zone.
----------

So what does "no associated timezone information" mean?  Does Derby 
normalize all timestamps to UTC, or does it just throw away the timezone 
information altogether?   If it is the second, that makes it impossible 
to move databases across timezones without having to recalculate any 
timestamps it uses.

Ideally I'd like all times to be stored by Derby in UTC, but I can't set 
the JVM timezone to UTC as the Derby database is running inside Tomcat, 
and such a change would affect *all* the webapps running in the web 
container.

-- 
Alan Burlison
--

Re: Moving a Derby database across timezones

[Alan Burlison <Al...@sun.com>]

Bryan Pendleton wrote:

>>> of the SQL standard foundation document) Derby's TIMESTAMP is
>>>
>>>   TIMESTAMP WITHOUT TIME ZONE
>>
>> Yeah, I figured as much.  The spec gives you a choice of with/without 
>> timezone, and Derby implements the broken one. 
> 
> Perhaps you could contribute an implementation of the other style
> of timestamp, then Derby would have both.

Perhaps, if I had time.  Unfortunately I don't.

-- 
Alan Burlison
--

Re: Moving a Derby database across timezones

[Bryan Pendleton <bp...@amberpoint.com>]

>> of the SQL standard foundation document) Derby's TIMESTAMP is
>>
>>   TIMESTAMP WITHOUT TIME ZONE
> 
> Yeah, I figured as much.  The spec gives you a choice of with/without 
> timezone, and Derby implements the broken one. 

Perhaps you could contribute an implementation of the other style
of timestamp, then Derby would have both.

thanks,

bryan

Re: Moving a Derby database across timezones

[Alan Burlison <Al...@sun.com>]

Daniel John Debrunner wrote:

> It is the second, which is defined by the SQL standard which has 
> datetime values with and without timezone. In SQL terms (section 4.6 of 
> the SQL standard foundation document) Derby's TIMESTAMP is
> 
>   TIMESTAMP WITHOUT TIME ZONE

Yeah, I figured as much.  The spec gives you a choice of with/without 
timezone, and Derby implements the broken one.  How dumb.

-- 
Alan Burlison
--

Re: Moving a Derby database across timezones

[Daniel John Debrunner <dj...@apache.org>]

Alan Burlison wrote:
> I have been looking at the derby documentation for TIMESTAMP, and it's 
> pretty confusing when it comet to try to figure what will happen if I 
> move a database onto a machine in a different timezone.
> 
> http://db.apache.org/derby/papers/JDBCImplementation.html#Derby+SQL+TIMESTAMP 
> 
> 
> ----------
> Derby’s SQL TIMESTAMP type represents a time of day in the form 
> yyyy-mm-dd hh:mm:ss.fffffffff (nanosecond granularity) with no 
> associated time zone information.
> 
> java.sql.Timestamp
> 
> A JDBC Timestamp (java.sql.Timestamp) by definition represents a point 
> in time, with nanosecond resolution, in a given time zone.
> ----------
> 
> So what does "no associated timezone information" mean?  Does Derby 
> normalize all timestamps to UTC, or does it just throw away the timezone 
> information altogether?   If it is the second, that makes it impossible 
> to move databases across timezones without having to recalculate any 
> timestamps it uses.

It is the second, which is defined by the SQL standard which has 
datetime values with and without timezone. In SQL terms (section 4.6 of 
the SQL standard foundation document) Derby's TIMESTAMP is

   TIMESTAMP WITHOUT TIME ZONE

Dan.