Skip to main content
T
tribule
Legend
May 7, 2020
Answered

UNIX epoch times! (again)

  • May 7, 2020
  • 2 replies
  • 4411 views

Hi,

 

Is there a reliable function to convert a specific date (with hours set as 00:00:00) to UTC time in UNIX epoch format? I have some success, but many of the epoch times are wrong. I need a date in UTC that is always set to midnight. The time is the issue. I've used a number of functions mentioned on the forums and none work all of the time. So if a person enters a date of 22/9/1970 (dd/mm/yyyy) I need a epoch date in milliseconds that is exactly for 22/9/1970 00:00:00.

 

This is what I have so far:

 

<cfset tzinfo = GetTimeZoneInfo()>
<cfset offset_seconds = tzinfo.utcTotalOffset>
<cfset startDateEpoch = CreateDateTime(1970, 1, 1, 0, 0, 0)>
<cfset dobDate = CreateDateTime(Year(MY_DATE), Month(MY_DATE), DAY(MY_DATE), 0, 0, 0)>
<cfset DATE_EPOCH = (DateDiff("s", startDateEpoch, dobDate) + offset_seconds) & "000">  

 

Problem is, the hour is sometimes 23:00:00, not 00:00:00, or 01:00:00 - always an hour around midnight. I am trying to take account of the timezone offset. We need the result in UTC. The value of MY_DATE is a query for a datetime field from our MSSQL table. We need 00:00:00 as the time in every occurence of the epoch date. I am using https://currentmillis.com/ to check the epoch values.

 

I've also used Dateconvert() with 'local2Utc' on the dobDate (removing the offset code) and still the same issue on some results.

 

Also, I tried getTime() to return the milliseconds since the dobDate e.g. dobDate.getTime() and the time is still 23:00:00 for example when I tested with the date 15/06/1965 (dd/mm/yyyy), which is -143514000000 epoch time.

 

Another example, we get -160527600000 for the date 30/11/1964 (dd/mm/yyyy). When I put that in the convertor, I get "Mon Nov 30 1964 01:00:00" - all correct except the 01:00:00 which we want to be 00:00:00.  😞

 

Thanks,

