topic GIS() feature item to sdf, where between date and date (ArcGIS API for Python) in Python Questions

GIS() feature item to sdf, where between date and date (ArcGIS API for Python)

TylerTownes — Fri, 10 Dec 2021 18:47:43 GMT

Creating a sedf from an AGOL is relatively straight forward, however, I hit a major stumbling block trying to query dates. I will write four codes blocks following, one that works, and three that don't, to see if somebody can point out my error. Ultimately I need to apply a where clause by the REPORTDATE column.

This code works, with where clause filtering a known CCN:

import pandas as pd from arcgis.gis import GIS gis = GIS() search_result = gis.content.search('title:crashes in dc AND owner:DCGISopendata') item_crash = search_result[0] df_crash = item_crash.layers[0].query(where="CCN = '10181486'").sdf df_crash

This code does not work:

... df_crash = item_crash.layers[0].query(where="REPORTDATE = '2010-12-17 05:00:00'").sdf ...

Nor does this code using unix time:

... df_crash = item_crash.layers[0].query(where="REPORTDATE = 1292562000000").sdf ...

Nor does this between where clause:

... df_crash = item_crash.layers[0].query(where="REPORTDATE BETWEEN 1292562000000 AND 1293512400000").sdf ...

I've been banging my head against the wall on this for a while now. I've tried other permutations of stings, no strings, unix time, iso time.

I have noticed three distinct time formats...AGOL Data shows 12/17/2010, 12:00AM, the arcgis FeatureSet shows unix time 1292562000000 and finally the sedf shows iso 2010-12-17 05:00:00. I'm less concerned about GMT versus local and EXACT times and more concerned about a range/between filter, so really would like to get the between working, just thought I would start with equals for testing.

Let me know if you have any pointers.

Thank you,

Tyler

Re: GIS() feature item to sdf, where between date and date (ArcGIS API for Python)

ABishop — Fri, 10 Dec 2021 19:16:18 GMT

Hello Tyler,

I am a bit of a novice at python scripting but I think I may see a typographical error? Seems that you should put the single quotes around the date time attribute as you have it formatted in the first script that you said works?

Re: GIS() feature item to sdf, where between date and date (ArcGIS API for Python)

TylerTownes — Fri, 10 Dec 2021 19:45:49 GMT

@ABishop Unix time is an integer data type I believe, so no quoting should be required, whereas the CCN is a string. The CCN, by the way, was just for testing to ensure my code was working. In any case I had tried quoting the unix time to no avail anyway. Thanks for the observation.

Tyler

Re: GIS() feature item to sdf, where between date and date (ArcGIS API for Python)

ABishop — Fri, 10 Dec 2021 20:07:14 GMT

Just a hunch... try building this same thing in a definition query window. Or even try replicating this workflow in model builder and then export the code to python script. See if you get the same resutls.

Re: GIS() feature item to sdf, where between date and date (ArcGIS API for Python)

GKmieliauskas — Mon, 13 Dec 2021 06:30:07 GMT

Hi,

Referring to the article SQL reference for query expressions used in ArcGIS your query must be like this:

df_crash = item_crash.layers[0].query(where="REPORTDATE = date '2010-12-17 05:00:00'").sdf