SQL Queries and Time Differences

[WildeKurt]

A particular gateway I'm working with is giving odd results when returning queries that contain a time type. When I make the query in SSMS (this is MS SQL) the times retuned are correct although they have a date component. When the same query is done inside Ignition, either via script of the Query Browser, they are off by one hour (less). The time zone of the gateway is correct and anyway wasn't even aware that SQL datetime or time types were time zone aware.

I do understand that the returned time type is cast into a java Date time so the date is always Jan 1, 1900 which is fine. I'm just perplexed about the time being off and unsure of how to address it. Any help would be appreciated.

Here's an example query:

SELECT start_time FROM _shift WHERE department_id=? AND shift_number=?

When issued from Ignition I get: 1900-01-01 06:00:00.000. When done in SSMS I get 07:00:00.0000000

Kurt

[Matrix_Engineering]

SQL Server store datetime object with time zone

Two suggestions: Don’t use jython’s datetime. Use java.util.Date and related types–that’s what Ignition passes around. 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…

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.