How to set up where clause for date when extracting data from a map service using python

06-23-2022 09:30 AM
Occasional Contributor

I successfully used Python 3 to download data from a REST URL using the information from this link How To: Extract data from a map service using Python (

I need help setting up my where clause when using a Date field.

Here is what worked when I used a non date field in my where clause:

params = {'where': 'RoadNumber < "00200"',
'geometryType': 'esriGeometryEnvelope',
'spatialRel': 'esriSpatialRelIntersects',
'relationParam': '',
'outFields': '*',
'returnGeometry': 'true',
'outSR': '',
'returnIdsOnly': 'false',
'returnCountOnly': 'false',
'orderByFields': '',
'groupByFieldsForStatistics': '',
'returnZ': 'false',
'returnM': 'false',
'returnDistinctValues': 'false',
'f': 'pjson',
'token': token

These don't work in my where clause for a date:

# Find today's date and calculate last 5 years
today =
thisyear = today.year
last5years = thisyear - 5
last5yeardate_string = str(last5years) + "-01-01 + 00:00:00"

params = {'where': 'CollisionDate >= last5yeardate_string', 

params = {'where': 'CollisionDate >= ( - dateutil.relativedelta(years=5))',



0 Kudos
4 Replies
MVP Honored Contributor

Depending on the service / DB being queried, the timestamp needs to be formatted a particular way. Try something like timestampe '<your timestamp here>' and see if that works.

Also, you may want to check out the ArcGIS Python API. It's purpose-built for interacting with GIS portals and servers, and I personally find it easier for querying services than using requests.

- Josh Carlson
Kendall County GIS
0 Kudos
Occasional Contributor III

I have a script that takes the current date and subtracts however many days I need off of it, but I'm doing it using a SQL expression.



"SystemEndDate IS NULL AND Type = 7 AND (GETDATE() - last_edited_date) <= " + str(days) + "






0 Kudos
New Contributor II

You could try something like:

params = {'where': "CollisionDate >= '{}'".format( - relativedelta(years=5))}
0 Kudos
Occasional Contributor

Thank you, Josh, Kara, and RViruet,

I was able to get this to work, using SQL date function:

params = {'where': 'EXTRACT(YEAR from CollisionDate) >= "2017"',

0 Kudos