nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Encuentra más publicaciones etiquetadas con

Accepted answers

All comments

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

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:

<SPAN class="">CreationDate > DATE '2019-03-26'</SPAN><SPAN class=""></SPAN>

In your Jupyter Notebook though, the syntax should be:

fset <SPAN class="">=</SPAN> FL<SPAN class="">.</SPAN>query<SPAN class="">(</SPAN>where <SPAN class="">=</SPAN> <SPAN class="">'CreationDate > DATE \'2019-03-26\''</SPAN><SPAN class="">)</SPAN>
<SPAN class=""># i.e. use backslashes to escape the single quotes around the date</SPAN>

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 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 Karen Smith‌,

I think your code should look like this:

<SPAN class="keyword token">from</SPAN> arcgis<SPAN class="punctuation token">.</SPAN>gis <SPAN class="keyword token">import</SPAN> GIS
item <SPAN class="operator token">=</SPAN> gis<SPAN class="punctuation token">.</SPAN>content<SPAN class="punctuation token">.</SPAN>get<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'itemid'</SPAN><SPAN class="punctuation token">)</SPAN>
FL <SPAN class="operator token">=</SPAN> item<SPAN class="punctuation token">.</SPAN>layers<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN>
fset <SPAN class="operator token">=</SPAN> FL<SPAN class="punctuation token">.</SPAN>query<SPAN class="punctuation token">(</SPAN>where <SPAN class="operator token">=</SPAN> <SPAN class="string token">'CreationDate > DATE \'2019-03-26\''</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
<SPAN class="comment token"># use backslashes to escape the single quotes around the date‍‍‍‍‍</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.