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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 10:28 AM To: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
