ArcGIS Enterprise 10.9 Datetime Handling for Dynamic Query Layer

cle444

Call for ArcGIS Enterprise experts! This is a queston about UTC and local time handling when publishing registered dynamic query layers to Enterprise.

Use case:
Publish a map image service that contains XY event layer that is based on a referenced SQL query table. The SQL query table is a simple SELECT query with a field of datetime data type. The datetime value is stored as a plain local time value without timezone and daylight saving information.

When pulishing to ArcGIS Enterprise via ArcGIS Pro Share as Web Layer, the local time zone and daylight saving option are configured in the sharing wizard.

The issue:
ArcGIS Enterprise doesn't respect the time zone and daylight saving configuration for such dynamic layers. Instead, it treats the value as UTC and convert it to local e.g. adding 13hours for NZ daylight saving time.

However, there is no issue if published from a static feature class from a file gdb. Extactly same data and same publish process.

Software:
Client - ArcGIS Pro 2.8
Server - ArcGIS Enterprise 10.9

Test: Publish a map with two layers of exactly same data, one from registered feature class and one based on a SQL query table.

1. Prepare workspace as below. Both layers are registered on the target ArcGIS Server.

2. Check value from local ArcGIS Pro using identify and popup. Same value 13/10/2021, 12:33pm for both layers.

3. Publish as map image layer.

configure timezone and daylight saving

4. Publish to Enterprise is successful. Check on map viewer from Portal.

Question:
With timezone and daylight saving configured during publish process, why Enterprise treats feature class layer correctly but not Event layer based on SQL query table?

For the query table and XY event layer, no matter what timezone you choose during the publish stage, NZ+12:00 or UTC or <None> or any other, Enterprise always treats it as UTC and converts it to local time, in my case, +1300.

Any ideas and insights would be much appreciated!

Thanks,
Hua

ahoque

Thanks @cle444 . I'm glad it worked for you.

I don't have answers for all the questions you have asked at least not at the detail level you hoped for. Let me try.

is it a Pro issue rather than the Enterprise issue?

The bug you ran into is a map service bug on the Enterprise side.

Is the 'Make XY Event Layer' a helper method that can handle different database technologies?

If I recall correctly, it is not.

For the SQL server, what you provided is how 'Make XY Event Layer' works behind the scene?

No, it doesn't. 'Make XY Event Layer' is a complete different implementation.

What is the main difference between the two layer types?

If I understands correctly, a 'XY Event Layer' reads rows from the table and generates geometries using x/y coordinates on the client side. Whereas the approach that I provided generates the geometries at the database level. Pro/map service simply gets the geometries (just like for any other query layer or feature class) and draws them.

In what scenarios you would use a query feature class layer instead of an XY Event layer, and vice versa?

Unfortunately I don't have any answer for this 😞 I need to think through, if I find any I will reply back here.

As you might have figured already, functionaly they are pretty similar. Of course one draw back for the query-layer approach is that you need to know sql and database specific spatial functions. Itonly works when your data are stored in an enterprise database. This approach wouldn't work for file gdb or shape files etc.

 

hope you will find this helpful!