Having some issues regarding time stored in a database as UTC and rendering properly for clients in different time zones.
Setup: MySQL and Ignition Gateway on the same server set to UTC time. Local client(my dev pc) set to UTC-5. I’m using TIMESTAMP datatypes in MySQL because I read that it considers timezone offset. I’ve got a table with a “start_at” time and date column(TIMESTAMP datatype). When I run a named query on a Perspective Table binding, the start_at column returns in UTC. I tried using system.db.runPrepQuery with the same results.
When I use the Database Query Browser I get the desired result(UTC-5) rendered properly.
I’m guessing this has something to do with the Table bindings being executed on the gateway? How do I return results session time zones to a perspective table?
Just realized that even my Named Query test returns results with a proper offset:
I think you are being tricked by stringification behavior. Ignition datetime objects (java.util.Date instances, always UTC millis under the hood) use the JVM timezone when they are asked for their string representation. If this happens in the gateway, the gateway JVM timezone is used. If this happens in the designer, the designer JVM timezone is used. If delivered to Perspective, it is converted to 64-bit milliseconds UTC and the Perspective component is expected to handle it (some might need extra config, like tables).
Printing or logging or displaying an object causes the object to stringify itself.
Also note that MySQL timestamp column type simply stores UTC. It doesn't try to remember an original zone like MSSQL datetime2. JDBC drivers generally apply a connection time zone when dumb datetime columns are involved, but properly transfer UTC when a utc-aware column type is used.
Thanks @pturmel . We’ve actually had this stringification conversation before when I was trying to show a time in a Label object. For the extra Table config you mentioned, is this something I missed in the props section? Or are you saying I’ll need to do a transform to manually handle this scenario?
props.columns.N.render
"auto" isn't sufficient. You need to deliberately set the column's render to date to get the client browser's time zone.
1 Like
So I found out my session.props.timeZoneId was set to “Etc/UTC”. After manually switching it to “America/Winnipeg” and setting props.columns.N.render to Date I got the result I wanted. However when I launch my client it switches back to “Etc/UTC”, which is the root of my problem I guess. This is on my local PC. I added a button to manually change TZ during a session and that fixed the issue. Not sure why it’s not picking up on my session timezone automatically.
1 Like
Found out my Project Properties>Perspective>General>Project Timezone was set to Gateway instead of Client
1 Like
