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
