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

UNIX epoch times! (again)

  • May 7, 2020
  • 2 replies
  • 4410 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?

      T
      tribuleAuthor
      Legend
      May 8, 2020

      Sorry, miskey.

       

      myDate = {ts '1949-07-29 00:00:00'}
      myDateUTC = {ts '1949-07-29 04:00:00'}
      myDateUTCZero = {ts '1949-07-29 00:00:00'}
      Date=Jul 29 1949 00:00:00.000 UTC

      Epoch = -644630400000


      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

      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