db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mikael" <mikael-arons...@telia.com>
Subject Re: timestamp question
Date Wed, 03 Sep 2008 07:54:34 GMT
Ok, thank's, I tried a few things and I think I got it working.

If I convert any local time to UTC in an SQL query string
and use an UTC calendar in getTimestamp( x, cal) and
setTimestamp( x, cal) I think I got it working.

The time specified as a string in a query is used as it is,

setTimestamp with an UTC calendar converts the
local time in timestamp to UTC and saves it.

getTimestamp with an UTC calendar converts the
UTC time in the resultset to local time.

With this setup everything should be UTC time
in the server and then I can convert it to local
(norwegian or swedish ;o) time when it arrives
to the client.

----- Original Message ----- 
From: "Dag H. Wanvik" <Dag.Wanvik@Sun.COM>
To: "Derby Discussion" <derby-user@db.apache.org>
Sent: Wednesday, September 03, 2008 2:10 AM
Subject: Re: timestamp question

> Mikael <mikael-aronsson@telia.com> writes:
>> I have googled this a bit and found some information but it does not
>> make it much easier to figure out.
> Derby does not store timezone information.
> Cf this thread:
> http://mail-archives.apache.org/mod_mbox/db-derby-user/200803.mbox/%3c47D342A9.9080806@apache.org%3e
> I tried with the two Derby drivers and verified that the timesstamps
> are returned as stored (and are not modified when setting through JDBC
> either), even if you supply a Calendar argument which has a timezone
> different than UTC (I have "Europe/Oslo" which is UTC+2 right now). I
> am not sure the behavior with Calendar argument is what you want
> though.. seems to me it should make a difference; from the Javadoc
> (Java 1.5):
> void setTimestamp(int parameterIndex,
>                  Timestamp x,
>                  Calendar cal)
>                  throws SQLException
>    Sets the designated parameter to the given java.sql.Timestamp
>    value, using the given Calendar object. The driver uses the
>    Calendar object to construct an SQL TIMESTAMP value, which the
>    driver then sends to the database. With a Calendar object, the
>    driver can calculate the timestamp taking into account a custom
>    timezone. If no Calendar object is specified, the driver uses the
>    default timezone, which is that of the virtual machine running the
>    application.
> It does somewhat defensively say: "it *can* calculate", not "should", 
> though...
>> Assume I have a Derby server on one computer and two clients running
>> on two different computers and they
>> all have different timezones.
>> If i use setTimestamp( x), getTimestamp(x) and "yyyy-mm-dd hh:mm:ss.n"
>> in querys, will this work as expected ?
> Yes.
>> Or if I use setTimestamp( x) in client A, save the record to the
>> database and get the record to client B and use
>> getTimestamp( x), will the returned time be correct in the clients
>> local timezone ?
> Not if the zones differ, you'd have to adjust it yourself.
> Hope this helps.
> Dag
>> I did find some information indicating that the server stores
>> timestamps in servers local time, if this is true
>> and I move the database to another computer (different timezone) or
>> change the timezone in the server
>> everything will be messed up, is this true or not ?
>> I am just trying to figure out how I should use Derby in an enviroment
>> where the server and the clients are
>> in different timezones.
> -- 
> Dag H. Wanvik, staff engineer
> Sun Microsystems, Databases (JavaDB/Derby)
> Haakon VII gt. 7b, N-7485 Trondheim, Norway
> Tel: x43496/+47 73842196, Fax:  +47 73842101 

View raw message