Reading/writing to SQL server time zone aware

Ignition
,
1

Using SQL server for logging to custom tables, how does everyone handle timezones?

I would like to use the datetimeoffset type, but ignition didn’t recognise it last time I tried to use it, even after updating the jdbc driver. Then I tried logging to datetime in UTC time zone by converting logged datetimes in python first. But then I don’t know how to convert it back again when reading it back at the client, as it can’t be done in the query as this is executed on the sql server which may have a different time zone… I don’t want to be post-query processing the results in python

For context, I’m logging process batch runs and capturing the stats from each run. But we live in an area with DST and so this ruins logs when it changes over.

1 Like
2

I’ve been lucky to avoid this issue… by using PostgreSQL. Which has always had excellent support for timestamptz columns (the behavioral match for SQL Server’s datetimeoffset, but only to microsecond precision).

(TimestampTZ columns, like Datetimeoffset, use raw storage in UTC, and convert to/from any connection’s timezone. Which yields a java.sql.Timestamp with exactly the right microseconds UTC. And Ignition happily carries those to TZ-aware clients correctly.)

{Sorry, non-responsive to your question. But I don’t know that there is an answer to your question. Which, for me, prompts a search for alternatives.}

1 Like