Thread: DateDiff

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    5
    Rep Power
    0

    DateDiff


    Hi,

    I am having an issue with the DateDiff Function. There is a bug in the tag and I have not found the correct update for this yet. I am running ColdFusion 9. The problem that I am having is that I use epoch time for what I do. Epoch time is the number of seconds from 01-01-1970 00:00:00

    I created a function in coldfusion that would convert a date to epoch time. I have been using the DateDiff Function that coldfusion provides but the problem is that when we enter into daylight savings the DateDiff is off by an hour.

    Example:

    DATEDIFF(s,'1970-01-01 00:00:00', '2011-03-16 00:00:00')

    should return - 1300233600
    instead it returns this - 1300230000

    which is exactly one hour behind.

    I have confirmed this using Sql Server to run its DateDiff functions which returns the correct date - "1300233600"

    So my question has 2 parts.

    First does anyone know about this problem and if so how can I fix it?

    Second, If im not able to fix it I have been trying a work around to using the DateDiff in SQL rather that Coldfusion but I am getting an error in my code and not sure what the problem is.

    <cffunction name="DateToEpoch" returntype="any" output="no">

    <cfargument name="DatePast" type="date" required="yes">

    <cfset var ReturnDateEpoch = "">

    <cfquery name="ReturnDateEpoch" datasource="Metro"">
    Select DATEDIFF(s,'1970-01-01 00:00:00', '#DatePast#')
    </cfquery>

    <cfreturn ReturnDateEpoch>

    </cffunction>


    But when this is executed I get this error:

    Complex object types cannot be converted to simple values.
    The expression has requested a variable or an intermediate expression result as a simple value. However, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.
    The most likely cause of the error is that you tried to use a complex value as a simple one. For example, you tried to use a query variable in a cfif tag.

    Any help or direction would be great

    Thanks,

    Nick
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    It's something on your end. I am also running CF 9, and when I run this:

    Code:
    <cfoutput>#DateDiff( 's','1970-01-01 00:00:00', '2011-03-16 00:00:00' )#</cfoutput>
    I get: 1300230000

    Edit: You can confirm this by using a tool like this: http://www.epochconverter.com/epoch/date-difference.php

    You might start by checking your JRE version (I'm using 1.6.0_21), and that the system clock on the CF server is set correctly.
    Last edited by kiteless; March 16th, 2011 at 05:21 PM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    5
    Rep Power
    0
    Ya I am running 1.6.0_17, so I am behind. Do I just go to http://java.sun.com and get the update from there?

    Thank you,

    Nick

    Originally Posted by kiteless
    It's something on your end. I am also running CF 9, and when I run this:

    Code:
    <cfoutput>#DateDiff( 's','1970-01-01 00:00:00', '2011-03-16 00:00:00' )#</cfoutput>
    I get: 1300230000

    You might start by checking your JRE version (I'm using 1.6.0_21), and that the system clock on the CF server is set correctly.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    5
    Rep Power
    0
    Hi,

    I am so sorry but I put the wrong numbers. When I run this I get the same thing as you 1300230000. If you take that number and check it you will see that it is 1 hour behind what it should be. When I take that number and run it at http://www.epochconverter.com/ it returns
    GMT: Tue, 13 Mar 2007 23:00:00 UTC. So to sum up

    <cfoutput>#DateDiff( 's','1970-01-01 00:00:00', '2011-03-16 00:00:00' )#</cfoutput> will return 1300230000 but it should be returning 1300233600. Can you check this again against yours and see if you are getting the same thing

    Thanks,

    Nick

    GMT: Tue, 13 Mar 2007 23:00:00 UTC = 1300230000
    GMT: Wed, 16 Mar 2011 00:00:00 UTC= 1300233600



    Originally Posted by kiteless
    It's something on your end. I am also running CF 9, and when I run this:

    Code:
    <cfoutput>#DateDiff( 's','1970-01-01 00:00:00', '2011-03-16 00:00:00' )#</cfoutput>
    I get: 1300230000

    Edit: You can confirm this by using a tool like this: http://www.epochconverter.com/epoch/date-difference.php

    You might start by checking your JRE version (I'm using 1.6.0_21), and that the system clock on the CF server is set correctly.
  8. #5
  9. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    The hour difference is because the start time you're using is Standard Time, but the end time is Daylight Savings Time. Because DST is an hour earlier than ST, the difference calculation is an hour less. You can confirm that by adding an hour to the end date, which simulates what that date and time are in Standard Time and makes both dates use the same time zone (Standard Time).

    You can confirm this by going back to the epochconverter and making sure the two dates are using the same time zone.

    Thu Jan 01 1970 00:00:00 GMT-0500
    Wed Mar 16 2011 00:00:00 GMT-0500

    Or, put another way, if you leave the dates as you have them initially, you can see just from looking at the time zones that one is an hour behind the other:


    Thu Jan 01 1970 00:00:00 GMT-0500 (start, not under DST)
    Wed Mar 16 2011 00:00:00 GMT-0400 (end, under DST)
    Last edited by kiteless; March 16th, 2011 at 06:34 PM.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    5
    Rep Power
    0
    This is what I don't understand and maybe you do. And keep in mind I only work with GMT. Why is it that when I run the DateDiff code in sql and coldfusion I get 2 different results.

    SQL - DATEDIFF(s,'1970-01-01 00:00:00', '2011-03-16 00:00:00') = 1300233600

    CF - DATEDIFF(s,'1970-01-01 00:00:00', '2011-03-16 00:00:00') = 1300230000

    Also if you go to epochconverter.com and manually type in '2011-03-16 00:00:00' it will return this 1300233600

    The same as SQL but Coldfusion is 1 hour different then both

    Why is that?

    Thanks,

    Nick



    Originally Posted by kiteless
    The hour difference is because the start time you're using is Standard Time, but the end time is Daylight Savings Time. Because DST is an hour earlier than ST, the difference calculation is an hour less. You can confirm that by adding an hour to the end date, which simulates what that date and time are in Standard Time and makes both dates use the same time zone (Standard Time).

    You can confirm this by going back to the epochconverter and making sure the two dates are using the same time zone.

    Thu Jan 01 1970 00:00:00 GMT-0500
    Wed Mar 16 2011 00:00:00 GMT-0500

    Or, put another way, if you leave the dates as you have them initially, you can see just from looking at the time zones that one is an hour behind the other:


    Thu Jan 01 1970 00:00:00 GMT-0500 (start, not under DST)
    Wed Mar 16 2011 00:00:00 GMT-0400 (end, under DST)
  12. #7
  13. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    I assume your CF server system clock is using a time zone that recognizes Daylight Savings Time, yes? Then the difference is because you're not creating your epoch value using a constant time zone. You're taking a start date that is in standard time and an end date that is in daylight time and calculating the seconds between them. This is why if you adjust for the one hour offset in the starting and ending time zones, you get the number you're expected. (enter "2011-03-16 00:00:00 GMT+0100" into the epoch converter to see this)

    Or, again, if you use the two CORRECT dates (including the correct time zones for each date) in the epoch converter difference calculator:

    Thu Jan 01 1970 00:00:00 GMT-0500 (start, not under DST)
    Wed Mar 16 2011 00:00:00 GMT-0400 (end, under DST)

    Edit: or to make it more obvious, use these:

    Thu Jan 01 1970 00:00:00 EST
    Wed Mar 16 2011 00:00:00 EDT

    You get the expected 1300230000.

    Basically I'm not sure what else to say. I'm not a SQL Server guru but I assume either the SQL box's system clock is using GMT/UTC, or SQL Server itself is configured to store its dates in standardized GMT or UTC time. Since neither of those time zones recognize daylight savings, the numbers will be different from using time zones that DO recognize daylight savings, because daylight savings is exactly the same as changing to another time zone.
    Last edited by kiteless; March 17th, 2011 at 12:47 PM.
  14. #8
  15. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    By the way, off the top of my head, here is one option to handle this:

    Code:
    public function dstSeconds()
    {
    	var timeZoneInfo = GetTimeZoneInfo();
    	return timeZoneInfo.isDSTOn ? 3600 : 0;
    }
    
    #DateDiff( 's', '1970-01-01 00:00:00', '2011-03-16 00:00:00' ) + dstSeconds()#
    Will give you 1300233600
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    5
    Rep Power
    0
    Thank you for your help. I have corrected my problem with the function I created below. But now I have the same problem in reverse. I can convert the date to the correct seconds but when I try to convert seconds to the correct date I have the off by an hour problem. The first function below works correctly date to epoch and the second function is epoch to date which has the off by an hour issue. Any Ideas?

    Thanks,
    Nick

    <cffunction name="DateToEpoch" returntype="any" output="no">

    <cfargument name="DatePast" type="date" required="yes">

    <cfset var ReturnDateEpoch = "">

    <cfset ReturnDateEpoch = #DateDiff('s', DateConvert('utc2Local', 'January 1 1970 00:00:00'), DateConvert('utc2Local', DatePast))#>

    <cfreturn ReturnDateEpoch>

    </cffunction>
    --------------------------------------------------------

    <cffunction name="EpochToDate" returntype="date" output="no">

    <cfargument name="EpochNumber" type="numeric" required="yes">

    <cfset var ReturnEpochDate = "">

    <cfset ReturnEpochDate = DateAdd('s', EpochNumber, 'January 1 1970 00:00:00')>

    <cfreturn ReturnEpochDate>

    </cffunction>







    Originally Posted by kiteless
    By the way, off the top of my head, here is one option to handle this:

    Code:
    public function dstSeconds()
    {
    	var timeZoneInfo = GetTimeZoneInfo();
    	return timeZoneInfo.isDSTOn ? 3600 : 0;
    }
    
    #DateDiff( 's', '1970-01-01 00:00:00', '2011-03-16 00:00:00' ) + dstSeconds()#
    Will give you 1300233600

IMN logo majestic logo threadwatch logo seochat tools logo