Calculate total time from first/last variables

[LanceWilson]

Ok, so I have this partially completed. What I need help with is how do I define my two time variables as the first and last values from the 'logdt' column of the output feature class that the user creates from my script tool? So I'm basically looking for a way to find and assign logdt.firstValue & logdt.lastValue to my created variables (logdt_firstValue, logdt_lastValue)

I'm hoping/assuming this is a relatively easy answer, but I'm not quite the python expert. Thanks in advance for any help!

(Also, is there a better way to populate the new field 'Total_Time' than by using the calculate field tool?)

# import arcpy & datetime modules
import arcpy
import datetime

# Ask user to select the Geodatabase workspace to use for data output
userWorkspace = arcpy.GetParameterAsText(0)

# Set workspace environment based upon user's choice
arcpy.env.workspace = userWorkspace

# Ask user to select an Oracle dB Connection
oracleDB = arcpy.GetParameterAsText(1)

# Ask user to name the Query Layer
queryLayerName = arcpy.GetParameterAsText(2)

# Ask user for an SQL Query Expression to be run against the selected Oracle dB
sqlQuery = arcpy.GetParameterAsText(3)

# Create spatial reference variable
spatialRef = arcpy.SpatialReference("W:\Data_Structure\Vector\Coordinate Systems\WGS 1984.prj")
# Process: 'Make Query Layer' - Creates a Query Layer using the user's Oracle dB and SQL query expression
# Parameters: (input_database, out_layer_name, query, {oid_fields},
# {shape_type}, {srid}, {spatial_reference})
arcpy.MakeQueryLayer_management(oracleDB, "Temp_Layer", sqlQuery, "UNIQUE_ID", "POLYLINE", "1050010", spatialRef)
# Process: 'Copy Features' - Copies the temporary Query Layer and stores it as a permanent feature class
# Parameters: (in_features, out_feature_class, {config_keyword},
# {spatial_grid_1}, {spatial_grid_2}, {spatial_grid_3})

arcpy.CopyFeatures_management("Temp_Layer", queryLayerName)
# Process: 'Add Field' - Add new field 'Total Time' for following expression
arcpy.AddField_management(queryLayerName, "Total_Time", "String")
# Process: 'Define Projection' - Defines the projection of queryLayerName feature class output
arcpy.DefineProjection_management(queryLayerName, spatialRef)
# -----------------------------------
# Below is using the datetime module in order to subtract the last and first date values from 'logdt'
# in order to get the total amount of time between the first and last GPS/AVL pings
# Create datetime format variable
dtFormat = '%m/%d/%Y %I:%M:%S %p'
# Create variable that equals the first 'logdt' value
logdt_firstValue =
# Create first 'logdt' value datetime format
logdt_firstDT = datetime.datetime.strptime(logdt_firstValue, dtFormat)
# Create variable that equals the last 'logdt' value
logdt_lastValue = 
# Create last 'logdt' value datetime format
logdt_lastDT = datetime.datetime.strptime(logdt_lastValue, dtFormat)
# Create variable that equals the subtraction of the last and first 'logdt' values
logdt_difference = logdt_lastValue - logdt_firstValue
# Process: 'Calculate Field' - Writes the logdt_difference into the new 'Total_Time' column
# Parameters: (in_table, field, expression, {expression_type}, {code_block})
expression == logdt_difference
arcpy.CalculateField_management(queryLayerName, "Total_Time", expression, "VB")

[DamonPettitt]

Maybe look into the arcpy.Statistics_analysis tool and use MIN for the earliest time and MAX for the latest time?

[LanceWilson]

Thanks for the help!

[DarrenWiens2 · Accepted solution]

In general, to read specific values from a table, you want to (must?) use a Search Cursor. This provides read access to your values. I'll add (although never seen this in reality) that the order of returned records is not guaranteed, so trusting a Search Cursor to return the first record first and the last record last is "risky". If your DBMS supports it, use ORDER BY.

Or, you can get first and last values using Summary Statistics, using both FIRST and LAST statistic types (or, more usefully, MIN and MAX). Then read that table with a Search Cursor.

[LanceWilson]

Great, thanks for the suggestions, I'll give them a try.