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 df_crash = item_crash.layers.query(where="CCN = '10181486'").sdf df_crash
This code does not work:
... df_crash = item_crash.layers.query(where="REPORTDATE = '2010-12-17 05:00:00'").sdf ...
Nor does this code using unix time:
... df_crash = item_crash.layers.query(where="REPORTDATE = 1292562000000").sdf ...
Nor does this between where clause:
... df_crash = item_crash.layers.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.
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?
@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.
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.