Getting UTC Timestamp For Logging In MySQL

General Discussion
1

I’m trying to get a UTC Timestamp that I can use for Inserting into a MySQL Table in a DateTime Field.

I’m using the system.tag.read function to get the value of a tag. I was then using the .timestamp field to get the timestamp of the tag and then using the system.db.dateFormat() to format the timestamp properly in the Datetime Field. I’m timestamping down to Milliseconds and was using the following formatting “yyyy-MM-dd HH:mm:ss:SSSZ” However this produces the correct formatting but the Field comes out with a -700 at the end which is an offset from GMT if I’m not mistaken taking into account daylight savings time. Of course this makes my INSERT Fail into MySQL.

Is there a function in Java or Python that I can use that will put out a UTC time already taking into account the local computer time without the -700. I could then use the system.db.dateFormat() to put it in the correct formatting to insert in the Table.

Thanks for the help. :smiley:

Bruce

2

I don’t know of a timestamp property attached to the tag. There is LastChange property thou.

You can script these into your code, if you need the time when you run the script.

Way 1: Ask the SQL Server for the time:
in MSSQL: SELECT SYSUTCDATETIME()

From Memory. Don’t have access to MySQL right now.
in MySQL: SELECT UTC_TIMESTAMP() will give you: ‘YYYY-MM-DD HH:MM:SS’

SELECT UTC_TIMESTAMP()+0 will give you: YYYYMMDDHHMMSS.uuuuuu

I believe this will work also…
SELECT DATE_FORMAT(SYSUTCDATETIME(), ‘%Y %m %d %k %i %s %f’’)

Way 2: Ask Python.

from datetime import datetime 

dt = datetime.utcnow()

if you are looking to format tag.lastchange property, I would have to look into that further…
as I get [Fri Apr 12 14:27:36 MDT 2013,Good] as a result.

Fun little side note on time… Unless something / someone updates (or bothers to set it initially) the time, it may be wrong. So if you ask the SQL server (local/remote) or the local machine(s) or the SCADA server. You may get different answers.
Great thing about ignition is it can connect to several databases / clients / servers at the same time…

Cheers,
Chris

3

Chris,

There is a timestamp property when you use the system.tag.read function(). there are actually three properites returned from system.tag.read(), like this returnvalue = system.tag.read(tagpath). THis reutrns returnvalue.value, returnvalue.quality and returnvalue.timestamp. I’m using the timestamp property since this should have the timestamp from my opc server, at least it should. I’ll look at the Python call if I can’t somehow convert the timestamp to UTC.

I’m trying to log the value as close to when it is received from the OPC Server since this is my customers requirement.

Any other ideas anyone?

Thanks,

Bruce

4

Guys, the timestamps you get back from the SQLTags (and from anywhere in Java, really), are already in UTC. When you print them out or whatever you’re doing to view them your timezone is applied for display purposes, but all Java Dates are backed by the UTC time.

What’s the column type in your DB?

5

My Column Type in MySQL is Datetime(3). I’m logging to milliseconds. I read that it stores it in UTC, but when I print it it local time. So, you are telling me not to worry about the time and insert it into the database without the z or Z at the end of the formatting?

Bruce

6

Two things:

  1. Leave off the ‘z’ or ‘Z’ from the formatting and see what happens when you insert.
  2. MySQL has only recently started support millisecond resolution on the timestamps, using a datatype that we don’t currently support.
  3. You’re entering a whole world of complexity here, and things can vary depending on the JDBC driver, the database, whether they are running on the same machines, in the same timezones or not, etc… Ultimately it’s up to you to understand this and make sure you are storing and retrieving data in a consistent manner…

But for now, just try inserting the timestamp having formatted it correctly :slight_smile:

7

Kevin,

It inserts fine when I remove the z or Z and the timestamp is stored in a milli-second format as required. Hopefully It is being stored in UTC and I’ll have to tell my customer to call out the data in a UTC format and not based on local time. I think this fullfuls their requirement. The databases are local where the Gateway exists but my customer will be pulling out the records and placing them into another datbase table for long term storage. They then delete the reconrds behind me as I keep processing more data since I’m only doing inserts, no deltes or updates on my end.

Thanks,

Bruce