Python / SQL / Model Builder help!

[StephenSmith8847]

Hi all,

I'm hoping someone can help me. I'm trying to follow this guide:

Previously had help from some community members and managed to get the whole flow running, however the space time cube wasn't changing dates when the parameter date changed, so i've now tried to upgrade the model and i think i have it all correct.

 This is what the model now looks like. Report Date is set and can be changed (once the tool is complete) by the inputter. The calculate value then creates the start date output (which then feeds into the select layer by attribute) we then recently added another calculate value to confirm the end date range. 

Starting Report Date Python expression: datetime.datetime.strptime('%ReportDate%', '%d/%m/%Y') - datetime.timedelta(days = 365)  (Data type is set to Date)

End Report Date Python expression: datetime.datetime.strptime('%ReportDate%', '%d/%m/%Y') (Data type set to Date)

So if the Parameter ReportDate is set to 01/06/2021, the output of the Starting Report Date is 01/06/2020 and the output for the End Report Date is 01/06/2021 which is perfect and is what we want to happen as that gives us a year period, which will change dynamically based on the ReportDate.

This then feeds into the Select Laybe by attribute, the SQL expression is: 

(Date >= timestamp '%Starting Report Date%') AND (Date <= timestamp '%End Report Date%')

E.g from the input layer, please create a new layer that has the dates greater than or equal to the start date or less than or equal to end date. (I have had to select remove from current selection otherwise it has another error. I would ideally like to choose select from new layer but that throws up a bigger fault.

The error with all the above set, is as follows:

Traceback (most recent call last):
  File "<string>", line 1766, in execute
  File "c:\program files\arcgis\pro\Resources\ArcToolbox\Scripts\SSCubeObject.py", line 295, in obtainData
    self.__obtainData()
  File "c:\program files\arcgis\pro\Resources\ArcToolbox\Scripts\SSCubeObject.py", line 374, in __obtainData
    self.__parseTimeInfo()
  File "c:\program files\arcgis\pro\Resources\ArcToolbox\Scripts\SSCubeObject.py", line 495, in __parseTimeInfo
    self.timeAlignment, timeBreaks = TUTILS.createTimeBreaks(timeData, breakTimeSize, 
  File "c:\program files\arcgis\pro\Resources\ArcToolbox\Scripts\SSTimeUtilities.py", line 379, in createTimeBreaks
    if refTime <= dataStartTime:
TypeError: '<=' not supported between instances of 'str' and 'datetime.datetime'

Failed to execute (Create Space Time Cube By Aggregating Points).

[dgiersz_cuyahoga]

I don't think you're supposed to put the quotes around the %variable name%

[StephenSmith8847]

Hi,

It seems to recognise it though with those in:

Executing (Select Layer By Attribute): SelectLayerByAttribute FullSVDataSetCrimesMAS_Merge "Remove from the current selection" ""Created Date" >= timestamp '01/06/2020' AND "Created Date" <= timestamp '01/06/2021'" 1

 So not sure if that's the problem.

[Kara_Shindle]

I'm wondering what your "Date" data type is in your expression.  When I look up that error, it's basically saying that "Date" is a string, and that your StartingReportDate & EndReportDates are datetime objects.  Your comparison operators are not going to work between different data types, maye try converting your "Date" field to a datatime object.

[StephenSmith8847]

How would I convert the "Date" field to a datatime object as I am happy to test this as I think you're correct and would this effect any of my current Python/SQL Expressions?

[StephenSmith8847]

In my data layer, the Created Date is set to DD/MM/YY with no time, does this matter?

[Kara_Shindle]

I would make sure that the formats for all your date fields are consistent, just to avoid any potential issues.

Is your Created Date field a string or a datetime object?

Can you show us where this expression is?  Is it in a script or a tool?

[StephenSmith8847]

The Created Date in my raw csv data file hasn't been formatted in any particular way, except to state in ArcGIS that it is a date.

I have two expressions, one for each output from the ReportDate variable that feeds into the calculate field, one for the start time:

datetime.datetime.strptime('%ReportDate%', '%d/%m/%Y') - datetime.timedelta(days = 365)

and one for the end time:

datetime.datetime.strptime('%ReportDate%', '%d/%m/%Y')

I was under the impression that the strptime changed it from a string to a datetime object using the %d/%m/%Y

[Kara_Shindle]

Is the Created Date a field or is it supposed to be the ReportDate?  Did you use that csv to create a shapefile or feature class?  If so, what does the field view for that shapefile/feature class look like?

I guess I'm having trouble trying to figure out what your Created Date is and where it fits into this workflow.

[StephenSmith8847]

Hi Kara,

Thanks again for your help, maybe if we can breakdown my SQL expression first to see if that's correct.

"Created_Date" is the name of the field within my feature class

%Starting Report Date% is the name of one of my outputs from the variable in the first Calculate field tool in the model.

%End Report Date% is the second output from the same first variable which is called ReportDate

"Created_Date" >= timestamp '%Starting Report Date%' AND "Created_Date" <= timestamp '%End Report Date%'

I feel it is this SQL which isn't passing on the correct dates to the space time cube input.