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

**URL:** <https://forum.inductiveautomation.com/t/writing-dates-in-utc-time-using-system-db-runprepupdate/29774>\
**Category:** Ignition\
**Created:** [November 7, 2019, 10:36pm UTC](https://forum.inductiveautomation.com/t/writing-dates-in-utc-time-using-system-db-runprepupdate/29774 "2019-11-07T22:36:43Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**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:** [November 7, 2019, 10:36pm UTC](https://forum.inductiveautomation.com/t/writing-dates-in-utc-time-using-system-db-runprepupdate/29774/1 "2019-11-07T22:36:43Z")

</div>

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!)

---

<div class="post-metadata">

**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:** [November 7, 2019, 10:56pm UTC](https://forum.inductiveautomation.com/t/writing-dates-in-utc-time-using-system-db-runprepupdate/29774/2 "2019-11-07T22:56:07Z")

</div>

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.

---

<div class="post-metadata">

**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:** [November 7, 2019, 11:00pm UTC](https://forum.inductiveautomation.com/t/writing-dates-in-utc-time-using-system-db-runprepupdate/29774/3 "2019-11-07T23:00:22Z")

</div>

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

---

<div class="post-metadata">

**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:** [November 7, 2019, 11:36pm UTC](https://forum.inductiveautomation.com/t/writing-dates-in-utc-time-using-system-db-runprepupdate/29774/4 "2019-11-07T23:36:22Z")

</div>

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

---

<div class="post-metadata">

**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:** [November 7, 2019, 11:56pm UTC](https://forum.inductiveautomation.com/t/writing-dates-in-utc-time-using-system-db-runprepupdate/29774/5 "2019-11-07T23:56:23Z")

</div>

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