Mark

    This topic has been closed for replies.
    Correct answer John123

    The TimeZoneOffset is the offset for the current time which could be different for another date depending on DST, 1 hour each way, which could account for your difference in some cases. Switching time zones and accounting for differences is a nightmare.

     

    An easier way to get Epoch time is to use Java called from your CF code. This script will give you the Epoch time for your date. I have tried to explain the steps so hope this helps. There is probably a more efficient way to create a Java date object with timezone than using the parser but I had some of this code from another project.

     

    <cfscript>
    // Your date to convert
    myDate = CreateDateTime(1964, 11, 03, 0, 0, 0);

     

    // Convert your local date to UTC
    myDateUTC = dateConvert('local2Utc', myDate);

    // Zero out the UTC time as the conversion will give you a time other than zeroes
    myDateUTCZero = CreateDateTime(year(myDateUTC), month(myDateUTC), day(myDateUTC), 0, 0, 0);

     

    // Display the intermediate values

    writeOutput("myDate = #myDate#<br>");
    writeOutput("myDateUTC = #myDateUTC#<br>");
    writeOutput("myDateUTCZero = #myDateUTCZero#<br>");

     

    // Create the Java date format object
    javaDateFormat = createObject("java","java.text.SimpleDateFormat").init("MMM dd yyyy HH:mm:ss.SSS zzz");

     

    // Build formatted date
    jdf = dateFormat(myDateUTCZero, "mmm dd yyyy") & " 00:00:00.000 UTC";
    writeoutput("Date=#jdf#<br><br>");

    // Use Java to parse date and return Epoch time
    epoch = javaDateFormat.parse(jdf).getTime();

     

    writeoutput("Epoch = #epoch#<br>");
    </cfscript>

    2 replies

    J
    John123Correct answer
    Participating Frequently
    May 7, 2020

    The TimeZoneOffset is the offset for the current time which could be different for another date depending on DST, 1 hour each way, which could account for your difference in some cases. Switching time zones and accounting for differences is a nightmare.

     

    An easier way to get Epoch time is to use Java called from your CF code. This script will give you the Epoch time for your date. I have tried to explain the steps so hope this helps. There is probably a more efficient way to create a Java date object with timezone than using the parser but I had some of this code from another project.

     

    <cfscript>
    // Your date to convert
    myDate = CreateDateTime(1964, 11, 03, 0, 0, 0);

     

    // Convert your local date to UTC
    myDateUTC = dateConvert('local2Utc', myDate);

    // Zero out the UTC time as the conversion will give you a time other than zeroes
    myDateUTCZero = CreateDateTime(year(myDateUTC), month(myDateUTC), day(myDateUTC), 0, 0, 0);

     

    // Display the intermediate values

    writeOutput("myDate = #myDate#<br>");
    writeOutput("myDateUTC = #myDateUTC#<br>");
    writeOutput("myDateUTCZero = #myDateUTCZero#<br>");

     

    // Create the Java date format object
    javaDateFormat = createObject("java","java.text.SimpleDateFormat").init("MMM dd yyyy HH:mm:ss.SSS zzz");

     

    // Build formatted date
    jdf = dateFormat(myDateUTCZero, "mmm dd yyyy") & " 00:00:00.000 UTC";
    writeoutput("Date=#jdf#<br><br>");

    // Use Java to parse date and return Epoch time
    epoch = javaDateFormat.parse(jdf).getTime();

     

    writeoutput("Epoch = #epoch#<br>");
    </cfscript>

      T
      tribuleAuthor
      Legend
      May 8, 2020

      Thanks very much John, this looks to be the solution as it zeroes out the UTC time part. I will run with this for a few days and see how we get on.

       

      What a polava. Adobe should create a reliable epoch time function and allow us to format the result.

       

      Can I ask, is this a bug in DateDiff() or is it expected behaviour?

      J
      John123
      Participating Frequently
      May 8, 2020

      Our Java version is 11.0.6 (Oracle). Our server is GMT (with DST etc). Our dates and times are in GMT too. We need a UTC date result however.

       

      GetTimeZonInfo() returns:

       

      structisDSTon: YES

      utcHourOffset: -1

      utcMinuteOffset: 0

      utcTotalOffset: -3600


      What you are seeing is correct. If your server is on GMT but following DST then really your server is on BST for the date you are converting to UTC, so will subtract 1 hour making a midnight time GMT (or BST) go back to 23:00 UTC the previous day. If you want your server to be on GMT all year round then you can't have it following DST.

       

      You can do the adjustment you added but then you are not getting true UTC times relative to your servers time.

       

        BKBK
        Community Expert
        BKBKCommunity Expert
        Community Expert
        May 7, 2020

        Does this help?

        https://gist.github.com/randomweapon/2640492

        T
        tribuleAuthor
        Legend
        May 7, 2020

        Hi,

         

        Thanks for that, but unfortunately it didn't help. I have tried that code before in tests and still don't get the correct result that I need. I need to create an epoch date with a midnight time value.

         

        Take the date 04/07/1956 (dd/mm/yyyy). I can only get epoch value -425869200000 which when tested gives Tue Jul 03 1956 23:00:00 UTC. The day is wrong as well of course, because midnight has not arrived.

         

        Sometimes, the epoch times are correct, sometimes they are not. That's the problem in a nutshell.

         

        Regards,

        Mark

        BKBK
        Community Expert
        BKBKCommunity Expert
        Community Expert
        May 7, 2020

        Ah, OK. What if you just stick to the bare-bones datetime value, and use the UTC locale to translate it for you. I am thinking of something like:

         

         

        <cfset setLocale("English (UK)")>
<cfset startDateEpoch=createDateTime(1970, 1, 1, 0, 0, 0)>
<cfset dobDate=createDateTime(1964, 11, 30, 0, 0, 0)>
<cfset DATE_EPOCH = DateDiff("s", startDateEpoch, dobDate)  * 1000>

         

        Terms & ConditionsAccessibility statement
        Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices