SQL Server store datetime object with time zone

Ignition
2

Two suggestions:

  1. Don’t use jython’s datetime. Use java.util.Date and related types–that’s what Ignition passes around.

  2. Set MS SQL Server to use UTC and the server it runs on to UTC. That is the only way to have flawless date/time/timezone support in Ignition.

Java’s datetime type is fundamentally built in milliseconds UTC (including serialization when passed over a network) with conversions to and from local time zones as needed. This allows clients and gateways to work with the same object in different time zones and get the right answers.

Microsoft SQL has a DATETIME column type that does not store time zone information. It also has a DATETIME2 column type that does store the origin timezone. Note that storing localtime+origin timezone means any instant in time is overspecified–there are multiple storable values that correspond to any given instant in time.

Java’s JDBC layer supports time zones and additional precision with java.sql.Timestamp, a subclass of java.util.Date. It is fundamentally UTC nanoseconds. Note that there is no reversible transform from DATETIME2, and DATETIME2 is therefore unsupported in JDBC. Hence my recommendation if you are stuck with SQL Server to use the DATETIME column type (which Ignition does) in a pure UTC environment (for the SQL Server–Ignition can be any localtime).

1 Like