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" <[EMAIL PROTECTED]>
To: "Derby Discussion" <[email protected]>
Sent: Wednesday, September 03, 2008 2:10 AM
Subject: Re: timestamp question
Mikael <[EMAIL PROTECTED]> 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/[EMAIL
PROTECTED]
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
