We have a hub-and-spoke architecture with a central hub where reporting runs. All data resides at each operating location, often in different time zones.
We are having challenges with building reports that display the correct time and date for the time zone where the production is taking place. Specifically, when we are in Designer to build and preview a report we see a different timestamp (offset by the time zone differential) than when the report runs as scheduled. This situation occurs even when we are running the Designer in the same time zone as the central server.
Example
Hub Ignition Gateway located in US Central Time Zone - this is where the report will run
Spoke/Location Ignition Gateway located in US Eastern Time Zone - this is where the production data resides
Ignition project on Hub is configured: Project Properties Time Zone Behavior: US/Eastern
Running Report Designer in central time zone (same TZ as the Ignition Gateway) the report preview shows shows batch end time of 15:25. When the report is generated automatically and emailed the end time for the same batch shows 14:25
We can certainly trial-and-error and figure out how to make this work by setting a report parameter for the hour correction factor, but would like to hear if:
Is it a bug or feature that the Report Builder displays a different time in preview mode and when run as scheduled (as described in the Example above)?
Has anyone figured out a programmatic way to handle preparing a report when the data resides in a different time zone from the Ignition gateway?
Has anyone figured out a programmatic way to handle sending reports at a consistent time of day when we transition to/from daylight savings? Of course we happen to have gateways in areas that are exceptions with respect to daylight savings so the time difference changes depending on DST.
Store and manipulate all times, timestamps, date boundaries, and shift boundaries in UTC. Transform inputs and configurations to UTC and transform for display in local time.
Use DB column types that can resolve instants in universal time instead of any local time. (For example, PostgreSQL's timestamptz instead of timestamp.)
Phil, will postgre do all the timezone manip for you? For example, if all records are store using timestamptz, and a client requests records from 2023-03-01 onwards (iso date format), local to the client, will postgre convert that into utc and return the right results?
Thank you Phil, I fully agree and would follow this if I had control of my data universe. Unfortunately I am in a MS SQL environment and Ignition is last to the party. I will work begin trying to at least get timezone aware type datetimeoffset
I'm interested if anyone else has similar experiences to my 2nd #1 above, bug or feature, where Reports have different outputs in Design vs Run time.
You can mitigate the need for a tz-aware column if both the gateway and the DB operate in UTC. Then everything the gateway stores into the database as "localtime" is in fact always correct. Ignition can then do the rest of the work.
Time Zones are the bane of my existence. We have our hub and DB servers on UTC time (and using MS SQL), but that still doesn't resolve the headaches of juggling timezones for Ignitions built-in historian. For example, if you have a datetime dropdown or calendar component and you use scripting to initialize the values for midnight and end of day (for display purposes to the end-user in their timezone), you have to adjust the value returned by system.date.midnight(system.date.now()) by the timezone of the user. Then the user selects a date range they want for the report and press submit. Here you have to reverse the adjustment to request the UTC date range. This is because the user wants a daily report from midnight to midnight, but they don't know (or care) which timezone this site should be in.
Within the report designer you have to do the same type of massaging of data but with respect to the timezone adjustment of the spoke gateway's data. In other words, if you request midnight to midnight from system.tag.queryTagHistory(....) on the server, the dates will be in UTC. And if you try displaying the data on graphs, it won't align correctly to the X-axis starting at midnight. So you have to loop through the rows of your data and adjust the datetimes. We also keep a database listing of spokes and the timezone they belong in so that if you run a report on site A (located in CST) from midnight to midnight you get a report that shows data on the graphs correctly as if you were in CST even though you might be in EST. Confused yet? Exactly.
@matsons, our reports are 100% scripts for this reason (and because of the flexibility). We use java.time.ZoneId and java.time.ZonedDateTime. With these two classes, we find the offset in seconds from the users time and UTC for both start and end times...you never know if the start is during daylight savings and the end is after. We subtract the offset from start and end times accordingly then query the tag historian. When it is time to revert back, we add the seconds back to each date in the returned dataset.
Inductive folks provided system.dataset.formatDates() function which helps when formatting the X-axis on Category charts. It would be great if they provided a system.dataset.setTimeZone() to adjust all dates. Or, maybe a timezone parameter to system.tag.getTagHistory() to return the dates in the specified timezone.
Don't even get me started with system.util.setLocale() in reports. It will format dates, but doesn't always format numbers unless you run the report twice or so.
