topic Re: Cannot Use Timestamp for Where Clause in Python Questions

Cannot Use Timestamp for Where Clause

CodyPatterson — Mon, 01 Jul 2024 13:41:53 GMT

Hey All,

I'm attempting to query a feature layer using the ArcGIS API for Python, and when using the where clause for a timestamp, I'm getting met with errors, even though it's validating within Select By Attributes in Pro.

where = 'EditDate < timestamp \'2024-06-28 00:00:00.000\''

I have attempted everything in this link, but none have worked, very confused on where I am going wrong here: https://community.esri.com/t5/arcgis-api-for-python-questions/querying-a-date-field-on-a-feature-layer/td-p/852126

Thanks in advance!

Cody

Re: Cannot Use Timestamp for Where Clause

jcarlson — Mon, 01 Jul 2024 13:44:32 GMT

Try putting the whole thing in double quotes?

"EditDate < timestamp '2024-06-28 00:00:00'"

The SQL expression might be valid, but timestamp formatting will depend on the underlying database. Where is the data coming from? Postgres? MSSQL? A file?

Re: Cannot Use Timestamp for Where Clause

CodyPatterson — Mon, 01 Jul 2024 13:50:19 GMT

Hey @jcarlson

I just attempted that with no success unfortunately, this is coming from AGOL through their Hosted Feature Layers, I'm not entirely confident in the DB behind it unfortunately.

Cody

Re: Cannot Use Timestamp for Where Clause

jcarlson — Mon, 01 Jul 2024 13:55:34 GMT

Any chance the service is public? I would be curious to attempt a query.

According to the REST API docs, you're formatting it fine…

https://developers.arcgis.com/rest/services-reference/enterprise/query-feature-service/#date-time-queries

Re: Cannot Use Timestamp for Where Clause

CodyPatterson — Mon, 01 Jul 2024 14:40:02 GMT

Hey @jcarlson

This service currently isn't public unfortunately, it has proprietary information and could not be shared that way, but if there is a public service you are aware of, I can definitely retry the query, or create my own that is blank that I could test with or send over to test with!

Cody

Re: Cannot Use Timestamp for Where Clause

jcarlson — Mon, 01 Jul 2024 15:21:21 GMT

Here's a living atlas layer:

https://services9.arcgis.com/RHVPKKiFTONKtxq3/ArcGIS/rest/services/USA_Wildfires_v1/FeatureServer/0/query?where=FireDiscoveryDateTime+>+TIMESTAMP+'2024-01-01+00%3A00%3A00'

Is it as simple as putting TIMESTAMP in all caps? I haven't known it to be case-sensitive in the past, but if yours isn't working, I'm at a loss. It's a DateTime field, not a Date Only?

Re: Cannot Use Timestamp for Where Clause

CodyPatterson — Mon, 01 Jul 2024 15:58:06 GMT

Hey @jcarlson

That worked perfectly fine in a GET request using the API, but unfortunately it did not work at all when I used it with the query(where={where clause}) function, I'm assuming it's not converting into URLEncoded parameters, what I may do is just use a URLEncoder to attempt and just build the GET request over the URL. I reformatted my original request in that format you have in the URL, and it seems to have worked, but this will require a ton of rewrite for what information it's looking for. Very odd that it doesn't work on the query function!

Cody

Re: Cannot Use Timestamp for Where Clause

jcarlson — Mon, 01 Jul 2024 16:08:24 GMT

I hate to say this, but… it works fine over here.

Re: Cannot Use Timestamp for Where Clause

CodyPatterson — Mon, 01 Jul 2024 17:30:49 GMT

Hey @jcarlson

I've figured it out, your example worked just fine with mine, as soon as I took out the out_fields. There was an invalid field within the query, specifically SHAPE was represented as SHAPE@ and I just assumed it was right. I went through and found it and once fixed, everything started going well. I'll put in an idea to possibly add a notice for when the fields are invalid rather than having a very lacking error message!

Thank you for your help!

Cody