topic Re: Use date timestamp field to select features in Python Questions

Use date timestamp field to select features

Anonymous User — Mon, 29 Apr 2019 19:00:29 GMT

Hello:

I want a user to select a date range which will select all features in that range based on the date field. Any advice?

python snippets‌

Re: Use date timestamp field to select features

DanPatterson_Retired — Mon, 29 Apr 2019 19:08:16 GMT

You saw this? because it depends on what you are querying as well

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

Re: Use date timestamp field to select features

Anonymous User — Mon, 29 Apr 2019 20:01:08 GMT

How can you use this in a python script?

Re: Use date timestamp field to select features

Anonymous User — Mon, 29 Apr 2019 20:07:27 GMT

Is there a reference guide for that?

Re: Use date timestamp field to select features

JoeBorgione — Tue, 30 Apr 2019 01:34:19 GMT

I think your'e going to have to try out a few expressions to see how it works. Looking at the reference Dan provides, there may be some fine tuning for your particular data base. In a general sense:

where YourDateField => someDate and YourDateField <= someOtherdate

The trick will be in how to format someDate and someOtherdate...

Re: Use date timestamp field to select features

Egge-Jan_Pollé — Tue, 30 Apr 2019 08:18:02 GMT

Hi Brandon Price‌,

Looking for a Python example? You may want to have a look at this video, just to start somewhere:

Writing Your Own Python DateTime Functions in the Arcmap Field Calculator

HTH,

Egge-Jan

Re: Use date timestamp field to select features

RandyBurton — Sat, 11 Dec 2021 14:11:29 GMT

'BETWEEN' will also work in date queries. For example with a file geodatabase:

where = "DateTime BETWEEN DATE '2019-04-01 00:00:00' AND DATE '2019-04-30 23:59:59'"

with arcpy.da.SearchCursor(feature, ['DateTime'], where_clause=where) as cursor:
    for row in cursor:
        print row‍‍‍‍‍‍‍‍‍‍

You will need to check the SQL documentation for the type of geodatabase you are working with. And the datetime module can also be very helpful.

from datetime import datetime, timedelta

date_past = datetime.now() - timedelta(days=180)

wc = "DateField < DATE '{}'".format(date_past.strftime("%Y-%m-%d %H:%M:%S"))

print wc‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Use date timestamp field to select features

Anonymous User — Tue, 30 Apr 2019 20:14:00 GMT

I created a python toolbox and I am trying to recreate the select by attributes query below within it in the Start Date and end parameters.

Toolbox (user view):

select by attributes query:

This is the unfinished code:

import arcpy, os,sys
geoName = sys.argv[1]
startDate = sys.argv[2]
endDate = sys.argv[3]

desiredDate =
query = startDate and e

I think maybe I need to convert the current date field (which is text) to a real date using the datetime module and then use the BETWEEN statement that Randy Burton suggested above to work for my class project.

Re: Use date timestamp field to select features

RandyBurton — Sat, 11 Dec 2021 14:11:31 GMT

I was doing some experimenting with a Python toolbox and came up with the following code:

import arcpy
import datetime


class Toolbox(object):
    def __init__(self):
        """Define the toolbox (the name of the toolbox is the name of the
        .pyt file)."""
        self.label = "Toolbox"
        self.alias = ""

        # List of tool classes associated with this toolbox
        self.tools = [Tool]


