You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Mikael <mi...@telia.com> on 2008/09/02 11:56:56 UTC
timestamp question
I have googled this a bit and found some information but it does not make it
much easier to figure out.
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 ?
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 ?
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.
Re: timestamp question
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Mikael <mi...@telia.com> writes:
> 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.
Yes, this is correct. The test I referred to in my previous post used
an explicit calendar object which just represented the current time
zone and this made no difference. A calendar object explicitly created
for another timezone (e.g. UTC), does make a difference.
Dag
Re: timestamp question
Posted by Mikael <mi...@telia.com>.
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" <Da...@Sun.COM>
To: "Derby Discussion" <de...@db.apache.org>
Sent: Wednesday, September 03, 2008 2:10 AM
Subject: Re: timestamp question
> Mikael <mi...@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
Re: timestamp question
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Mikael <mi...@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