topic Querying a date field on a feature layer? in ArcGIS API for Python Questions

Querying a date field on a feature layer?

Anonymous User — Wed, 03 Apr 2019 01:39:16 GMT

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')

Re: Querying a date field on a feature layer?

Egge-Jan_Pollé — Sun, 12 Dec 2021 10:29:50 GMT

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

Re: Querying a date field on a feature layer?

StephenUsmar2 — Sun, 12 Dec 2021 10:29:52 GMT

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

Re: Querying a date field on a feature layer?

Egge-Jan_Pollé — Sun, 12 Dec 2021 10:29:55 GMT

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

Re: Querying a date field on a feature layer?

StephenUsmar2 — Wed, 03 Apr 2019 23:16:40 GMT

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

Re: Querying a date field on a feature layer?

RandyBurton — Wed, 03 Apr 2019 23:27:54 GMT

A timestamp would be like:

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

Re: Querying a date field on a feature layer?

Egge-Jan_Pollé — Thu, 04 Apr 2019 08:06:58 GMT

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

Re: Querying a date field on a feature layer?

Egge-Jan_Pollé — Thu, 04 Apr 2019 08:12:21 GMT

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

Re: Querying a date field on a feature layer?

jhdn490 — Tue, 05 Oct 2021 15:39:52 GMT

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

Re: Querying a date field on a feature layer?

Manager_HMWSSBCDC — Fri, 10 May 2024 07:54:31 GMT

Hi Egge-Jan,

Its worked for me but if I want to add filter with date range, Ex:

lyr.query(where="field_name = 'value'" and "date_field = start_date_value AND date_field = end_date_value)
How to achieve this kind of solution.