WebDev, Gateway Network, Historians

[MMaynard]

So looking for ideas here.
We have an extensive gateway network with a central historian.
The historical data is in CST on the central gateway.
Locations and users are all across multiple time zones.

We use WebDev with system.tag.queryTagHistory to pull data from the central historian to various systems.
How would you suggest going about pulling data in the clients time zone from the historians?

[pturmel]

@MMaynard

The historical data is in CST on the central gateway.

SQL Server, I presume? (It's java-friendly datetime & datetime2 column types are not tz-compatible.)

You should run your DB on UTC with a connection set to UTC and let Ignition handle all TZ operations.

On MariaDB, use the TIMESTAMP column type instead of DATETIME.

On PostgreSQL, use the timestamptz column type instead of timestamp.

[MMaynard]

The Epoch time is in the t_stamp column of the history tables.
The issue is the built in functions for gathering the data doesn’t have any way to specify the client time zone.
system.db.queryTagHistory()
So the interface just assumes the TZ of the WebDev gateway and returns the data in CST.

I can always massage the returned datasets and modify each record before returning it to the requesting system(s) but that ends up with a bunch more custom code.

[pturmel]

@MMaynard

doesn’t have any way to specify the client time zone.

Not needed. Because:

@MMaynard

and returns the data in CST

But it doesn't. The java.util.Date and java.sql.Timestamp datatypes that end up in datasets are themselves UTC. They don't have an associated timezone at all. You get the effect of a timezone at the point where they are turned into strings. If Perspective, this is the gateway. Vision projects can be told to assume a particular time zone, so stringification there uses that one.

[MMaynard]

I understand that, WebDev acts like Perspective in regards to the “client” timezone conversion of data.
Maybe I need to put this in a feature request against system.tag.queryTagHistory since it is always recommended to purchase and use WebDev when you need to feed data to other systems.

[pturmel]

@MMaynard

feature request against system.tag.queryTagHistory

Won't be possible. Datasets from this function contain java.util.Date objects that don't have a timezone. You have to do it yourself when stringifying the result for the WebDev output. Presuming you know the target timezone, use this procedure:

Update the perspective date component as per system date

Yes, your scripts can take the UTC milliseconds from the java.util.Date Ignition uses and make a java.time.Instant with its static method .ofEpochMilli(). Then, obtain a java.time.ZoneId, usuallly by lookup with .of(). With those two, you can obtain a java.time.ZonedDateTime with its .ofInstant() method. That can then be formatted numerous ways, all respecting the given ZoneId.

[MCC]

How about a case where you are using the system.tag.storeTagHistory() function on a gateway that is in a different timezone than the source of the data? I use system.date.parse() to generate a Date from text in a CSV file. Is there an elegant way to convert that date to UTC before calling storeTagHistory?

[pturmel]

@MCC

I use system.date.parse() to generate a Date from text in a CSV file.

If the text representing the timestamp doesn't include a timezone, the java.util.Date() will be interpreted in the timezone of the gateway running system.date.parse(). Because java.util.Date is always UTC milliseconds under the hood. Consider using more manual parsing with java's DateFormatter class. There are many options. Sounds like you want to parse to a LocalDateTime, then apply UTC to get a ZonedDateTime, from which you can then get the java.util.Date for use throughout Ignition.