How do I get the data from HostedFeatureLayer for today's date using python from ArcGIS Online

[ChallagundlaSindhuraITS]

Curently, I am pulling all the data from the featurelayerid using the below script:

feature_layer_id = ' ' Inspform = gis.content.get(feature_layer_id) insp=Inspform.layers[0].query().sdf insp.to_sql('testtable',con=engine,schema=None,if_exists='replace',index=False,index_label=None,dtype=None,method=None)

The featurelayer has two fields with create date and edit date. would like to pull data where createdate or edit date is equal to sysdate.

How can i achieve this? I am new to python and Arcgis

I browsed some sample notebooks, but no luck

[JamesCrandall]

You probably just want feature_layer.query().  Take a look at the docs

[ChallagundlaSindhuraITS]

Thank You, I tried something like this, I dont want to hard code the date. It should be sysdate.

insp=Inspform.layers[0].query(where =" CreationDate > [datetime(2022, 10, 7)]").sdf ; I am trying to figure out the correct syntax

[JamesCrandall · Accepted solution]

It's expecting a formatted date.  I do something similar to setup where clause for querying the last 24 hours on a feature layer.

from datetime import datetime, timedelta

last_24hour_date_time = datetime.now() - timedelta(hours = 24)
timestamp = last_24hour_date_time.strftime('%Y-%m-%d %H:%M:%S')
whereClause = "CreationDate>= '{}'".format(timestamp)

[ChallagundlaSindhuraITS]

Thank You James, The CreationDate format is

2022-10-07 17:46:00.158999808

Tried below statements , but no luck. Please Advise.

inspd=Inspform.layers[0].query(where = "CreationDate>= '{}'".format(timestamp)).sdf
inspd=Inspform.layers[0].query(where = "CreationDate>= '{}'".format(last_24hour_date_time)).sdf

[JamesCrandall]

last_24hour_date_time = datetime.now() - timedelta(hours = 24)
timestamp = last_24hour_date_time.strftime('%Y-%m-%d %H:%M:%S.%f')

nspd=Inspform.layers[0].query(where = "CreationDate>= '{}'".format(timestamp)).sdf

[ChallagundlaSindhuraITS]

This statement also didn't work for me.

last_24hour_date_time = datetime.now() - timedelta(hours = 24)
timestamp = last_24hour_date_time.strftime('%Y-%m-%d %H:%M:%S.%f')
print(timestamp)

2022-12-07 14:44:34.257940

Do you think its because the milli seconds in the creationdate has 9 digits and the timestamp is showing 7 digits and its not getting compared ?

[JamesCrandall]

That's why I added the "%f" in the strftime 

It's hard to say what's the issue.  I don't really undersrtand what the .sdf is about?  Likely issue is something other than datetime formats.

Do this: go to the query task to your feature service and fill in the WHERE input parameter with what you think is correct.  If it works that's the formatting of your WHERE statement in your python script.

[ChallagundlaSindhuraITS]

Did a workaround other than where clause, Below script worked:

from datetime import datetime, timedelta

last_24hour_date_time = datetime.now() - timedelta(hours = 1200)
timestamp = last_24hour_date_time.strftime('%Y-%m-%d %H:%M:%S')
#print (timestamp) #2022-10-19 18:41:09
filterinsp = insp[insp['CreationDate'] >= timestamp] ### this worked to get all 24 hours data
filterinsp

Thanks for your help!!