Writing dates in UTC time using system.db.runPrepUpdate(...)

Ignition
1

Edit: database is MS SQL Server 2014 and above
I need to store datetimes in a database table in UTC time. The table field itself is a datetime type, so by itself it doesn’t have an attached timezone offset, however I need to convert my local timezone time to the equivalent UTC time, and have that stored, as otherwise daylight savings will destroy my data.

I’m creating dates using system.date.* functions. From what I understand, the java.util.Date object returned by these really just hold the number of millis since the epoch, which is in UTC by definition. When I use these Dates with the runPrepUpdate function however, the SQL database is storing the local datetime.

How can I store in UTC? (I’ve already tried to get the local timezone offset hours so I can subtract it from the local time e.g. +10:30 => 10.5 before passing it to the runPrepUpdate function, but it’s been far more difficult than I thought it would be… I haven’t tried to extract the values out of the text string itself yet, but that would be horrible and a very ast resort!)

2

Change the column type to timestamp (mariadb) or timestamptz (postgresql) and it’ll ‘Just Work’. Those column types store as UTC and convert everywhere as needed. JDBC supplies the millis from java.util.Date for direct storage.

3

We’re using MS SQL Server :thinking: I should have mentioned that in my OP. Edited, but good to know RE the other DBs

4

Ah, for SQL Server the type is DATETIMEOFFSET. Apparently there are problems with that in Ignition.

5

Yep :slight_smile: hence why looking to convert to UTC before it writes (and then convert back when I read, which is future Nick’s problem)