ArcGIS Datastore Default Timezone
Hey All,
Just wondering if there are ramifications for altering the default timezone within the postgresql.conf file in an ArcGIS Datastore deployment?
The reason is as follows:
We need to be able to set a query on a field in a hosted feature layer, which makes use of CURRENT_TIMESTAMP. We're using ArcGIS Online Assistant to alter the default query within the Web Map to be (we understand that this is non-standard):
This query is saying, when the current time passes the expiry datetime field, then we will no longer display the feature in the Web Map.
The above query works in a Web Map, when using AGOL, and my guess is this is because the underlying DB storing the data in AGOL returns CURRENT_TIMESTAMP as UTC. Whereas in a Datastore deployment, CURRENT_TIMESTAMP returns in the local timezone where the database was installed. This is verified by running the query manually against the Datastore PostgreSQL DB, or checking the postgresql.conf file.
The problem is that features appear to store their datetime data as UTC within the database and the conversion to local is done within Portal itself (verified by querying the ArcGIS Server REST endpoint). Therefore the above query is querying the 'expiry' field as UTC against CURRENT_TIMESTAMP in the local timezone, which does not produce the correct results.
If we edit the postgresql.conf file and set the timezone to UTC:
then CURRENT_TIMESTAMP does return as UTC, which will create a valid query above.
Is there a reason the DB is deployed using the local timezone settings? and, what are the ramifications for altering this setting to be UTC?
Cheers,
Jordan
