I’ve got an application I’m working on that is going to be used as a time clock style application.
The data will be stored in an existing MSSQL server. The appropriate columns are set for the datetime2 data type currently.
The gateway and SQL server will be located in the same time zone EST or America\New York
Basically I want to make sure that I’m inserting the proper time into the tables so that DST doesn’t screw with them.
Is logging the system.date.now() value from Ignition sufficient to avoid any DST issues or should I have MSSQL log the UTC time based on when the insert occurs?
If I log the UTC value, how do I get it back out and convert it to local time properly to display it? In my playing around, if I do a SQL insert with the getutcdate() function, the UTC date/time is stored in SQL however when I go an read it from the Ignition side the time doesn’t get offset down to the local time zone. Date/Time stuff isn’t my forte by any means, so I’m most definitely doing something wrong with this
I’ve been struggling with the exact same issue. DST is the biggest PITA!
The best way would be to store the data in SQL using the datetimeoffset data type which includes the timezone, but unfortunately, this isn’t supported in Ignition (and actually I don’t know how this would work with DST either when the clock is changed). The next best is as you said, store it in UTC time. But as you’ve discovered, then it’s difficult / impossible to read this back in the client in its local time. Consider that the client could be in a different timezone to the SQL server, and queries are executed on the SQL server and not the client.
I’ve tried and am out of ideas about how to resolve the issue… unless for every time I run the query, I run it through a script that converts the timestamps to local time. But that’s not very efficient
Maybe you could get the client’s local timezone, store it in a client tag, and pass it into the SQL query to offset the datetime? I haven’t tried that It would fail though for past timestamps that happened prior to the DST time changing, as the offset would then change…
You can select UTC times and get them back under a certain time zone. In which case, the daylight saving time is also observed. But there will always be annoying functionality. Like when you use DATEDIFF to calculate the duration of something. Things get a different meaning when you talk about a duration in hours and it happened over the DST time jump, or a duration in days (f.e. between two midnight timestamps) where one day just happened to be 23 hours and thus not count for a full day…
This is one of the reasons I recommend PostgreSQL. Its timestamptz column type stores in UTC microseconds, converting to/from the connection timezone as needed. This makes it work perfectly with java’s java.util.Date and java.sql.Timestamp classes, across all time zones.
Consider firing Microsoft, in favor of something that Just Works™.
At this point I’m considering an alternative backing SQL Server just because of this issue.
@pturmel I know you are a big proponent of PostgreSQL… I would have a tough time putting that in our facility since it would be a one off. Do you know if MySQL has the same functionality? I could justify that one as we have that currently installed along with MSSQL. MSSQL is for our business side of things and MySQL is for our products that we install for our customers.
I think MariaDB has something similar. (I haven't recommended MySQL for any serious work since its founder created the MariaDB fork. And carried the majority of volunteer coders with him.)
Could you use system.date.toMillis to convert the timestamp to an integer and store that in your database in an int or long field? Then system.date.fromMillis would convert it back to local time for display. I’m pretty sure Postgres is doing something similar behind the scenes.
It would fail though for past timestamps that happened prior to the DST time changing, as the offset would then change…