Understanding Tag Historian Timestamps in PostgreSQL

[pturmel]

@tyler.bennett

it gives me time in my gateway timezone (not UTC).

No, it is printing the time in the gateway timezone, but the underlying java object is timezoneless.

I recommend you read this topic about datetime objects in Ignition in general:

System.date.now Timezone

The java.util.Date() object returned from any of Ignition's date and time functions, whether expression or script, Has No TimeZone! It is always UTC milliseconds. It is converted to a zoned format when converted to string. So, if converted to a string in the gateway, it will use the gateway timezone. If in Perspective, in the browser, it will use Perspective's client timezone. If you convert to string yourself, in a script or expression in Perspective, since those run in the gateway, you w…

Then consider that PostgreSQL uses the same technique for its timestamp with time zone datatype (aka timestamptz), except that the underlying storage format is UTC microseconds instead of milliseconds (and a non-epoch zero point).

So, timestamptz in PostgreSQL is fundamentally timezoneless, and is only shown with a timezone when converted to a string in an execution context that has a time zone. Same when parsing a string into a timestamp--uses the execution context's timezone, then stores/computes with the UTC microseconds.