Calculate Number of Days from Current Date

[DevinUnderwood2]

I am trying to use a search cursor to look  up a date field to only return LastUpdates within the last 30 days.

today = datetime.date.today()

I want to use the datetime and date field types which are in different formats and is why I can't calculate the difference.

How can I format them the same to accomplish this ?

[JamesMacKay3]

What kind of data source are you working with - File GDB, Shapefile, SQL Server, Oracle, etc.?

[DevinUnderwood2]

Oracle

sde features

[JamesMacKay3]

I'm not sure of the Oracle date function (don't often work with it) but there should be a DB "current date" function you can use as part of the cursor's where clause.

A quick search says it's sysdate and you can use integer operators on it, so you could do something like this (theoretically, I can't test it):

with arcpy.da.SearchCursor(fc, "*", ‍"LastUpdates > SYSDATE - 30") as cursor:
    for row in cursor:
        # Do stuff‍‍‍‍‍‍

I've used this kind of where clause as Definition Expressions (albeit SQL Server equivalent), can't see why it wouldn't work as a cursor's where clause.

[DevinUnderwood2]

Thanks for the advice.

SYSDATE - Is this from the import sys module ?

I tried this but with an error using SYSDATE

[JamesMacKay3]

It's not a Python function, it's an Oracle function that's executed by the database when the cursor is created.  No need to import anything on the client/Python side.

[TedKowal]

would not the where if it is passed to the oracle database be something like this .... 

date_column BETWEEN SYSTIMESTAMP - INTERVAL '30' DAY

http://www-01.ibm.com/support/docview.wss?uid=swg21262277

[RandyBurton]

One possible way with Python:

from datetime import datetime, timedelta

daysAgo30 = datetime.now() - timedelta(days=30)

dateSearch = "dateField > date '{}'".format(datetime.strftime(daysAgo30,"%Y-%m-%d %H:%M:%S"))

result: "dateField > date '2017-12-31 15:35:32'"‍‍‍‍‍‍‍

The format for the where clause will vary depending on the type of database used.  You may want to format only for Y-D-M, omitting the time part.  Code above should work for a file geodatabase.  See also: SQL reference for query expressions used in ArcGIS

[DevinUnderwood2]

Thanks, this works by confirming the calculation of date difference. timedelta is definitely the key.

I will now work on incorporating Joshua's example to use a cursor.

import arcpy
import datetime
from datetime import timedelta

# Set Date Variables
today = datetime.date.today()
daysAgo30 = today - timedelta(days=30)
print "Today is: " + " " + str(today)
print "30 days ago is:" + " " + str(daysAgo30)‍‍‍‍‍‍‍‍‍

[JoshuaBixby · Accepted solution]

If you are going to be working with a cursor anyways, I suggest doing your date filtering with the cursor using Python.  That way, your code will be agnostic of back-end data store and more portable.  You can use a generator expression to act as the date filter:

import arcpy
from datetime import datetime, timedelta

tbl =    # path to table or feature class
flds =   # list of field names for cursor, including date fields
dt_fld = # name of date field

with arcpy.da.SearchCursor(tbl, flds) as cur:
    dt_i = cur.fields.index(dt_fld)
    dt_filtered = (rec for rec in cur
                   if rec[dt_i] and datetime.now() - rec[dt_i] <= timedelta(30))
    for row in dt_filtered:
        # do whatever ‍‍‍‍‍‍‍‍‍‍‍‍‍

I added an extra check, if rec[dt_i], to ensure the code won't error if a date is missing.  I assume if a date is missing you do not want the record returned.