System.date.now() to sql t_stamp

Ignition
,
1

I'm using the tag historian for tag history data. I've got a MySQL database set up and when using the database browser I see t_stamps like so:

image

I can't make heads or tails of these numbers. How can I format/transform system.date.now() to get a value like these t_stamps?

2

system.date.toMillis(system.date.now())

2 Likes
3

That's Unix time: number of seconds since 1970/01/01.
I believe the column's type is responsible for the format it stores things in.

Use a datetime type to store your data instead if you don't want those timestamps.

4

Slight tweak - Java's default timestamp format is milliseconds since epoch - so often functions that expect unix time will need you to divide by 1000.

5

So divide by 1000 here or no?

6

The Ignition system.date functions and the Expression Language toMillis and fromMillis functions do this for you.

1 Like
7

If you're working within Ignition, you shouldn't need to.

If you're trying to make these human readable in your DB, e.g. MySQL's FROM_UNIXTIME, you'll need to divide by 1000.

8

So if I wanted to get the current time and go back 2 seconds before comparing against the database, should I subtract 2 or 2000? Or use system.date.addSeconds()?

9

This. system.date.now() returns a Date object; system.date.addSeconds knows how to work with a Date object, and returns a modified one. Within Ignition, always use Date objects directly as much as you can - only format to string at the last possible moment for display purposes.

10

You need to be careful with the historian database as the system uses analog or discrete deadband settings to reduce the database size and can use some interpolation on data retrieval.

Generally it's a bad idea to be querying the historian databases because it's so much work due to the data compression and the data partitioning which, by default, creates a new table for each month. Let Ignition do all the grunt work for you.

What's the real problem you're trying to solve here?

1 Like
11

I have a BOOL tag that I'm monitoring for when it changes states from 0 to 1. When it does so, I need to record the timestamp - for the last 5 changes.

So, I've got an integer array (length 6) that gets written to using this tag's value changed script. It's length 6 because as part of the process I update the current index and clear the next before incrementing the index.

Then, these timestamps will be used to query the historical database for the 90 seconds after that timestamp.

12

I recommend you do all your math with Ignition's date/time helpers, or directly with java functionality. The historian requires the start/end bounds to be java.util.Date.

13

OK, your BOOL t_stamp array seems to be working and may be the best approach.

For the history you could consider a script to loop through the BOOL t_stamps and use system.tag.queryTagHistory() to retrieve the 90 s worth of tag history.

There are a large number of options in the function. I think you could cut it down to,

paths = [<list of tagPaths>]
startTStamp = <t_stamp from BOOL array>
results = system.tag.queryTagHistory(paths, 
        startDate = startTStamp, 
        rangeSeconds = 90,
        returnSize = -1,
        returnFormat = <probably 'Tall'>, 
        noInterpolation = <probably False>, 
        ignoreBadQuality = <you decide>
)

You might find it useful to use a table component to test this approach.

https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory

14

that's the plan, but just to clarify the t_stamps are not BOOLs (of course). There's one BOOL tag who's tag valueChanged script will write to an integer array tag when the value changes to 1.

Haven't gotten to the actual query part yet. I'll probably do this dynamically on the popup window so the query is only executed when needed.

1 Like
15

So when I use

timestamp = system.date.toMillis(system.date.addSeconds(system.date.now(), timestamp_offset))

and try to put this into a list I get an "L" at the end. Why?

path = '[Area_01]Pumps_Motors/Test_Motor'

#get current time as millis for database and apply offset
timestamp = system.date.toMillis(system.date.addSeconds(system.date.now(), 0))

print timestamp

#read the index and start times array
index_num, times_arr, equip = [readQV.value for readQV in system.tag.readBlocking([path+'/Hist/Prev_Start_Times.Index',path+'/Hist/Prev_Start_Times',path+'Equip_Name'])]

index = int(index_num)
times = list(times_arr)

#update the current index with the new timestamp
times[index] = timestamp
print times

results in this output

1700166462224
[0, 1700166462224L, 0, 0, 0, 0]

Are these t_stamps INTs or Strings?

16

You are printing things. Printing yields an approximation of the actual object content. The L suffix is jython's notation for a long integer, as opposed to a simple 32-bit integer.

17

... and you'll sometimes see Unicode strings represented by u'abcde'.

Yes, the t_stamps are INTs (number of milliseconds since 1970-01-01.

18

Then I guess I need a long array instead of an integer array.

19

Not until 2038:

A Python 'long' that is less than 32-bit-signed-int-max (231-1, 2,147,483,647) can and will be losslessly converted into an integer by Ignition when it's put into your int array.

1 Like
20

I tried to put it into an Integer Array tag and it didn't work. Worked once I changed it to a Long Array.

1 Like