I found a bug in the time series chart in perspective, which got me thinking about the way time stamping is handled in Ignition.
To start with the chart, I am feeding it data from a sql query with a t_stamp column of type datetime. Since datetime data doesn’t have any timezone data attached to it, I assumed it would be displayed verbatim in the chart. Instead the chart assumes that the raw value is in the timezone of the gateway and it needs to be converted to the timezone of the client. This is a dashboarding app with data from many different timezones, and the timestamps are always assumed to be local time from wherever the data is coming from. For example, if I am looking at data from a site in Folsom, it is assumed that if I pull in data time stamped at 12p, I know that data is from noon at Folsom. Then if I pull up a dashboard from a site in New York, I know that data at 12p, is noon in New York. This is the reason we are using datetime in SQL. Timezones should never be converted, the data should always be left alone. I was told that in a future release, the timezone conversion would respect the project timezone, so if I set the timezone to gateway timezone, it should work. My question here is this: Why is there a timezone conversion at all, when you can’t know the source timezone in the first place? The component itself really ought to ask for that info as a property.
If Ignition wants to be able to make assumptions about timezones, wouldn’t it make more sense to store time stamps as Unix time? To that point, it would be handy if transaction groups allowed storing the timestamp as Unix time, rather than having to do that manually. I know it’s an easy thing to do yourself, but it seems that IA wants Ignition to be able to tinker with timezones, so why not start pushing people to use a format that allows that to be possible?
Ignition does everything internally in UTC, as 64-bit UTC milliseconds is the actual information in an instance of java.util.Date. If the DB stores timestamps in UTC (like Postgres’ timestamptz), then JDBC will convert directly between java.util.Date and that column correctly in all cases. If the DB doesn’t retain TZ information, then those conversions will use the gateway TZ or a TZ specified in the connection extra properties. Which may not be correct for data sources in another TZ.
Ignition’s Tag Historian uses a 64-bit integer in the DB to store UTC millis in order to avoid brand-specific problems with timezone-aware columns. So if you are using Tag History, your events are almost certainly correctly UTC timestamped.
From there, datetime objects must be rendered for the client. Vision runs in its own process on a client, and can render for the client in any desired timezone (project setting). Perspective is a challenge, because there’re opportunities for misinterpretation through multiple conversions at different points of the stack, especially if string conversions are mixed in. You can always render at the gateway in the gateway’s timezone. A browser can use its own time zone, but you have to have uniform ways to get that into the session object and utilized on both ends of the connection. Non-trivial, and I haven’t actually seen any document describing what IA is doing to remain uniform across all Perspective components. (If anything?)
Now you want a perspective chart to render different data points in different time zones on the same chart? Good luck with that. /:
The behavior that makes sense to me is that a chart should just see a time stamp and roll with it as if time zones didn’t exist. For example it sees a timestamp of 2020-01-01 01:30:00 and it just plots it as is. In my mind this is in line with the sql standard datetime. I suppose there are specific cases where this doesn’t work though, as in when time changes for daylight savings, since that essentially chops an hour off of the time axis.
Would this be the solution then:
- Store all time stamps as Unix time
- Move each site into it’s own project, so that each site can have its own timezone in the project properties, and the client can still navigate between projects via URLs.
- Now each perspective component will know that it is going from Unix time to Project timezone.
So, you want to re-engineer Ignition from the ground up, in a language that doesn’t use UTC for its core Date datatype? I think implementing your “makes sense to me” would be extra-ordinarily difficult and would break far more applications that it would benefit. And no, the SQL standard for datetime is not presumed to be suited to all applications, which is why the SQL standard also specifies timestamp datatypes that are TZ-aware.
Personally, the idea that I’d have to write my code to keep track of all timezones manually for each client just makes me cringe. I want my SCADA package to handle it for me, as Ignition does. I just have to use the SQL standard TZ-aware datatypes and it all magically “just works”.
Maybe you need a better brand of DB.
1 Like
I think I agree with a lot of what you’re saying and just haven’t communicated what confuses me here.
I agree that I want Ignition to handle timezones for me if I am using a TZ-aware format. I’m only confused about Ignition attempting conversions when the datatype is TZ-unaware.
The reason that becomes an issue imo is that Ignition pushes you to use a TZ-unaware datatype in transaction groups. I’m unaware of any way to use TZ-aware datatypes in transaction groups, and I have asked IA a few times about this over the years. My understanding is that I would have to use a numeric type to represent time since epoch.
The issue is that JDBC doesn't have an non-TZ-aware datatype. JDBC translates any DB column that is not TZ-aware to the gateway's timezone (or a specific timezone via the DB connection's extra props).
By default, yes. But you can modify the t_stamp column type yourself to a TZ-aware type. Unfortunately, there are problems with the JDBC driver for SQL Server with datetimeoffset types. And limited resolution with older MySQL's TIMESTAMP type. Consider MariaDB with type TIMESTAMP(3) or PostgreSQL with type timestamptz(3).
(No idea about Oracle--haven't used it in a decade.)
2 Likes
Okay now I get it. Thanks for the explanation, especially on the JDBC.
Also I was unaware that the lack of compatibility for timezone aware datatypes was SQL Server specific. That might be just the leverage I needed to get us off of SQL Server for future implementations.