class Tool(object):
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "Tool"
        self.description = ""
        self.canRunInBackground = False

    def getParameterInfo(self):
        """Define parameter definitions"""

        feature = arcpy.Parameter(
            displayName = "Feature layer to search",
            name = "feature",
            datatype = "GPFeatureLayer",
            parameterType = "Required",
            direction = "Input")

        startDate = arcpy.Parameter(
            displayName = "Select start date",
            name = "startDate",
            datatype = "GPDate",
            parameterType = "Required",
            direction = "Input")

        endDate = arcpy.Parameter(
            displayName = "Select ending date",
            name = "endDate",
            datatype = "GPDate",
            parameterType = "Required",
            direction = "Input")

        params = None
        return [ feature, startDate, endDate ]

    def isLicensed(self):
        """Set whether tool is licensed to execute."""
        return True

    def updateParameters(self, parameters):
        """Modify the values and properties of parameters before internal
        validation is performed.  This method is called whenever a parameter
        has been changed."""
        return

    def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter.  This method is called after internal validation."""
        return

    def execute(self, parameters, messages):
        """The source code of the tool."""

        # the addMessages are for debugging and can be removed     
        messages.addMessage('Feature: {}'.format(parameters[0].valueAsText))
        messages.addMessage('Start: {}'.format(parameters[1].valueAsText))
        messages.addMessage('End: {}'.format(parameters[2].valueAsText))
        messages.addMessage('Type: {}'.format(type(parameters[2].value)))

        # one way to set the time portion
        # startDate = '{} 00:00:00'.format(parameters[1].value.strftime("%Y-%m-%d")) 
        # endDate = '{} 23:59:59'.format(parameters[2].value.strftime("%Y-%m-%d"))

        startDate = parameters[1].value.strftime("%Y-%m-%d %H:%M:%S")
        endDate = parameters[2].value.strftime("%Y-%m-%d %H:%M:%S")

        where = "DateTime BETWEEN DATE '{}' AND DATE '{}'".format(startDate, endDate)
        messages.addMessage('Where: {}'.format(where))

        arcpy.SelectLayerByAttribute_management(parameters[0].valueAsText, "NEW_SELECTION", where_clause=where)

        return‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

There are lots of addMessage() for debugging. At line 72, I used the type function to verify that the parameter being used was a datetime type. In addition I came across this topic: arcpy script tool - format parameter Date data type. It may provide some additional insight in how date/time works in a Python script or toolbox. Hope this helps.

Re: Use date timestamp field to select features

Anonymous User — Wed, 01 May 2019 15:43:34 GMT

Hello Randy:

This is great. Thanks for your effort. This is what I got:

I apologize. I am having trouble with the where clause and getting it to synchronize with my layer. The field that I am using is called UPDATEDATE. I tried a variety of ways to add my field to the where clause and was unsuccessful. My coworkers version is similar to yours although he is using this query variable instead of the where clause which works:

query = '"UPDATEDATE" < date \'{0}\' and "UPDATEDATE" > date \'{1}\''.format(endDT,startDT)

This is his full version:

import arcpy,datetime
#from datetime import datetime as dt

FC = arcpy.GetParameterAsText(0)
FC_1 = FC.replace("\\","/")
statrDate = arcpy.GetParameterAsText(1)
endDate = arcpy.GetParameterAsText(2)

startDT = datetime.datetime.strptime(statrDate,'%m/%d/%Y %I:%M:%S %p')
endDT = datetime.datetime.strptime(endDate,'%m/%d/%Y %I:%M:%S %p')

outFC = arcpy.GetParameterAsText(3)
outFC_1 = outFC.replace("\\","/")
query = '"UPDATEDATE" < date \'{0}\' and "UPDATEDATE" > date \'{1}\''.format(endDT,startDT)
arcpy.Select_analysis(FC_1,outFC_1,query)

The date variables look similar to yours (startDT and endDT)

Brandon

Re: Use date timestamp field to select features

RandyBurton — Sat, 11 Dec 2021 14:11:34 GMT

It looks like your friend is working with a custom script toolbox and not a Python toolbox. There's some differences in the coding and how they work. See Comparing custom and Python toolboxes.

I am assuming you want to use arcpy.Select_analysis in your tool. This tool requires 2 parameters with an optional where clause.

Select_analysis (in_features, out_feature_class, {where_clause})

In my sample script, there are three parameters: one for the in_features and two for use in the where clause. You will need to add another parameter which will be the out_feature_class. Replace lines 47-48 with something like:

        outFC = arcpy.Parameter(
            displayName = "Feature to be saved",
            name = "outFC",
            datatype = "DEFeatureClass",
            parameterType = "Required",
            direction = "Output")

        return [ feature, startDate, endDate, outFC ]‍‍‍‍‍‍‍‍

The execute block also will need changes. Replace lines 65 to the end with something like:

    def execute(self, parameters, messages):
        """The source code of the tool."""

        feature = parameters[0].valueAsText
        startDate = parameters[1].value.strftime("%Y-%m-%d %H:%M:%S")
        endDate = parameters[2].value.strftime("%Y-%m-%d %H:%M:%S")
        outFC = parameters[3].valueAsText
        
        where = "DateTime BETWEEN DATE '{}' AND DATE '{}'".format(startDate, endDate)
        # messages.addMessage('Where: {}'.format(where))

        arcpy.Select_analysis(feature, outFC, where)

        for i in range(arcpy.GetMessageCount()):
            messages.addMessage(arcpy.GetMessage(i))

        return
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You will need to watch the indentation as you edit your code. Lines 14-15 ( for i in range.... GetMessage ) will print the output of the Select_analysis tool, so you can see the parameters that are passed to it and any status messages it produces.

As Joe Borgione pointed out in his comment will be the formatting of the time/date. You may need to investigate the SQL reference for your geodatabase. I am assuming that it is a file geodatabase (.gdb). Field names can be put inside double quotes, but this appears to be optional. Date/time strings go inside single quotes, and the DATE in front of the string might also be optional. Again, this depends on your geodatabase.

As you finalize your project, you may want to add some code in the updateParameters section for validating your parameters.‌