I'm using the Python API to query a feature layer on AGOL, and I'm having issues querying on date fields. I just want to query one feature layer where the date field is after 2019/03/26. I've tried almost every date format possible with no luck - any help would be appreciated!
Here's what I have so far, written in jupyter lab:
from arcgis.gis import GIS
item = gis.content.get('itemid')
FL = item.layers
fset = FL.query(where = 'CreationDate > 2019/03/26')
Hi Karen Smith,
I think your code should look like this:
from arcgis.gis import GIS item = gis.content.get('itemid') FL = item.layers fset = FL.query(where = 'CreationDate > DATE \'2019-03-26\'') # use backslashes to escape the single quotes around the date
I found the answer in this recent blog - thanks to my good old friend G...:
You can query esriFieldTypeDate (date-time) fields in two different ways: by
TIMESTAMP date functions. Each type of date-time query must include a date function to make sure the query is treated in the proper way.
<DateField> = DATE 'YYYY-MM-DD'
<DateField> = TIMESTAMP 'YYYY-MM-DD HH:MI:SS'
Thanks for this - unfortunately it's still not working. Error is:
Unable to perform query. Please check your parameters.
(Error Code: 400)
I've tried both DATE and TIMESTAMP (it's stored as a timestamp, but I don't really care about the time)
Is there anything else I could try? My only other thought is creating a field that is default stored to the time I need to query on, and setting the query to 'CreationDate > StartDate' but I really don't want to do that. There must be an easier way to query a date field!
Hmmmm, it works for me, so maybe it should also work for you ...? I would suggest to try it again. It really comes down to the correct syntax - as soon as the syntax is not completely correct the query will fail. (There is no such thing as slightly correct or more or less OK syntax. Nope, it's got to be perfect, or otherwise it will refuse to run. In that sense computers are just like...)
BTW - I am testing this in ArcGIS Online, which of course always offers the latest and the greatest version ("currentVersion" : 10.61). So, if you are running an older version of Portal, it might be that this syntax is not yet supported?! (What version are you running?)
Anyway, let's try:
CreationDate > DATE '2019-03-26'
fset = FL.query(where = 'CreationDate > DATE \'2019-03-26\'') # i.e. use backslashes to escape the single quotes around the date
Please let us know: is it your version not supporting this functionality OR did you manage to correct the syntax?
Just try to help 🙂
I'm accessing my layers from AGOL, but not sure how to check the version. Would it matter the version of python API installed in my environment?
I can't even use this query within ArcGIS Pro...and this is with selecting an exact time stamp from my data. Once I've saved the layer into a gdb I can query using this method.
Here's the properties of my CreationDate field:
Any advice would be great
I don't think that's correct? When I use your syntax (with an epoch/UNIX timestamp in milliseconds) I get an error:
'where' parameter is invalid
fset = FL.query(where = 'DATE_LATEST_INSPECTION > TIMESTAMP \'2019-02-08 12:08:56\'')
which in my case works correctly.
I am running out of gas here... 😞 Following the documentation it works for me and not for you...?!
The only thing I could think of, but that's just a wild guess, is that you are using an incorrect single quote?!
Maybe someone else has an idea?