Strange Happenings with UTC Conversions - BEWARE

Report · Feb 08, 2017

This is less a request for help and more a beware for the unexpected...

I’m using CF11 and encountered a strange issue when trying to use UTC timestamps for comparison. It appears there is a hidden time zone indicator associated with CFs datetime object -- and I’m guessing it with the underlying Java datetime object. If all datetime comparisons happens during the request and no string conversion or database storage of the datetime objects happen, no issues. But if you convert a UTC converted datetime object to a string or store it in a database for a future comparison, you will receive some unexpected result as the comparison seems to take into account the hidden time zone and this field is not preserved when converted to a string or retrieved from a database. Example:

local.dttm1 = createDateTime(2017,02,01,01,00,00);

local.dttm1UTC = dateConvert('local2utc', local.dttm1);

local.dttm2 = createDateTime(2017,02,01,02,00,00);

local.dttm2UTC = dateConvert('local2utc', local.dttm2);

local.dttm3 = createDateTime(2017,02,01,03,00,00);

local.dttm3UTC = dateConvert('local2utc', local.dttm3);

local.dttm2a = parseDateTime("#dateTimeFormat(local.dttm2,'yyyy-mm-dd hh:nn:ss')#");

local.dttm2aUTC = parseDateTime("#dateTimeFormat(local.dttm2UTC,'yyyy-mm-dd hh:nn:ss')#");

</cfscript>

local.dttm1UTC = #local.dttm1UTC#

local.dttm2UTC = #local.dttm2UTC#

local.dttm3UTC = #local.dttm3UTC#

local.dttm1UTC < local.dttm2UTC = SHOULD BE YES

#local.dttm1UTC# < #local.dttm2UTC# = #local.dttm1UTC LT local.dttm2UTC#

local.dttm2UTC < local.dttm3UTC = SHOULD BE YES

#local.dttm2UTC# < #local.dttm3UTC# = #local.dttm2UTC LT local.dttm3UTC#

local.dttm2aUTC = #local.dttm2aUTC#

local.dttm1UTC < local.dttm2aUTC = SHOULD BE YES

#local.dttm1UTC# < #local.dttm2aUTC# = #local.dttm1UTC LT local.dttm2aUTC#

local.dttm2aUTC < local.dttm3UTC = SHOULD BE YES

#local.dttm2aUTC# < #local.dttm3UTC# = #local.dttm2aUTC LT local.dttm3UTC#

</cfoutput>

Here are my results, depending on your time zone, your mileage may vary:

local.dttm1UTC = {ts '2017-02-01 09:00:00'}
local.dttm2UTC = {ts '2017-02-01 10:00:00'}
local.dttm3UTC = {ts '2017-02-01 11:00:00'}

local.dttm1UTC < local.dttm2UTC = SHOULD BE YES
{ts '2017-02-01 09:00:00'} < {ts '2017-02-01 10:00:00'} = YES

local.dttm2UTC < local.dttm3UTC = SHOULD BE YES
{ts '2017-02-01 10:00:00'} < {ts '2017-02-01 11:00:00'} = YES

local.dttm2aUTC = {ts '2017-02-01 10:00:00'}

local.dttm1UTC < local.dttm2aUTC = SHOULD BE YES
{ts '2017-02-01 09:00:00'} < {ts '2017-02-01 10:00:00'} = YES

local.dttm2aUTC < local.dttm3UTC = SHOULD BE YES
{ts '2017-02-01 10:00:00'} < {ts '2017-02-01 11:00:00'} = NO

I think this is a CF bug as I don’t believe the datetime comparison should apply a hidden time zone indicator. This took me a few hours and was a bear to diagnose as I was saving the UTC converted datetime in a database and later comparing it to a newly converted UTC value.

If I’m doing something wrong here, let me know. Otherwise I’ll report this as a bug.

Report · Feb 09, 2017

I dont see the same results when I try.

Using your exact code i get the following:

local.dttm1UTC = {ts '2017-02-01 01:00:00'}
local.dttm2UTC = {ts '2017-02-01 02:00:00'}
local.dttm3UTC = {ts '2017-02-01 03:00:00'}
local.dttm1UTC < local.dttm2UTC = SHOULD BE YES
{ts '2017-02-01 01:00:00'} < {ts '2017-02-01 02:00:00'} = YES
local.dttm2UTC < local.dttm3UTC = SHOULD BE YES
{ts '2017-02-01 02:00:00'} < {ts '2017-02-01 03:00:00'} = YES
local.dttm2aUTC = {ts '2017-02-01 02:00:00'}
local.dttm1UTC < local.dttm2aUTC = SHOULD BE YES
{ts '2017-02-01 01:00:00'} < {ts '2017-02-01 02:00:00'} = YES
local.dttm2aUTC < local.dttm3UTC = SHOULD BE YES
{ts '2017-02-01 02:00:00'} < {ts '2017-02-01 03:00:00'} = YES

Tested this on all versions of CF and Lucee and get the same.

Tried changing the initial times incase it was to do specifically with the time in the last check but it also works fine. Problem is I am UTC at the moment, so nothing is really converting behind the scenes, but I am assuming it still goes through the motions.

I do know though that using EQ, LT, GT etc for anything other then numbers can get flakey. Maybe use DateCompare or DateDiff will give more accurate results.

Report · Feb 09, 2017

I'm fairly certain that if you are in the UTC zone that this will not be a problem. It'll affect all other zones in one direction or the other.

Report · Feb 09, 2017

I copied your code, with the exception of the line, local.dttm2aUTC = #local.dttm2aUTC#, which I consider unnecessary. My test gives:

local.dttm1UTC = {ts '2017-02-01 00:00:00'}

local.dttm2UTC = {ts '2017-02-01 01:00:00'}

local.dttm3UTC = {ts '2017-02-01 02:00:00'}

local.dttm1UTC < local.dttm2UTC = SHOULD BE YES

{ts '2017-02-01 00:00:00'} < {ts '2017-02-01 01:00:00'} = YES

local.dttm2UTC < local.dttm3UTC = SHOULD BE YES

{ts '2017-02-01 01:00:00'} < {ts '2017-02-01 02:00:00'} = YES

local.dttm1UTC < local.dttm2aUTC = SHOULD BE YES

{ts '2017-02-01 00:00:00'} < {ts '2017-02-01 01:00:00'} = NO

local.dttm2aUTC < local.dttm3UTC = SHOULD BE YES

{ts '2017-02-01 01:00:00'} < {ts '2017-02-01 02:00:00'} = YES

So I would suggest you go ahead with the bug report. In it, refer to this page.

I am on ColdFusion 2016. My time zone is UTC+01:00 (Amsterdam, Berlin, Bern, ...).