Query Feature Layer on Date field

[Anonymous User]

Hello Friends,

I am querying a feature layer using python. I want to retrieve all records which have been entered after a particular date and time. Based on this https://www.esri.com/arcgis-blog/products/api-rest/data-management/querying-feature-services-date-ti... blog, I have written my script as below:

featureLayer.query(where="CreationDate> TIMESTAMP '2021-05-18 13:20:00'") 

The above query gets me all records entered on 2021-05-18 and after that. It is not taking the time component 13:20:00 into consideration. How to write the above query so that I can retrieve only records entered after '2021-05-18 13:20:00' ? In ARCGIS online feature layer the date in the date field is stored as 05/18/2021, 1:19 PM.

Any help on this is much appreciated.

Thanks,

Rauf

[MingHome]

Couple of things to check:

1. Check if the date field is in UTC zone. You might have to converte your  '2021-05-18 13:20:00' to UTC.

2. You should be above to use date string instead of TimeStamp, like: "CreationDate> '2021-05-18 13:20:00'"

Good luck!

Ming

[DavidPike]

it looks fine, it may be an issue with how the date is actually stored as opposed to what you see in AGOL.  I would look at the unix epoch time outputs from the query to test that they are probably referencing the TIMESTAMP in UTC and thus throwing off your query.

Try and query the layer in UTC and hopefully you'll get the intended results.

[Anonymous User]

Hi David, The unix epoch time output is "CreationDate": 1621358390873. Could you please share an example on how to query the layer in UTC?

[DavidPike]

So this gives a timestamp of Tuesday, 18 May 2021 17:19:50.873 GMT.

I would check to see that this matches the timezone difference with your initial query, then look to use the datetime module to turn your query into epoch time (milliseconds not seconds) or a UTC string.

Then use that as the datetime in the where clause.

[Anonymous User · Accepted solution]

Hi David,

Sorry for the late response.

I resolved this issue by converting the date to local time zone as below

import pytz
from pytz import timezone

local = timezone('US/Eastern')

naive = datetime.strptime(datevariable, "%Y-%m-%d %H:%M:%S") // here datevariable contains date
local_dt = local.localize(naive, is_dst=None)
utc_dt = local_dt.astimezone(pytz.utc)
queryDate=utc_dt.strftime("%Y-%m-%d %H:%M:%S")

// you can then use this in the query as below

query(where="EditDate > TIMESTAMP '"+queryDate+"'")

Thanks,

Rauf