topic Re: Calculate Number of Days from Current Date in Python Questions

Calculate Number of Days from Current Date

DevinUnderwood2 — Tue, 30 Jan 2018 23:21:41 GMT

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 ?

Re: Calculate Number of Days from Current Date

JamesMacKay3 — Tue, 30 Jan 2018 23:27:26 GMT

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

Re: Calculate Number of Days from Current Date

DevinUnderwood2 — Tue, 30 Jan 2018 23:30:15 GMT

Oracle

sde features

Re: Calculate Number of Days from Current Date

JamesMacKay3 — Sat, 11 Dec 2021 12:30:12 GMT

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.

Re: Calculate Number of Days from Current Date

DevinUnderwood2 — Tue, 30 Jan 2018 23:47:38 GMT

Thanks for the advice.

SYSDATE - Is this from the import sys module ?

I tried this but with an error using SYSDATE

Re: Calculate Number of Days from Current Date

JamesMacKay3 — Tue, 30 Jan 2018 23:50:46 GMT

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.

Re: Calculate Number of Days from Current Date

RandyBurton — Sat, 11 Dec 2021 12:30:14 GMT

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

Re: Calculate Number of Days from Current Date

JoshuaBixby — Sat, 11 Dec 2021 12:30:17 GMT

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.

Re: Calculate Number of Days from Current Date

DevinUnderwood2 — Sat, 11 Dec 2021 12:30:20 GMT

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)‍‍‍‍‍‍‍‍‍

Re: Calculate Number of Days from Current Date

TedKowal — Sat, 11 Dec 2021 12:30:22 GMT

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

Re: Calculate Number of Days from Current Date

DevinUnderwood2 — Wed, 31 Jan 2018 23:21:42 GMT

I am confused on line 9

dt_i = cur.fields.index(dt_fld)

Where did you get index from ?

Re: Calculate Number of Days from Current Date

DevinUnderwood2 — Wed, 31 Jan 2018 23:33:55 GMT

I modified slightly on just the variable names but get the error.

ValueError: tuple.index(x): x not in tuple

with arcpy.da.SearchCursor(fc1, datefield) as cur:
    dt_i = cur.fields.index(datefield)
    dt_filtered = (rec for rec in cur
                   if rec[dt_i] and datetime.now() - rec[dt_i] <= timedelta(30))
    for row in dt_filtered:
            print ("{0}".format(row[0]))

Re: Calculate Number of Days from Current Date

JoshuaBixby — Wed, 31 Jan 2018 23:46:00 GMT

The fields property for a cursor returns a list, which has an index method.

Re: Calculate Number of Days from Current Date

JoshuaBixby — Wed, 31 Jan 2018 23:56:41 GMT

I really need to see the whole code block since you are defining variables upstream.

The error message itself is saying that datefield is not present in the fields from the cursor, which does seem odd given that is the only field you passed. Maybe it is a case mismatch, I am not sure.

Provide more code and also put a print statement in for datefield and even cur.fields.

Re: Calculate Number of Days from Current Date

DevinUnderwood2 — Sat, 11 Dec 2021 12:30:25 GMT

import arcpy
import datetime
from datetime import timedelta

# Set Feature Variables
fc1 = r'Database Connections\a.sde\BACK.wNetwork\BACK.wSystemValve'
datefield = ['LASTUPDATE']

with arcpy.da.SearchCursor(fc1, datefield) as cur:
    dt_i = cur.fields.index(datefield)
    dt_filtered = (rec for rec in cur
                   if rec[dt_i] and datetime.now() - rec[dt_i] <= timedelta(30))
    for row in dt_filtered:
            print ("{0}".format(row[0]))‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Calculate Number of Days from Current Date

RandyBurton — Thu, 01 Feb 2018 02:37:35 GMT

bixb0012‌, thanks for sharing the code. It has been added to my snippets collection.

Re: Calculate Number of Days from Current Date

DevinUnderwood2 — Thu, 01 Feb 2018 14:52:00 GMT

Your welcome,

However, this doesn't work though, I need to work out the error still.

Re: Calculate Number of Days from Current Date

RandyBurton — Sat, 11 Dec 2021 12:30:28 GMT

datefield can be a list in line 9, but not in line 10. Try:

datefield = ['LASTUPDATE'] # list

with arcpy.da.SearchCursor(fc1, datefield) as cur:
    dt_i = cur.fields.index(datefield[0]) # first element in list

# or - as in Joshua's original code

datefield = ['LASTUPDATE']
dt_fld = 'LASTUPDATE'

with arcpy.da.SearchCursor(fc1, datefield) as cur:
    dt_i = cur.fields.index(dt_fld)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Calculate Number of Days from Current Date

DevinUnderwood2 — Thu, 01 Feb 2018 17:52:41 GMT

Thanks for the advice.

I still have the following issue.

TypeError: unsupported operand type(s) for -: 'datetime.date' and 'datetime.datetime'

Re: Calculate Number of Days from Current Date

RandyBurton — Sat, 11 Dec 2021 12:30:30 GMT

That is probably related to how you are importing the datetime modules.

# insead of

import datetime

if rec[dt_i] and datetime.datetime.now() - rec[dt_i] <= datetime.timedelta(30))

# use this

from datetime import datetime, timedelta

if rec[dt_i] and datetime.now() - rec[dt_i] <= timedelta(30))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The second method of importing modules will save some typing and make the code easier to read, as illustrated in lines 5 and 11 above.