Gut check - UTC datetimes in MS SQL and Scripting

Ignition
1

I just need a gut check on time zones and datatypes.

I have a server running MS SQL and Ignition set to UTC time. I have quite of few queries using the GETUTCTIME() MS SQL function. I had the same project running in two time zones (EST AND HST) and to keep my sanity, I switched EVERYTHING to UTC. My computer, servers, etc. Now, more people are using it so I'm switching back to client side time zones. Luckily we're only in EST now.

Based on all my testing, I should be ok since most scripts are gateway scope (UTC still) and Ignition does a decent job converting time zones for data entry. Backend, everything should still be UTC. So I'm setting my project time zone to CLIENT or EST, depending on feedback from co-workers.

BUT, am I missing anything?

2

If both gateway and database use UTC, then JDBC will deliver instances of java.util.Date from MS SQL Server DATETIME columns in UTC. Ignition can then pass those around to clients with any time zone and the value will be interpreted correctly in every time zone. Just make sure that queries always use value parameters (in named queries) or question mark parameters (in scripted "Prep" queries) for date/time content. This ensures the gateway will have UTC for the database.

Let your clients (Vision or Perspective) auto-format to strings for display.

If you do that, you should be good.

1 Like
3

Great, Thanks Phil.