Why is there an offset when using DateConvert with DateCompare?

Report · Mar 04, 2014

I'm developing on my local server and my current timezone is GMT +11 hours.

So, I was expecting that when I ran this code:

#DateCompare(date1,date2,'d' )#

that it would return a "-1" but it returned a "0" implying that the "day" was the same.

So, to double check I output:

DateConvert("Local2UTC", now())

grabbed its displayed value : {ts '2014-03-05 07:08:58'} and inserted that for comparison thus:
DateCompare("{ts '2014-03-05 07:08:58'}",date2,'d' ) and sure enough, it did return a "-1" reflecting that the day of date1 is indeed earlier than date 2.

Further investigation showed that I had to add my timezone difference to date2 in hours thus:

<cfset date2 = '2014-03-06 11:00:00> to get

#DateCompare(date1,date2,'d' )#

to return a "-1".

Is this a bug or am I misinterpreting??

Report · Mar 05, 2014

You are misinterpreting, a bit. Well, ColdFusion is, too.

Start with ColdFusion. Run the following test code:

isDate('0.1'): #isDate('0.1')#

isDate('2a'): #isDate('2a')#

isDate('3p'): #isDate('3p')#

</cfoutput>

ColdFusion will tell you that it recognizes '0.1', '2a' and '3p' as valid dates! What dates do they stand for?How does each translate to your time zone and locale? ColdFusion might be bending it like Beckham, who knows? We wouldn't go into that.

However, the moral is clear. If you wish to avoid ambiguity, especially when comparing datetimes, then avoid representing dates as strings. Convert all your datetimes to datetime objects. That will enable you to compare like with like. For example,

createdate(2014,3,6) in place of '2014-03-06';

dateConvert("Local2UTC", now()) in place of {ts '2014-03-05 07:08:58'};

createdate(2014,3,6,11,0,0) in place of '2014-03-06 11:00:00'.

Report · Mar 05, 2014

Hmmm, I think I might have to revisit a whole bunch of code with dates, Doh!

I think your advice of comparing like with like is appropriate however I'm still getting an unexpected result.
Sooo:
DateConvert("Local2UTC", now()) returns {ts '2014-03-05 15:11:14'}
note: 5th day of month
DateCompare(createdate(2014,3,6),DateConvert("Local2UTC", now()),'d' )
returns a "0", implying that both are the same day of the month??
I've tried changing the value in the create date expression to:

createdate(2014,3,5) and createdate(2014,3,7).
The values returned from the compare statement are then -1 and 1 respectively.
In all cases the DateCompare statement behaves as though DateConvert("Local2UTC", now()) returns a date from the 6th day of the month instead of the 5th??

Now using like with like, but still not getting expected result??
Further thoughts??

Report · Mar 05, 2014

First off, a correction. I wrote in my last post,

createdate(2014,3,6,11,0,0) in place of '2014-03-06 11:00:00'.

That should have been: createdatetime(2014,3,6,11,0,0) in place of '2014-03-06 11:00:00'.

Now, on to dateCompare. When I run the following code

dateConvert: #dateConvert("Local2UTC", now())#

dateCompare: #dateCompare(createdate(2014,3,6),DateConvert("Local2UTC", now()),'d')#

</cfoutput>

I get

dateConvert: {ts '2014-03-05 20:15:33'}

dateCompare: 1

My time zone is UTC+01:00. I wonder what yours is.

Report · Aug 07, 2014

It's been a while, must admit, I was left a bit confused about the whole thing as it just didn't add up when I ran my code. Spent hours today playing with this and have worked a few things out, unexpected things ......

My local time zone is UTC +10 hours and my remote server is UTC -5hours.

The smart money according to many is to "always refer to UTC time" so that code can be used on any server and that sounds good to me. So, what I want to do is a comparison between current UTC time and a dateTime of my choice. First of all I'm getting UTC time like this <cfset Date1 = DateConvert('local2Utc', now())> and when run on my local server indeed brings up a time that is exactly 10 hours earlier, great right. Then I create a date of my choosing using CreateDateTime() according to your own good advice. Then I compare them using DateCompare and voila, instead of date2 being compared against the value I generated for date1 it compares with the value for now() (server time).

For the code below, I only have to wait until date1 = {ts 2014,08,07,11,03,46} before it returns a 1, 10 hours earlier than expected. I tested the exact same code on my remote server and the times were off by 5 hours which represents that servers time zone. If I replace the dateConvert statement for date1 with a createDateTime statement everything works fine without any offset. Looks like a bug to me.

Date 1 = #date1#
Date 2 = #date2#
#DateCompare(date1, date2 )#

Report · Aug 07, 2014

web-eng wrote:

The smart money according to many is to "always refer to UTC time" so that code can be used on any server and that sounds good to me.

Indeed the gist of the matter, spot on: compare like with like. So, what you apparently want is, either

or

Report · Aug 07, 2014

Hmmm, like with like and all other good practices aside, there is a bug. Also, like just about everyone else, I develop locally in one timezone +10 hours and run my sites remotely on a different timezone -5 hours, altogether a significant time difference. So after coming to understand how to deal with the bug I've come up with a way to compare dateTimes on the remote server, but it does require a spot of copying a string in where you've been advising not to.

First, to the dateCompare() bug. To expose the bug we better use a finer increment than days, so instead of specifying, simply leave the “datepart” attribute empty so that it defaults to seconds. You say you’re in a +1hour timezone so try this.

Presuming a local time of 16:00 on August 7^th 2014

<cfdump var="#date1#"> should produce {ts '2014-08-07 15:00:00'}

