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.