Railo SQL, Amazon Cloud and Time Zones

We've been working today with localizing time stamps, where a web server, a database server, and the client using the application are all in different time zones respectively.

Setup:

1. Select the time zone in Railo Admin to match the time zone of your server's OS.  Server Administrator tab -> Regional -> Time Zone pull down
2. Configure your data source's time zone. Datasource -> [ Pencil beside data source ] -> Time Zone.  ( For Amazon Web Services, all RDS instances are UTC )
3. In our case, we're specifying in the users account what time zone they are in, so we can use the underlying java.util.TimeZone

The test:

Doing a sql insert of railo's "#now()#" timestamp, and reading it again from the database should be the same value, as well, the now() timestamp should be the same in the database as returned from your browser when output. ( They should all be in sync.. and do their dark magic across time zones properly and seamlessly )
 

From Michael Offner See the Railo blog post here

"If Railo has set the the Timezone CST and the DB Server is running in PST you run into problems for a very simple reason. the Timestamp produced ({ts '2012-07-25 16:20:14'}) is based on the CST Timezone, this means it is "2012-07-25 16:20:14 CST", BUT the TimeZone information is not present in the timestamp.
so if the DB server is reading the string timestamp from the SQL String, it has no timezone information and the DB Server simply takes it own timezone for the timestamp, because it has no idea from whitch timezone the request is coming from, this means the db server interpret the timestamp as "2012-07-25 16:20:14 PST" what is 2 hours later.
then when you get this date back you get "2012-07-25 18:20:14 CST" what is exactly the same as "2012-07-25 16:20:14 PST", this means the failure happens when you send the string timestamp to the db server in a different timezone without the timezone information. this conversion problem does not happen when you get the date back via a select statement, simply because in this case the date is send in a timezone independent format."

Notes: 

• The dateTime datatype in sql server does not have time-zone information included: http://msdn.microsoft.com/en-us/library/ms187819.aspx