Highlighted

Strange Happenings with UTC Conversions - BEWARE

Advocate ,
Feb 08, 2017

Copy link to clipboard

Copied

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:

<cfscript>

            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>

<cfoutput>

            local.dttm1UTC = #local.dttm1UTC#<br />

            local.dttm2UTC = #local.dttm2UTC#<br />

            local.dttm3UTC = #local.dttm3UTC#<br />

            <br />

            local.dttm1UTC &lt; local.dttm2UTC = SHOULD BE YES<br />

            #local.dttm1UTC# &lt; #local.dttm2UTC# = #local.dttm1UTC LT local.dttm2UTC#<br />

            <br />

            local.dttm2UTC &lt; local.dttm3UTC = SHOULD BE YES<br />

            #local.dttm2UTC# &lt; #local.dttm3UTC# = #local.dttm2UTC LT local.dttm3UTC#<br />

            <br />

            local.dttm2aUTC = #local.dttm2aUTC#<br />

            <br />

            local.dttm1UTC &lt; local.dttm2aUTC = SHOULD BE YES<br />

            #local.dttm1UTC# &lt; #local.dttm2aUTC# = #local.dttm1UTC LT local.dttm2aUTC#<br />

            <br />

            local.dttm2aUTC &lt; local.dttm3UTC = SHOULD BE YES<br />

            #local.dttm2aUTC# &lt; #local.dttm3UTC# = #local.dttm2aUTC LT local.dttm3UTC#<br />

</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.

Views

218

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Strange Happenings with UTC Conversions - BEWARE

Advocate ,
Feb 08, 2017

Copy link to clipboard

Copied

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:

<cfscript>

            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>

<cfoutput>

            local.dttm1UTC = #local.dttm1UTC#<br />

            local.dttm2UTC = #local.dttm2UTC#<br />

            local.dttm3UTC = #local.dttm3UTC#<br />

            <br />

            local.dttm1UTC &lt; local.dttm2UTC = SHOULD BE YES<br />

            #local.dttm1UTC# &lt; #local.dttm2UTC# = #local.dttm1UTC LT local.dttm2UTC#<br />

            <br />

            local.dttm2UTC &lt; local.dttm3UTC = SHOULD BE YES<br />

            #local.dttm2UTC# &lt; #local.dttm3UTC# = #local.dttm2UTC LT local.dttm3UTC#<br />

            <br />

            local.dttm2aUTC = #local.dttm2aUTC#<br />

            <br />

            local.dttm1UTC &lt; local.dttm2aUTC = SHOULD BE YES<br />

            #local.dttm1UTC# &lt; #local.dttm2aUTC# = #local.dttm1UTC LT local.dttm2aUTC#<br />

            <br />

            local.dttm2aUTC &lt; local.dttm3UTC = SHOULD BE YES<br />

            #local.dttm2aUTC# &lt; #local.dttm3UTC# = #local.dttm2aUTC LT local.dttm3UTC#<br />

</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.

Views

219

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Feb 08, 2017 0
Advocate ,
Feb 09, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 09, 2017 0
Advocate ,
Feb 09, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 09, 2017 0
Adobe Community Professional ,
Feb 09, 2017

Copy link to clipboard

Copied

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, ...).

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 09, 2017 0