Stillnet Studios

I ran into an interesting issue today. Our database timestamps are all stored in UTC. So when a user runs a report from a webpage, we convert the time they entered into a UTC timestamp. If we didn’t do this, the report may miss some records.

For example, a customer buys a product at 11:00pm our time on the 1st of the month. The timestamp in the database for this record will be 5:00am on the 2nd, since we are -6 hours from UTC here in the Central timezone.

So we modify timestamps in our reporting queries like this:

...  WHERE
order.createDate > <cfqueryparam value="#DateConvert('local2utc', arguments.startDate)#" cfsqltype="cf_sql_timestamp">
AND
order.createDate < <cfqueryparam value="#DateConvert('local2utc', arguments.endDate)#" cfsqltype="cf_sql_timestamp">

There is an issue with this in CF10. Take this code:

<cfset arguments.startDate = CreateDateTime(2012,1,1,00,00,00) />
<cfquery>
...
WHERE order.createDate > <cfqueryparam value="#DateConvert('local2utc', arguments.startDate)#" cfsqltype="cf_sql_timestamp">
....
</cfquery>
<cflog text="I think I passed #DateConvert('local2utc', arguments.startDate)# to the database" />

If you look in your log you’ll see I think I passed {ts '2012-01-02 6:00:00'} to the database (since I am -6 from UTC). But if you look in the debugging information for this query, or use SQL Server Profiler to view it, you’ll see it gets sent to the database as 2012-01-01 00:00:00:000!

My guess is that the DateConvert() method doesn’t actually change the value. Instead it does something like adjust the timezone or offset inside the date object. And I think the cfqueryparam tag isn’t paying attention to whatever DateConvert has changed. I further confirmed this by trying to add .toString() on the method result, inside the cfqueryparam tag. This causes the expected behavior.

After tracking this down my coworker Jason Troy discovered there is a bug report for this already. From reading through these bug reports it doesn’t sound like Adobe is going to make any changes, however. They do point out that you can set this JVM argument to return to the previous behavior of CF9: -Dcoldfusion.preserveTimeZoneOnPersist=true.

More info:
https://bugbase.adobe.com/index.cfm?event=bug&id=3338974
https://bugbase.adobe.com/index.cfm?event=bug&id=3347145

So for now what I’ve done is change my code to this:

...  WHERE
order.createDate > <cfqueryparam value="#DateConvert('local2utc', arguments.startDate).toString()#" cfsqltype="cf_sql_timestamp">
AND
order.createDate < <cfqueryparam value="#DateConvert('local2utc', arguments.endDate).toString()#" cfsqltype="cf_sql_timestamp">

You may wonder what’s the reason for this change in behavior? It’s alluded to in the bug reports above. I think there is a different way we are supposed to be doing this now, I haven’t got it completely figured out yet. One of the bug report comments from Himavanth Rachamsetty, who is on ColdFusion team, says this:

The current CF 10 behavior included in the update 4 is correct because when you move the time from one timezone to another, date.getTime() should not change at all.
The way date object is defined in Java, the time elapsed since Epoch should not change at all no matter which timezone the date is in. CF 9 and the earlier versions were violating this definition of Java date and were changing the time elapsed since epoch. This caused few issues which we fixed in CF 10.
When the date is persisted and retrieved from DB, the JVM automatically converts it back to local timezone. While retrieving a date, we don’t have any mechanism to find out that a date was converted to UTC when it was first inserted and therefore we would not be able to do any conversion ourselves. It is actually the application’s responsibility to convert the date/time to appropriate timezone after it is retrieved. Another option to work with date and DB is – you always store date as string in DB and when it is retrieved, convert it back to date.
Since this change of behavior in CF 10 could break a lot of applications, we have fixed this by introducing a flag so that the applications can run without any issues. Since this flag changes the way date is defined in Java, we will keep it disabled by default.