We are storing alarms in an alarm journal. The alarm journal table is auto created with the field 'eventtime' being a timestamp without timezone, just regular Ignition.
My gateway time is set at the physical timezone of the server, so not utc. The project timezone is set to client timezone.
My issue: When an alarm is triggered and stored in the alarm journal, it's stored in the database not in utc time, but in the gateway time, even though the column is a timestamp without timezone.
What is best practice here? Should the gateway be set to utc time to fix this? Or am I doing something wrong?
That's what "timestamp without timezone" means--it implies the timezone of the database connection, which is initiated by the gateway. Ignition doesn't use any non-timezone datetimes in its code, but java.util.Date and friends, which are UTC milliseconds under the hood. That is, they specify a unique moment in time without regard to timezone, and are thereafter converted to and from local timezones as needed.
That means that all datetimes pulled from a database that don't have timezone will be assumed to be in the gateway's timezone and the UTC millis established from that. No, there's no way to change this behavior.
In other words, timestamptz is almost always the correct choice in Ignition. FWIW, in PostgreSQL, that column type consumes exactly the same space as the timestamp type, but the 64-bit microseconds they use under the hood is interpreted differently in the DB--UTC versus localtime.
Some databases (MS SQL Server) have timestamptz-ish column types (datetime2 in MS SQL) that don't play nicely with Ignition, usually because they are overspecified for a unique instant in time. With such DBs, you should set the gateway to UTC so the timestamp type works correctly (like your case).
"That" means the original timestamps (UTC millis under the hood) will be converted to DB connection's timezone for the localtime conversion and stored that way. Then when queried, the localtime pulled from the DB is again converted to UTC using the DB connection's timezone. Just because you are storing them without a timezone doesn't mean Ignition can use them without a timezone. It's just implied.
You chose a column type that is always localtime. timestamptz is always UTC.
Yes. Ignition doesn't use any localtime objects. So your "localtime" is converted to a UTC-based object when retrieved from the DB (using gateway TZ), dispatched where needed in Ignition in that form, then converted to the client timezone for display.
No, it is doing precisely what you asked. Your mistake is assuming that Ignition can move timestamps around in any localtime format.
I recommend that to prevent ambiguity, particularly if the database is accessed by any non-Ignition means that have a different connection timezone.
Your mistake is thinking a localtime retrieved from a DB is treated any differently than a UTC-based timestamp retrieved from your DB. They are all converted to a UTC-based format at the point of retrieval.
So if I understand you correctly, Ignition auto-create-table for the alarm journal isn't serving me well and I should change the data type of that column so it has a timestamp with timezone data type.