Using Python to select records by date field

[RachelAlbritton]

I am trying to write a python script to select records created within the past 7 hours (date field - database time).

I've written a script that calculates the time 7 hours prior to the current time:

(Python 2.7.8)

start_time= datetime.timedelta(hours = 7)
end_time = datetime.datetime.now()
report_time = end_time-start_time #this is the time that gets used to filter records

Then I wanted to use the "report_time" variable to select the relevant records in a table view but I'm getting an invalid expression error. I'm not sure if the error is because the SQL expression is incorrect (which is possible) OR if its because there's formatting differences in the date/time structure. For example if I run the above expressions in python shell, the datetime returned is 2016-02-05 03:49:11.322000

The date field in the SDE table is formatted like this:  2/4/2016 2:18:25 PM. I wondering if this matters and if so, how to deal with it.

Here's my current SQL script:

SQL = '"created_date" <= report_time' #this returns an expression error
#SQL = "'created_date' <= report_time" #also tried this - expression error
#SQL = 'created_date'<= report_time # returns error: TypeError: can't compare datetime.datetime to str

print SQL

arcpy.SelectLayerByAttribute_management(ViewTable,"NEW_SELECTION", SQL)

[DanPatterson_Retired]

The section on sql queries with date and time may help

SQL reference for query expressions used in ArcGIS—Help | ArcGIS for Desktop

and there is this Convert Time Field—Help | ArcGIS for Desktop

[DarrenWiens2]

You need to get your query into the format: "Datefield" = date 'yyyy-mm-dd hh:mm:ss'

Use strftime() to format the datetime object.

The following works for a shapefile, which doesn't support time, but you can generally use this pattern:

>>> report_time = datetime.datetime.now()
... SQL = '"OBSDATE" <= date \'' + str(report_time.strftime('%Y-%M-%d')) + '\''
... print SQL
... arcpy.SelectLayerByAttribute_management("MY_LAYER","NEW_SELECTION", SQL) 
...
"OBSDATE" <= date '2016-05-26'

[RachelAlbritton]

I used the reference provided to format the time to match the time format in the ArcSDE table.

report_time.strftime('%m/%d/%Y %I:%M:%S %p')

Its really import that I'm able to query against the time, not just the date or I can't grab only relevant reocrds.

By itself, this prints out the correct format:

02/04/2016 12:03:28 PM

but when I take that and plug it into a where clause I'm still having issues.

# Get start and end times for report

start_time= datetime.timedelta(hours = 24)

end_time = datetime.datetime.now()

report_time = end_time-start_time #this is the time that gets used to filter records

#find all records that are later than or = to report_time

SQL = '"created_date"<='+ str(report_time.strftime('%m/%d/%Y %I:%M:%S %p'))

print SQL

arcpy.SelectLayerByAttribute_management(ViewTable,"NEW_SELECTION", SQL)

Returns:

ExecuteError: ERROR 000358: Invalid expression

Failed to execute (SelectLayerByAttribute).

I also tried it this way (so the attribute is in single quotes) and still get the same error:

SQL = "'created_date'<= "+ str(report_time.strftime('%m/%d/%Y %I:%M:%S %p'))

[RachelAlbritton · Accepted solution]

I figured it out. I opened out the select by attribute tool in arcmap, selected the 'date_created' field and then clicked on get records so I could see how the date-time format looked in the SQL calculator compared to how it looked in the table. They were different! Using this information I reconstructed my where clause:

#Turn ReportsTable into a View Table

arcpy.MakeTableView_management(ReportsTable, ViewTable)

# Get start and end times for report

start_time= datetime.timedelta(hours = 24)

end_time = datetime.datetime.now()

report_time = end_time-start_time #this is the time that gets used to filter records

#find all records that are later than or = to report_time

SQL = "created_date >="+ "'"+report_time.strftime('%Y-%m-%d %H:%M:%S')+"'"

print SQL

arcpy.SelectLayerByAttribute_management(ViewTable,"NEW_SELECTION", SQL)

Note that the quotes before report_time are double- single - double quotes " ' "

Same with the very last set of quotes