Dynamic DATE query not working properly on Web Map

[DiegoLlamas]

Ok, so I have this layer in a web map with point features that represent a type of incident. Each incident has a date of occurrence (a column with a date format named fecha) associated to it with no information on time (dd/mm/yyyy). I want the web map to filter the layer so that it only shows incidents that occurred this year by editing its JSON file (using AGOL Assistant) and inserting a dynamic date SQL Query.

Here is what I’ve been trying to use:

                (EXTRACT(YEAR FROM fecha) =  EXTRACT(YEAR FROM CURRENT_DATE))

The query filters the layer by equating the year from the DATE column (fecha) to the year corresponding to the current date.

The query works, but it returns all the incidents that occurred on the current year (2019) except for December 31 of that year. However, it also includes the incidents occurring on December 31 of the previous year (2018). I don’t know if someone has already experienced this and if there’s a workaround.  

[Anonymous User]

Hi Diego,

Would you be able to attach an exported sample of your data here? What is your local UTC time zone?

Without testing on my side it's hard to be completely sure, but I think what is occurring is that ArcGIS Online stores date fields in UTC 0, then Map Viewer/ArcGIS Online/Field Apps/Web Apps read the time zone of the browser/device and convert the date/time to local on the fly. The query is against UTC 0 instead of the local time zone, and when the Web Map re-converts the date field back to Local Time the time shift changes the year for Dec 31. 

The workaround here could be to create and calculate a field to store Local Time in ArcGIS Pro, then base your Web Map Filter on that field. Another option may be to use the toLocal Arcade function to calculate a field in the Web Map, then set the Web Map filter on this field. 

Hope this helps,

-Peter