Querying a date field on a feature layer?

[Anonymous User]

Hi,

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[0]

fset = FL.query(where = 'CreationDate > 2019/03/26')

[Egge-Jan_Pollé]

Hi Karen Smith‌,

I think your code should look like this:

from arcgis.gis import GIS
item = gis.content.get('itemid')
FL = item.layers[0]
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...:

Querying Feature Services: Date-Time Queries 

It says:

You can query esriFieldTypeDate (date-time) fields in two different ways: by DATE or 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'

HTH,

Egge-Jan

[StephenUsmar2]

Hi Egge-Jan,

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!

Karen

[Egge-Jan_Pollé]

Hi Stephen Usmar‌ and Karen Smith‌,

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:

• If you go to your service's REST query page (e.g. https://some_place/some_id/arcgis/rest/services/My_Service/FeatureServer/0/query), the syntax should be:

CreationDate > DATE '2019-03-26'

• In your Jupyter Notebook though, the syntax should be:

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 🙂

Egge-Jan

[StephenUsmar2]

Hi,

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:

• CreationDate (type: esriFieldTypeDate, alias: CreationDate, SQL Type: sqlTypeOther, length: 8, nullable: true, editable: false)

Any advice would be great

[RandyBurton]

A timestamp would be like:

fset = FL.query(where = 'CreationDate > TIMESTAMP 1551355736000' )‍‍‍

[Egge-Jan_Pollé]

Hi Randy,

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

According to Sarah Scott's blog (Querying Feature Services: Date-Time Queries ) the syntax - using this converter https://www.epochconverter.com/ -  should indeed be: 

fset = FL.query(where = 'DATE_LATEST_INSPECTION > TIMESTAMP \'2019-02-08 12:08:56\'')

which in my case works correctly.

Egge-Jan

[jhdn490]

This worked for me, but I do need something for 3 days ago, which I am working on.

[Egge-Jan_Pollé]

Hi Karen/Stephen,

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?

Egge-Jan