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

273
4
12-10-2021 10:47 AM
TylerTownes
New Contributor III

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

0 Kudos
4 Replies
ABishop
Regular Contributor III

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?

Amanda Bishop, GISP
0 Kudos
TylerTownes
New Contributor III

@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

0 Kudos
ABishop
Regular Contributor III

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.

Amanda Bishop, GISP
0 Kudos
GintautasKmieliauskas
Regular Contributor II

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
0 Kudos