Highlighted

UNIX epoch times! (again)

Engaged ,
May 07, 2020

Copy link to clipboard

Copied

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

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>

Views

2.3K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

UNIX epoch times! (again)

Engaged ,
May 07, 2020

Copy link to clipboard

Copied

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

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>

Views

2.3K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 07, 2020 0
Adobe Community Professional ,
May 07, 2020

Copy link to clipboard

Copied

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 07, 2020 0
Engaged ,
May 07, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 07, 2020 0
Adobe Community Professional ,
May 07, 2020

Copy link to clipboard

Copied

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>

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 07, 2020 0
Engaged ,
May 07, 2020

Copy link to clipboard

Copied

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

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 07, 2020 0
Engaged ,
May 07, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 07, 2020 0
LEGEND ,
May 07, 2020

Copy link to clipboard

Copied

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)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 07, 2020 0
Engaged ,
May 07, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 07, 2020 0
LEGEND ,
May 07, 2020

Copy link to clipboard

Copied

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,

 

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 07, 2020 0
Participant ,
May 07, 2020

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 07, 2020 1
Engaged ,
May 08, 2020

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 08, 2020 0
Engaged ,
May 08, 2020

Copy link to clipboard

Copied

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

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 08, 2020 0
Participant ,
May 08, 2020

Copy link to clipboard

Copied

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?

 

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 08, 2020 0
Participant ,
May 08, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 08, 2020 0
Engaged ,
May 08, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 08, 2020 0
Participant ,
May 08, 2020

Copy link to clipboard

Copied

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.

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 08, 2020 1
tribule LATEST
Engaged ,
May 08, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 08, 2020 0