Using Python to select records by date field

10181
4
Jump to solution
02-05-2016 10:08 AM
RachelAlbritton
Occasional Contributor III

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)
0 Kudos
1 Solution

Accepted Solutions
RachelAlbritton
Occasional Contributor III

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

View solution in original post

4 Replies
DanPatterson_Retired
MVP Emeritus
DarrenWiens2
MVP Honored Contributor

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
Occasional Contributor III

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

0 Kudos
RachelAlbritton
Occasional Contributor III

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