Now create a dateTime using CreateDateTime(2014,08,07,15,30,00) This time half way between your local dateTime and UTCDateTIme.

Now compare them <cfdump var="# DateCompare(date1, date2 )#> in this example on your local server, you would expect to return a -1 since date1 is earlier, but in fact it will return a +1 because it seems to be somehow only looking at the now() time (later) instead of the UTC converted time. This is a bug, DateCompare should not care where it gets its data from it should simply get it and then compare the two values. Usually there is no problem using dateCompare but when used in conjunction with the DateConvert('local2Utc', now()) it ignores the value generated by the conversion. Anyway, so much for the bug, Now to the procedure that I’m using that requires copying a string.

I’m wishing to compare a dateTime that I want something to stop being displayed on the remote server.

<cfset myDate = CreateDateTime(2014,08,05,17,0,0)>

<cfset myDateInUTC = DateAdd("s", GetTimeZoneInfo().UTCTotalOffset, myDate)>

<cfdump var="#myDateInUTC#">

This produces the string: {ts '2014-08-15 07:00:00'} .

I then generate currentUTCDateTime from:

Both these dates are then compared thus:
DateCompare(currentUTCDateTime, "{ts '2014-08-07 15:00:00'}" )

I have to generate the string on my local server and then upload it which is why I’m unable to simply use a function. It’s a bit convoluted but it does work. Note that in this example I’m setting the currentUTCDateTime using DateAdd instead which doesn’t trip up like DateConvert.

Report · Aug 07, 2014

web-eng wrote:

Now create a dateTime using CreateDateTime(2014,08,07,15,30,00) This time half way between your local dateTime and UTCDateTIme.

<cfset date2 = CreateDateTime(2014,08,07,15,30,00)>

This is where "comparing like with like" is relevant. The statement marked in bold is an assumption. As such, it can be false!

CreateDateTime(2014,08,07,15,30,00) is an absolute value of datetime. It contains no information about your geographical location, and so can be greater than or less than your local datetime, depending on your time zone.

Report · Aug 07, 2014

"CreateDateTime(2014,08,07,15,30,00) is an absolute value of datetime." Yes yes, it is an absolute value! DateCompare compares two absolute date values, well it seems as though it should after all the description is "Performs a full date/time comparison of two dates." Doesn't actually say "comparison of two absolute dates" but surely that's what it means. Let me also say that I've never come across any other coldfusion tag or function reinterpreting a date based on my location unless specifically specified. In fact, when I set my value for date1 using <cfset date1 = DateAdd("s", GetTimeZoneInfo().UTCTotalOffset, now())> dateCompare seems to accept that date as absolute and doesn't need to reinterpret it. The fact that dateCompare gets all 'interpretive" based on how date1 is set still seems like a bug.

The statement in bold is true when taking into account the context of your location being +1hour, which was included in the qualification of the statement.

Report · Aug 08, 2014

Again, dateCompare does not interpret. We developers do or should. To avoid any ambiguity related to time zones, the developer should first obtain the UTC equivalent of each of the two datetime values to be compared.

Report · Aug 08, 2014

Are you simply asserting that it doesn't interpret? I found that it does which is why it's a bug. I gave you an example, did you run that code to check?

Not sure why you're advising me to get the UTC equivalent of each dateTime value when that is exactly what I'm already doing in my "procedure that I’m using that requires copying a string" above.

Report · Aug 08, 2014

web-eng wrote:

Are you simply asserting that it doesn't interpret? I found that it does which is why it's a bug. I gave you an example, did you run that code to check?

Just to be sure we're talking about the same thing, do you mean this code:

<cfoutput>#dateCompare(date1, date2 )#</cfoutput>

Report · Aug 08, 2014

The code in your question is not definitive enough for a straight answer. To expose the bug, you need to select a timeDate value for date2 which is in between your Now() and dateConvert('local2Utc', now()) (1 hour in your case). So just copy the code below and make sure to select an appropriate value for date2 taking into account when you're running the test. Note that I'm outputting the values for date1 and date2 to the screen so you can see the "absolute values" they resolved to prior to dateCompare(). What I find on both my remote and local servers (CF10) is that instead of comparing with the date1 (UTC time as displayed on the screen) it compares with the now() time.

Time of test = #Now()#

date1 = #date1#
date2 = #date2#

dateCompare(date1, date2 ) = #dateCompare(date1, date2 )#

</cfoutput>o

This is what was output to the screen

Time of test = {ts '2014-08-09 14:18:42'}

date1 = {ts '2014-08-09 04:18:42'}

date2 = {ts '2014-08-09 13:20:00'}

dateCompare(date1, date2 ) = 1

Report · Aug 09, 2014

Thanks for your explanation. My bad: it is now clear I misunderstood. Sorry.

Apparently, in dateCompare, Coldfusion implicitly converts date2 to local UTC. I agree with you that this kind of interpretation is inadmissible. The developer should be the one to do the interpretation, and Coldfusion should simply compare the 2 values passed to the function. You should indeed report a bug.

Report · Aug 09, 2014

Hey, no sweat, as you can probably tell, I'm not an advanced programmer and I do appreciate being one of the many that you've assisted on this forum so thanks. Bug submitted Thanks for sticking with this issue to the end!

Report · Nov 23, 2014

Hi web-eng,

I've added a CF9 vs CF10 code comparison to your ticket #3802931 which illustrates the CF9 bug that CF10 fixed.

Thanks!,

-Aaron

Report · Nov 23, 2014

Aaron, thanks for your contribution, but the bug is not with dateConvert, it's with dateCompare.

Adobe Community

Why is there an offset when using DateConvert with DateCompare?

1 Correct answer