topic Re: Using where clause to query date ranges in ArcPy in Python Questions

Using where clause to query date ranges in ArcPy

GeoffreyWest1 — Mon, 06 Apr 2015 14:10:10 GMT

I would like to query a feature classes date range. For instance, if there is data within a 2 day range then I would like to print true or do something else. I thought arcpy.Exist would do the job, but I am missing something here. I guess my arcpy.Exist is only seeing if the variable exists, how would I check to see if the actual where clause is True for my feature layer?

query = arcpy.MakeFeatureLayer_management(fc, "QueryLayer", """Date BETWEEN '2014-02-16 16:53:25' AND '2014-02-17 18:53:25'""") try: query finally: if arcpy.Exists(query): print "true" else: print "no data"

Re: Using where clause to query date ranges in ArcPy

JamesCrandall — Mon, 06 Apr 2015 14:30:55 GMT

My guess is your sql is failing. Simplify the sql to something you know works to see if .Exists is true. Make it super simple like "OBJECTID = 1" or something like that, if it prints "true" then you know your sql needs to be fixed. You should get an ExecuteError 000230 if it fails to apply the selection.

Otherwise, I get a "true" print statement with your code above.

But I'm not completely sure your approach is doing what you want. Can you clarify exactly what you want to do? Because I can't fully understand. Maybe you want to count?

arcpy.GetCount_management(query)

Re: Using where clause to query date ranges in ArcPy

VinceAngelo — Mon, 06 Apr 2015 14:40:01 GMT

Surely the result will exist, even if it was empty.

What you really want is the count of the features in this new layer, but instead of creating new objects with a query, I'd think you'd be better off accessing the selection environment, to obtain the number of features selected.

You should also know that the syntax of temporal queries differs by data source (shapefile different than FGDB different than Oracle enterprise GDB different than SQL-Server EGDB, different than any of the dozens of other data sources), so your question should include the data source, so that syntax could be verified.

- V

Re: Using where clause to query date ranges in ArcPy

GeoffreyWest1 — Sun, 12 Dec 2021 04:11:23 GMT

My goal is to search for the date range of my features. If the dates fall within my specified time period then do something, else do something else. A search cursor worked brilliantly.

cursor = arcpy.SearchCursor(fc,"""Date BETWEEN '2014-02-16 16:53:25' AND  '2015-02-17 16:53:25'""" )
for row in cursor:
 print(row.getValue("Date"))




if row.getValue("Date") < datetime.datetime.now():
    print "true"

Re: Using where clause to query date ranges in ArcPy

XanderBakker — Mon, 06 Apr 2015 15:39:31 GMT

In addition to what Vince Angelo wrote, below an example of an expression for data in an Enterprise GDB (Oracle) and the same data in a File GDB:

EGDB (data resides in Oracle):

YourDateField BETWEEN timestamp '2014-02-16 16:53:25' AND timestamp '2014-02-17 18:53:25'

FGDB:

YourDateField BETWEEN date '2014-02-16 16:53:25' AND date '2014-02-17 18:53:25'

The whereclause parameter would become:

where_clause="YourDateField BETWEEN timestamp '2014-02-16 16:53:25' AND timestamp '2014-02-17 18:53:25'"

More on this in the help topic: "SQL reference for query expressions used in ArcGIS"

scroll down to Date-time syntax for ArcSDE geodatabases

Re: Using where clause to query date ranges in ArcPy

JamesCrandall — Mon, 06 Apr 2015 15:46:05 GMT

My goal is to search for the date range of my features

Doesn't the sql in your MakeFeatureLayer answer this already? I mean, you are asking to make a feature layer with the features that have Date attribute values between the 2 dates you specify.

Also, do you have a field named "Date"? If so, you might want to consider changing it to something that isn't a reserved word.

Re: Using where clause to query date ranges in ArcPy

GeoffreyWest1 — Mon, 06 Apr 2015 15:53:11 GMT

Yeah, but with a search cursor I am able to search the data with the specified range instead of creating a feature layer.

Re: Using where clause to query date ranges in ArcPy

JamesCrandall — Sun, 12 Dec 2021 04:11:26 GMT

I'm really lost as to what it is you want to do. Here, let's try something simple. Does this do what you want?

query = arcpy.MakeFeatureLayer_management(fc, "QueryLayer", """Date BETWEEN '2014-02-16 16:53:25' AND '2014-02-17 18:53:25'""")
_count = arcpy.GetCount_management(query)
if _count > 0:
    print "Feature Layer has: " + str(_count) + " rows."
else:
    print "Feature Layer has zero rows"

Re: Using where clause to query date ranges in ArcPy

XanderBakker — Sun, 12 Dec 2021 04:11:28 GMT

I guess your example isn't the clearest I can think of (since you query dates in a range of the past and apply a condition if that date is less than the current datetime...

To throw in some code to check if a date is in a range (processing all features) could be:

import arcpy
from datetime import datetime

fc = r"some reference to your fc"
frmt = '%Y-%m-%d %H:%M:%S'
fldDate = "Date"
start = datetime.strptime('2014-02-16 16:53:25', frmt)
end = datetime.strptime('2015-02-17 16:53:25', frmt)

cursor = arcpy.SearchCursor(fc)
for row in cursor:
    date = row.getValue(fldDate)
    if start <= date <= end:
        print "in between: {0}".format(date)
    else:
        print "NOT in between: {0}".format(date)
del cursor, row

# in case of ArcGIS 10.1 or higher
flds = (fldDate)
with arcpy.da.SearchCursor(fc, flds) as curs:
    for row in curs:
        date = row[0]
        if start <= date <= end:
            print "in between: {0}".format(date)
        else:
            print "NOT in between: {0}".format(date)
del curs, row