How to convert a time to epoch with Expression

I know how to convert epoch to various types of date formats, but how do I go the other way?

I have these two formats and would like to convert to epoch. They are both currently of Data Type=DateTime

Wed Mar 19 15:23:08 CDT 2014
2014-03-19 15:23:08.93

I know how you can accomplish this with a script if that helps:

import datetime
td = (datetime.datetime(2012,03,20,0,0) - datetime.datetime(1970,1,1))
print (td.microseconds + (td.seconds + td.days * 24 * 3600))

This script creates two datetime objects (one with the date I want to convert and then another at the epoch time) and then takes the difference, which creates a timedelta object. Then it converts the number of days, hours and seconds to ms and sums them with the ms part of the timedelta object.

You can take a look at the datetime module documentation for python to get a better idea of how to work with them. I built this example by directly copying some of their examples and then piecing them together. Just remember that the version of Jython in Ignition less than the version documented in most python docs. So some features you read about might not be available in Ignition (such as the total_seconds() function added to the timedelta object in Python 2.7).

You could wrap this up in an app module and then call it using the runscript expression if you really need to do it from an expression. Just realize that you’re going to have to parse out the different parts of the dates you have because the datetime constructor takes a bunch of integers as arguments. Hope this helps.

Thanks Dave. I never thought about using the runScript in the Expression. Seems a bit more complicated but might be an option. I guess I could always use the Java getTime() method which appears to do the same thing.

I’ll keep my fingers crossed that someone has a fancy way of doing this using only Expression tools:
dateArithmetic, dateExtract, dateDiff, dateFormat, toDate, etc


I’m not 100% clear on what you’re asking: are you trying to get elapsed time since epoch for a given date?

If so…

dateDiff(your_date_here, toDate("1970-1-1 0:00:00"), "sec") * 1000

would get you milliseconds since epoch…

1 Like

That’s exactly what I was looking for! I feel embarrassed for missing that. :blush:

I know this may be another question but I was hoping to use milliseconds since the epoch to protect against DST changes. I’m not sure my approach of converting two date time tags to epoch before using dateDiff would be beneficial.

I currently take the value from Query Tag that is a record from MS SQL server datetime Data Type column and subtract that from a LastChange property of a SQL Tag. This gives me a float with seconds. Will this be accurate if one of the values happen before DST and the other after DST?

I apologize but I am having hard time understanding how Ignition and MS SQL handle date time; behind the scenes vs what we see in the Designer or SQL Management Studio.

The dateFormat expression has the time zone Z which could be used to determine whether DST applies. I am using it in an expression SQLTag to determine how many hours to add/substract:

toInt(dateFormat(now(),"Z")) / 100

Also see strftime and strptime

I’m sure you guys know this already, but if you change a SQL Tag’s data type from DateTime to INT8 (and remove Metadata formatting), you get the number of ms since the epoch.

For instance,
An Expression Tag with now() set to DateTime will show:
2014-03-21 11:05:10 AM

An Expression Tag with now() set to INT8 will show:

The same is true if I query Microsoft SQL with a Query Tag, SELECT GETDATE(). Additionally, I get the same value if I query a SQL record in a datetime column with INT8.

All appear to be milliseconds since the epoch. I should be able to use these for any time calculations (deltas) around DST to avoid negatives and +60 minute values. So…I was overcomplicating it again trying to go back and forth from locale’s time and UTC and epoch.

I guess anything that goes “through” Ignition via a Tag hits the Java layer which uses UTC and milliseconds from the epoch.