AnsweredAssumed Answered

Calculate total time from first/last variables

Question asked by lancemwilson on Jun 4, 2015
Latest reply on Jun 4, 2015 by lancemwilson

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")