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
I was doing some experimenting with a Python toolbox and came up with the following code:
<SPAN class="keyword token">import</SPAN> arcpy <SPAN class="keyword token">import</SPAN> datetime <SPAN class="keyword token">class</SPAN> <SPAN class="token class-name">Toolbox</SPAN><SPAN class="punctuation token">(</SPAN>object<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">def</SPAN> <SPAN class="token function">__init__</SPAN><SPAN class="punctuation token">(</SPAN>self<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="string token">"""Define the toolbox (the name of the toolbox is the name of the .pyt file)."""</SPAN> self<SPAN class="punctuation token">.</SPAN>label <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Toolbox"</SPAN> self<SPAN class="punctuation token">.</SPAN>alias <SPAN class="operator token">=</SPAN> <SPAN class="string token">""</SPAN> <SPAN class="comment token"># List of tool classes associated with this toolbox</SPAN> self<SPAN class="punctuation token">.</SPAN>tools <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>Tool<SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">class</SPAN> <SPAN class="token class-name">Tool</SPAN><SPAN class="punctuation token">(</SPAN>object<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="keyword token">def</SPAN> <SPAN class="token function">__init__</SPAN><SPAN class="punctuation token">(</SPAN>self<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="string token">"""Define the tool (tool name is the name of the class)."""</SPAN> self<SPAN class="punctuation token">.</SPAN>label <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Tool"</SPAN> self<SPAN class="punctuation token">.</SPAN>description <SPAN class="operator token">=</SPAN> <SPAN class="string token">""</SPAN> self<SPAN class="punctuation token">.</SPAN>canRunInBackground <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">False</SPAN> <SPAN class="keyword token">def</SPAN> <SPAN class="token function">getParameterInfo</SPAN><SPAN class="punctuation token">(</SPAN>self<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="string token">"""Define parameter definitions"""</SPAN> feature <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>Parameter<SPAN class="punctuation token">(</SPAN> displayName <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Feature layer to search"</SPAN><SPAN class="punctuation token">,</SPAN> name <SPAN class="operator token">=</SPAN> <SPAN class="string token">"feature"</SPAN><SPAN class="punctuation token">,</SPAN> datatype <SPAN class="operator token">=</SPAN> <SPAN class="string token">"GPFeatureLayer"</SPAN><SPAN class="punctuation token">,</SPAN> parameterType <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Required"</SPAN><SPAN class="punctuation token">,</SPAN> direction <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Input"</SPAN><SPAN class="punctuation token">)</SPAN> startDate <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>Parameter<SPAN class="punctuation token">(</SPAN> displayName <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Select start date"</SPAN><SPAN class="punctuation token">,</SPAN> name <SPAN class="operator token">=</SPAN> <SPAN class="string token">"startDate"</SPAN><SPAN class="punctuation token">,</SPAN> datatype <SPAN class="operator token">=</SPAN> <SPAN class="string token">"GPDate"</SPAN><SPAN class="punctuation token">,</SPAN> parameterType <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Required"</SPAN><SPAN class="punctuation token">,</SPAN> direction <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Input"</SPAN><SPAN class="punctuation token">)</SPAN> endDate <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>Parameter<SPAN class="punctuation token">(</SPAN> displayName <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Select ending date"</SPAN><SPAN class="punctuation token">,</SPAN> name <SPAN class="operator token">=</SPAN> <SPAN class="string token">"endDate"</SPAN><SPAN class="punctuation token">,</SPAN> datatype <SPAN class="operator token">=</SPAN> <SPAN class="string token">"GPDate"</SPAN><SPAN class="punctuation token">,</SPAN> parameterType <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Required"</SPAN><SPAN class="punctuation token">,</SPAN> direction <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Input"</SPAN><SPAN class="punctuation token">)</SPAN> params <SPAN class="operator token">=</SPAN> None <SPAN class="keyword token">return</SPAN> <SPAN class="punctuation token">[</SPAN> feature<SPAN class="punctuation token">,</SPAN> startDate<SPAN class="punctuation token">,</SPAN> endDate <SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">def</SPAN> <SPAN class="token function">isLicensed</SPAN><SPAN class="punctuation token">(</SPAN>self<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="string token">"""Set whether tool is licensed to execute."""</SPAN> <SPAN class="keyword token">return</SPAN> <SPAN class="token boolean">True</SPAN> <SPAN class="keyword token">def</SPAN> <SPAN class="token function">updateParameters</SPAN><SPAN class="punctuation token">(</SPAN>self<SPAN class="punctuation token">,</SPAN> parameters<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="string token">"""Modify the values and properties of parameters before internal validation is performed. This method is called whenever a parameter has been changed."""</SPAN> <SPAN class="keyword token">return</SPAN> <SPAN class="keyword token">def</SPAN> <SPAN class="token function">updateMessages</SPAN><SPAN class="punctuation token">(</SPAN>self<SPAN class="punctuation token">,</SPAN> parameters<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="string token">"""Modify the messages created by internal validation for each tool parameter. This method is called after internal validation."""</SPAN> <SPAN class="keyword token">return</SPAN> <SPAN class="keyword token">def</SPAN> <SPAN class="token function">execute</SPAN><SPAN class="punctuation token">(</SPAN>self<SPAN class="punctuation token">,</SPAN> parameters<SPAN class="punctuation token">,</SPAN> messages<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="string token">"""The source code of the tool."""</SPAN> <SPAN class="comment token"># the addMessages are for debugging and can be removed </SPAN> messages<SPAN class="punctuation token">.</SPAN>addMessage<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Feature: {}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>parameters<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>valueAsText<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> messages<SPAN class="punctuation token">.</SPAN>addMessage<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Start: {}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>parameters<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>valueAsText<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> messages<SPAN class="punctuation token">.</SPAN>addMessage<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'End: {}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>parameters<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>valueAsText<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> messages<SPAN class="punctuation token">.</SPAN>addMessage<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Type: {}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>type<SPAN class="punctuation token">(</SPAN>parameters<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>value<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># one way to set the time portion</SPAN> <SPAN class="comment token"># startDate = '{} 00:00:00'.format(parameters[1].value.strftime("%Y-%m-%d")) </SPAN> <SPAN class="comment token"># endDate = '{} 23:59:59'.format(parameters[2].value.strftime("%Y-%m-%d"))</SPAN> startDate <SPAN class="operator token">=</SPAN> parameters<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>value<SPAN class="punctuation token">.</SPAN>strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%Y-%m-%d %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN> endDate <SPAN class="operator token">=</SPAN> parameters<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>value<SPAN class="punctuation token">.</SPAN>strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%Y-%m-%d %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN> where <SPAN class="operator token">=</SPAN> <SPAN class="string token">"DateTime BETWEEN DATE '{}' AND DATE '{}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>startDate<SPAN class="punctuation token">,</SPAN> endDate<SPAN class="punctuation token">)</SPAN> messages<SPAN class="punctuation token">.</SPAN>addMessage<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Where: {}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>where<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute_management<SPAN class="punctuation token">(</SPAN>parameters<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>valueAsText<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"NEW_SELECTION"</SPAN><SPAN class="punctuation token">,</SPAN> where_clause<SPAN class="operator token">=</SPAN>where<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">return</SPAN><SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
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.
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 ]<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
The execute block also will need changes. Replace lines 65 to the end with something like:
<SPAN class="keyword token">def</SPAN> <SPAN class="token function">execute</SPAN><SPAN class="punctuation token">(</SPAN>self<SPAN class="punctuation token">,</SPAN> parameters<SPAN class="punctuation token">,</SPAN> messages<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> <SPAN class="string token">"""The source code of the tool."""</SPAN> feature <SPAN class="operator token">=</SPAN> parameters<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>valueAsText startDate <SPAN class="operator token">=</SPAN> parameters<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>value<SPAN class="punctuation token">.</SPAN>strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%Y-%m-%d %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN> endDate <SPAN class="operator token">=</SPAN> parameters<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>value<SPAN class="punctuation token">.</SPAN>strftime<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"%Y-%m-%d %H:%M:%S"</SPAN><SPAN class="punctuation token">)</SPAN> outFC <SPAN class="operator token">=</SPAN> parameters<SPAN class="punctuation token">[</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>valueAsText where <SPAN class="operator token">=</SPAN> <SPAN class="string token">"DateTime BETWEEN DATE '{}' AND DATE '{}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>startDate<SPAN class="punctuation token">,</SPAN> endDate<SPAN class="punctuation token">)</SPAN> <SPAN class="comment token"># messages.addMessage('Where: {}'.format(where))</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>Select_analysis<SPAN class="punctuation token">(</SPAN>feature<SPAN class="punctuation token">,</SPAN> outFC<SPAN class="punctuation token">,</SPAN> where<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> i <SPAN class="keyword token">in</SPAN> range<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>GetMessageCount<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN> messages<SPAN class="punctuation token">.</SPAN>addMessage<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>GetMessage<SPAN class="punctuation token">(</SPAN>i<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">return</SPAN> <SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
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.
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
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,sysgeoName = 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.
'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<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
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<SPAN class="line-numbers-rows"><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN><SPAN></SPAN></SPAN>
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
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...
Is there a reference guide for that?
How can you use this in a python script?
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
Signed in members can post, follow updates, and more. New here? Register a free account.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
