Storing and Retrieving UTC from Database

Ignition
,
1

I have a system than where the Ignition Server is setup in Eastern Time and the Database MSSQL is setup in Eastern Time. I have a project that will run where clients will be in more than one time zone.

Due to how many projects are on the Existing Ignition server and the number of systems using the database, changing the system to run in UTC is off the table, plus our DB admins have stated they won't change the servers to UTC.

What is the best way to to work in UTC when the server and the database is in Eastern?

Can I just work with the Millisecond Time and store everything as a bigInt in that database, or should I do some conversion before writing to the database so I can store it as a date in SQL.

2

Use the datetimeoffset column type.

(Or switch to a more competent database, like PostgreSQL, and use its timestamptz column type.)

3

I added a datetimeoffest column type to a test table and I see this error.

Exception: java.sql.SQLException: Unknown SQL type: -155

4

Where and when do you see this error?

5

I see it on my tag Query Binding and in the Named Queries Testing

image

image
image596×373 21.8 KB

6

Looking at some other Form posts, I may just need to update the JDBC driver? Does that not update when updating the server? We are on the latest 8.3

7

Not in 8.1 nor from 8.1 to 8.3. JDBC drivers are now modules in v8.3, so that may change going forward.

8

My MSSQL JDBC Driver is 1.3.6. What is the latest version out?

image

9

13.4

10

This is the Ignition module version.

This is a JDBC driver version.

I don't know if there's a good way to see the JDBC version without looking at the file system / JAR file in 8.3...

11

FWIW I have a database that uses the datetimeoffset column type and to have Named Queries work, I must cast it to a datetime column type. Otherwise, I get the same error.

MS SQL Server Version 15.0.4470, Ignition 8.1.42 for context

12

You lose the timezone offset data when doing this though? Right?

13

Right, it gives you time stamp local to that offset.

14

Good news here - in 8.3.7 there's going to be a (best effort) column on the drivers table showing the JDBC driver version.

Currently we're shipping 13.2.1 in the MSSQL JDBC module, for what it's worth.

15

In case it wasn't obvious from the replies, the ms sql jdbc driver doesn't support the datetimeoffset data type with ignition

16

So without the datetimeoffset field was is the best way in Ignition and MSSQL(which I can't change) to work in UTC time when the servers are both Eastern?

17

If an event happens at 10a Eastern, what time do you want to be displayed for a client viewing that event while on Central time?

18

Probably just do the math to convert to the appropriate time zone, using the system.date functions.

19

Is abandoning the project an option?
I'm only half joking.. Any "fix" you apply will have to be applied everywhere to all queries when dealing with datetimes. And they will be more like "breaks". Gross and not fun