Timezone changes when running a cfquery select

[Tanner Bachman]

Hi Gurus!

I'm having an issue that I think might be a bug and was wondering if anyone has any input for me.

I'm running a simple program that logs information to a MySQL database.  The DB server and Web/App server are different boxes, both running Ubuntu 10.04 x64.  Both of the OS's have the correct timezone set as Pacific Standard Time.

The main Railo Server context has the regional timezone set as America/Los Angeles (PST).  I have a client (on his own web context) that has the timezone setting of America/Chicago (so Central Standard Time for him).

The problem occurs when pulling data from the MySQL DB.  One of the tables has a column called "create_date" with a datatype of "datetime".  I use a simple insert query to insert data like so:

<cfquery datasource="mydb" name="mytest">
INSERT INTO my_table(create_date)
VALUES(#createodbcdatetime(now())#)
</cfquery>

I'm actually located in the PST timezone and when inserting a record, the time is reflected correctly as exactly 2 hours ahead of me (so CST) which works as it should.  However, when I run a simple select statement and then output the time it also adds an additional 2 hours (timezone adjustment) to what's already been inserted into the DB.  So essentially, Railo is appending another 2 hours to my time when pulling it from the DB.

Let's say it's 6:00 AM PST my time and I'm running the above SQL statement.  I look at the inserted data using MySQL Workbench and it shows the time as 8:00 AM.  This is correct as my client is 2 hours ahead of me and that's what he wants to see.
When I pull that out and display it, using a simple timeformat(my_sql_var, "h:mm tt") I get 10:00 AM...2 hours ahead of my clients timezone settings.

I also ran a simple timeformat(now(), "h:mm tt") and it displays the time correctly...2 hours ahead of me in recognition of my clients timezone setting.

At this point it's something either in the DB server or Railo and I'm wondering if anyone has run into this before.

Thanks in advance for all of your help!

Tanner

[Michael Offner]

this is a common missunderstanding we already have a solution for it,

but let me first explain what exactly is happening:

with this code:

<cfquery datasource="mydb" name="mytest">
INSERT INTO my_table(create_date)
VALUES(#createodbcdatetime(now())#)
</cfquery>

you send a SQL statement like this to the DB Server

INSERT INTO my_table(create_date)
VALUES({ts '2012-07-25 16:20:14'})

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.

how to solve?

the best solution is to send the date in a timezone independent format, for example this way

<cfquery datasource="mydb" name="mytest">
INSERT INTO my_table(create_date)

VALUES(<cfqueryparam value="#now()#" cfsqltype="CF_SQL_TIMESTAMP">)
</cfquery>

of course this solution perhaps need mny changes in existing code, so we did a better solution for this.

in the Railo admin go to your datasource declaration, in the detail view of your datasource, you can find the point "Timezone", simply set this to your DB Timezone and yo are fine!

Of course this has no affect on dates already stored, they are wrong in the db.

this setting will change the Environment timezone inside cfquery.

in addition you can also use the attribute "timezone" with the tag cfquerty:

<cfquery datasource="mydb" name="mytest" timezone="PST">

INSERT INTO my_table(create_date)
VALUES(#createodbcdatetime(now())#)
</cfquery>

/micha

2012/7/24 Tanner Bachman <tanner....@gmail.com>

--

/micha

Michael Offner CTO Railo Technologies GmbH

[James Kilford]

Wow!  That's amazing!

[Tanner Bachman]

Micha,

Thanks for your quick response and explanation of how this works.  Initially I had left the Datasource timezone as "---same as Railo instance---", but once I modified it to PST it worked as it should.

Here were the final settings that worked for me:

1) Railo instance Regional Timezone: America/Chicago (CST)
2) Datasource Timezone: America/Los_Angeles (PST)

Thanks for your help on this one...I very much appreciate it!

Tanner