# runPrepUpdate with MS-SQL 'datetimeoffset' types **URL:** \ **Category:** Ignition\ **Created:** [April 8, 2019, 2:16am UTC](https://forum.inductiveautomation.com/t/runprepupdate-with-ms-sql-datetimeoffset-types/23908 "2019-04-08T02:16:20Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [April 8, 2019, 2:16am UTC](https://forum.inductiveautomation.com/t/runprepupdate-with-ms-sql-datetimeoffset-types/23908/1 "2019-04-08T02:16:20Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 8, 2019, 2:51am UTC](https://forum.inductiveautomation.com/t/runprepupdate-with-ms-sql-datetimeoffset-types/23908/2 "2019-04-08T02:51:06Z")
> [@nminchin](#): > > Or perhaps is this an issue with the system.date.now() function that I’m using to insert the data in? 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? ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [April 8, 2019, 2:56am UTC](https://forum.inductiveautomation.com/t/runprepupdate-with-ms-sql-datetimeoffset-types/23908/3 "2019-04-08T02:56:21Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 8, 2019, 2:56am UTC](https://forum.inductiveautomation.com/t/runprepupdate-with-ms-sql-datetimeoffset-types/23908/4 "2019-04-08T02:56:55Z")
> [@pturmel](#): > > This is fully automatic (preserving UTC millis) with datetime column types in SQL Server. Hmmm. Apparently this isn't true. /-: I'm a bit too used to PostgreSQL. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [April 8, 2019, 2:58am UTC](https://forum.inductiveautomation.com/t/runprepupdate-with-ms-sql-datetimeoffset-types/23908/5 "2019-04-08T02:58:07Z")
It looks like I can use this though to insert the correct, timezoned, date: ```auto now = system.date.now() now_tz = system.date.addMinutes(now, now.getTimezoneOffset()) system.db.runPrepUpdate('INSERT INTO testDateTimeOffset VALUES (?)', [now_tz]) ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 8, 2019, 2:58am UTC](https://forum.inductiveautomation.com/t/runprepupdate-with-ms-sql-datetimeoffset-types/23908/6 "2019-04-08T02:58:40Z")
> [@nminchin](#): > > 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. ~~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. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [April 8, 2019, 3:01am UTC](https://forum.inductiveautomation.com/t/runprepupdate-with-ms-sql-datetimeoffset-types/23908/7 "2019-04-08T03:01:01Z")
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. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [April 8, 2019, 3:06am UTC](https://forum.inductiveautomation.com/t/runprepupdate-with-ms-sql-datetimeoffset-types/23908/8 "2019-04-08T03:06:26Z")
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 😕 Note: I have updated my MS JDBC to the latest version