Query Feature Layer on Date field

687
5
Jump to solution
05-20-2021 01:47 PM
by Anonymous User
Not applicable

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

0 Kudos
1 Solution

Accepted Solutions
by Anonymous User
Not applicable

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

View solution in original post

0 Kudos
5 Replies
MingHome
New Contributor III

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

0 Kudos
DavidPike
MVP Frequent Contributor

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.

0 Kudos
by Anonymous User
Not applicable

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

0 Kudos
DavidPike
MVP Frequent Contributor

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.

0 Kudos
by Anonymous User
Not applicable

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

0 Kudos