runPrepUpdate with MS-SQL 'datetimeoffset' types

Ignition
1

I’m trying to use the function system.db.runPrepUpdate to insert some datetimeoffset type data, however the date that is inserted does not include the timezone offset and is setting this part of the datetime to 0 (i.e. it’s assuming UTC time).

Is this function supposed to handle the datetimeoffset type, or is this something that’s missing?

Or perhaps is this an issue with the system.date.now() function that I’m using to insert the data in?

Cheers

EDIT:
It looks like Ignition doesn’t (yet?) support the MS-SQL datetimeoffset type.

2

system.date.now() simply instantiates a new java.util.Date() object, which is milliseconds UTC under the hood. These objects are displayed as local time or created from local time as needed. (In Ignition, using the project timezone setting.) Moving these to and from a database is under the control of the JDBC driver, which is expected to save that true timestamp as appropriate for the database. This is fully automatic (preserving UTC millis) with datetime column types in SQL Server. Why do you think you need a datetimeoffset column type?

3

I need to log sequence data every time a sequence runs and want to store the datetimes in UTC time so that I don’t have to worry about daylight savings time change-over periods. I log both the start and end sequence times and then report the durations that the sequence ran for, and for periods across the DST changeover period, these durations are out I believe due to TZ info not being stored in the SQL table.

Using the datetimeoffset field would be more of a reminder to me and to others that these datetimes are actually in UTC and not in local.

4

Hmmm. Apparently this isn't true. /-: I'm a bit too used to PostgreSQL.

5

It looks like I can use this though to insert the correct, timezoned, date:

now = system.date.now()
now_tz = system.date.addMinutes(now, now.getTimezoneOffset())

system.db.runPrepUpdate('INSERT INTO testDateTimeOffset VALUES (?)', [now_tz])
6

Seems to me they are working correctly. What do you see when you retrieve and display them in an Ignition client? Maybe you need a newer JDBC driver.

FWIW, the timezonetz column type in PostgreSQL does exactly what you want.

7

Well at the moment I’ve been storing them in the SQL table without timezone info, and with the local datetime rather than the UTC datetime. It’s only now after we’ve just turned our clocks back that I’ve realised my mistake.

8

Thanks Phil, unfortunately I’m stuck using MS SQL and so going to have to work around it.

Edit: Although I’ve just now tried to do a simple query to pull back some datetimeoffset fields and get this error:
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Unknown SQL type: -155

Derp… it would seem I can’t use the datetimeoffset type at all :confused:
Note: I have updated my MS JDBC to the latest version