db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sica, David (David)" <ds...@avaya.com>
Subject RE: Time Zone with Torque and PostgreSQL
Date Wed, 07 Apr 2004 01:02:49 GMT
Actually, I have successfully implemented options 1) and 3).  I think
maybe option 3) might be the "better" option because timestamp with time
zone is only good through AD 2037 and it doesn't involve messing with
any Torque files.  

However, that said, I'm leaning towards using option 1) because it's
easier from the application standpoint and less prone to error if the
jvm's timezone is not set to UTC.

Thanks for your input!
David

-----Original Message-----
From: Benjamin Yu [mailto:BenjaminY@InformativeResearch.com] 
Sent: Tuesday, April 06, 2004 8:57 PM
To: 'Apache Torque Users List'
Subject: RE: Time Zone with Torque and PostgreSQL


I'm not sure if option 2 will work for you...because new Date() and new
Date(new GregorianCalendar().getTimeInMillis())
will create a new object of the same time.

Java API states that Date "is intended to reflect coordinated universal
time (UTC)". To which the Default constructor "initializes it so that it
represents the time at which it was allocated". And "the DateFormat
class should be used to format and parse date strings"

The java.text.DateFormat uses the values from TimeZone.getDefault() and
the value set by dateFormat.setTimeZone() to correctly output a string
with the proper timezone adjustments.

I believe that the client's "localtime" will be inserted into the table
when inserting a new Date() regardless of whatever timezone the server
is in. This effect appears to happen also with ms sql server's datetime.

>1) I could use the PostgreSQL datatype "timestamp with time zone" 
>though  I'm wondering if/how Torque will handle this datatype?

The default postgresql templates just use the "timestamp" datatype.
Thus, there will be no timezone conversion. However, one could modify
the torque-gen-3.1\templates\sql\base\postgresql\db.props file to add
"with time zone".

There's always option 3) set the jvm's timezone to utc in the
environment.

However, I'm not sure which one of the options that i discussed to
advocate.
:)  But I am leaning towards option one... to make the timestamp columns
in the database timezone aware...

-Ben

-----Original Message-----
From: Sica, David (David) [mailto:dsica@avaya.com]
Sent: Tuesday, April 06, 2004 10:28 AM
To: torque-user@db.apache.org
Subject: Time Zone with Torque and PostgreSQL


I have an application that could be running on servers in different time
zones.  I'm storing several columns of data in PostgreSQL using the
timestamp datatype.  I now realize that I need to record the time zone
as part of the timestamp as this application could be inserting into the
database from different time zones.  I'm trying to determine the best
way to handle this situation.  Here's some thoughts:

1) I could use the PostgreSQL datatype "timestamp with time zone" though
I'm wondering if/how Torque will handle this datatype?

2) I could always store the timestamp in UTC by calling new
Calendar().getTimeInMillis() and setting this value in my Torque data
object.

Are there any comments or suggestions for implementation?

Thanks,
David Sica


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message