Managing Time Zones & Time Stamps in Reports

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

  1. Hub Ignition Gateway located in US Central Time Zone - this is where the report will run
  2. Spoke/Location Ignition Gateway located in US Eastern Time Zone - this is where the production data resides
  3. Ignition project on Hub is configured: Project Properties Time Zone Behavior: US/Eastern
  4. 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:

  1. 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)?

  2. 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?

  3. 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.

Thanks in advance!

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.)

3 Likes

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?

Surely Ignition would do the conversion to UTC and pass the modified start and end dates to SQL which would work exclusively in UTC?

Ignition does the work. Using a tz-aware column ensures that non-ignition DB connections are also consistent.

2 Likes

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.

Thank you

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.