UNIX epoch times! (again)

[tribule]

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

[BKBK]

Does this help?

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

[tribule]

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]

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>

[tribule]

Hi,

I tried that with one of my dates that was not working earlier, and it was successful. I've used the * 1000 bit before but not the setLocale() - is that locale setting essential?

I will let it run and see how it goes. Thanks!

Thanks,

Mark

[tribule]

Right, I just had in the date 31/10/1989 and your code gives epoch value of:

625798800000

In https://www.epochconverter.com/ this converts to the correct date but with a 01:00:00 time value, so some work, some do not again.

Regards,

Mark

[WolfShade]

If, after all this, it still isn't working 100% as expected, file a bug with TRACKER.  Bring the URL here, I'll vote for it.

V/r,

^ _ ^

UPDATE:  It might be the online converter you're using.  I tried https://www.unixtimestamp.com/index.php and it worked fine.  (Uses seconds, not milliseconds, so remember to divide by 1k)

[tribule]

Hi. The API I am integrating with needs a millisecond value with midnight as a time (only). Any times that are not 00:00:00 are rejected.

Which value did you use btw? I tried the site you mentioned and it said:

625798800000 is equivalent to: 10/10/21800 @ 4:00pm (UTC) - no good

and 625798800 was equivalent to: 10/31/1989 @ 1:00am (UTC) - no good

To be honest I'm not sure if this is a bug or expected behaviour. Should the code that I and @BKBK provided create epoch dates with times set to midnight, or not?

Thanks,

Mark

[WolfShade]

I used your 625798800 and it gave me a date/time of  OCT-31-1989 12:00 am.

To be honest I'm not sure if this is a bug or expected behaviour. Should the code that I and @BKBK provided create epoch dates with times set to midnight, or not?

Should, yes, absolutely.  Which is why I think this is a bug and should be reported to Adobe Tracker.

V/r,

^ _ ^

[John123 · Accepted solution]

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>

[tribule]

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?

[tribule]

John, I might have spoken too soon.

We had a date come in as 29/07/1949. The date returned from your code put 28/07/1949 in to the system. Here's the output from your code:

myDate = {ts '1949-07-29 00:00:00'}
myDateUTC = {ts '1949-07-28 23:00:00'}
myDateUTCZero = {ts '1949-07-28 00:00:00'}
Date=Jul 28 1949 00:00:00.000 UTC
Epoch = -644716800000

It seems 23:00:00 went in first, as I found myself in my tests, and thus midnight was not reached and we are one day early.

This will work, but it's very crude:

<cfif Hour(myDateUTC) eq 23>
<cfset myDateUTCZero = CreateDateTime(year(myDateUTC), month(myDateUTC), day(myDateUTC)+1, 0, 0, 0)>
</cfif>

<cfif Hour(myDateUTC) eq 1>
<cfset myDateUTCZero = CreateDateTime(year(myDateUTC), month(myDateUTC), day(myDateUTC)-1, 0, 0, 0)>
</cfif>

Any ideas on that?

Thanks,

Mark

[John123]

On my system that date produces the following

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

Epoch = -644803200000

What version of Java are you running and what timezone is your local?

[John123]

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

[tribule]

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

[John123]

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.

[tribule]

Understood, but we still need the midnight time in all cases. I will add the adjustment and hope that it works in the majority of cases.