Problems finding Time at a location with field calculator

[VirginiaWalgren]

I am trying to figure out how to calculate the time that is spent at a particular location I have the arrival and departure times. When ever I try to use the field calculator to solve for the time spent anything over a few seconds it has the totally wrong time for.

For example the first column is the departure time and the second the arrival, but the elapsed time gets wonky after you go over a full minute.

Initially the TransTi column was written 74657 and not as time. It is likely that is where things are going wrong, but other than changing the number format on properties to custom I could not figure out a way to get the column to have the : between the numbers as they should.

Currently my field calculator just has [E_DTM_tim] - [TransTi]

Any help would be appreciated.

[FreddieGibson]

Everything appears to be working fine for me. Could you provide more information about how you're calculating the time difference and how you're storing the time values in the field (i.e. are you storing them as time objects or strings)?

Below is a screenshot of what I'm seeing with the times specified with in your screenshot. I've also included the script I used to test. This script can also create a table with the times shown in your screenshot.

import arcpy
import os
from datetime import datetime


m_fields = [("StartTime", "TEXT"), ("EndTime", "TEXT"), ("ExpectedDiff", "SHORT") , ("TimeDiffFC", "SHORT"), ("TimeDiffUC", "SHORT")]
m_time = [("7:46:35", "7:46:57", 22), ("8:06:10", "8:06:42", 32), ("8:22:54", "8:23:16", 22),
          ("8:24:51", "8:25:21", 30), ("8:33:22", "8:33:37", 15), ("9:07:56", "9:08:15", 19),
          ("9:10:47", "9:15:01", 254), ("9:21:39", "9:25:33", 234), ("9:29:51", "9:30:22", 31)]


def createdata(folder):
    outPath = os.path.join(folder, "data")
    if os.path.exists(outPath):
        arcpy.management.Delete(outPath)
    
    os.makedirs(outPath)
    
    gdb = arcpy.management.CreateFileGDB(outPath, "data.gdb")
    tab = arcpy.management.CreateTable(gdb, "TimeTable")
    
    for fname, ftype in m_fields:
        arcpy.management.AddField(tab, fname, ftype, field_length=8 if ftype == "TEXT" else "#")
        
    with arcpy.da.InsertCursor(tab, [f[0] for f in m_fields][:3]) as cursor:
        for time in m_time:
            cursor.insertRow(time)
        
    return tab


def calcTime(table):
    arcpy.management.CalculateField(table, "TimeDiffFC", "(datetime.datetime.strptime(!{0}!, \"{2}\") - datetime.datetime.strptime( !{1}!, \"{2}\")).seconds".format("EndTime", "StartTime", "%H:%M:%S"), "PYTHON")
    
    with arcpy.da.UpdateCursor(table, [f[0] for f in m_fields]) as cursor:
        for row in cursor:
            row[4] = (datetime.strptime(row[1], "%H:%M:%S") - datetime.strptime(row[0], "%H:%M:%S")).seconds
            cursor.updateRow(row)


if __name__ == '__main__':
    if '__file__' in dir():
        srcPath = os.path.dirname(__file__)
    else:
        from sys import argv
        srcPath = os.path.dirname(argv[0])
        
    table = createdata(srcPath)
    calcTime(table)

[VirginiaWalgren]

That doesn't help because the TransTi is a double and not inherently displayed as time, it only looks that way because I changed the numbers with the custom format.

That column is the part that messes the whole thing up. So how do I convert double into a time or date field?

Everything in that row is written like this originally.

[FreddieGibson]

I get what you're doing. You're experiencing a logic error and not a software/syntax error. It appears that you're just taking the time and putting together the numbers in a double field. Then you're just subtracting the two numbers and assuming this would give you the difference in time.

For example, from 9:15:01 to 9:10:47 is 4 minutes and 14 seconds (i.e. 254 seconds), but you're treating the times as the numeric values 91501 - 91047 = 454.

Both are correct answers, but if you're wanting the answer to represent time you cannot take your approach. If this is supposed to represent time you're missing the critical fact that one minute is equal to 60 seconds and not 100 seconds.

My code isn't designed to work with your numbers, but the logic is still valid for what you're trying to do. The only difference is that you'd need to parse your time values from the double values instead of using the strings I used. You would need to either cast the values to string and slice it to get the hours, minutes, and seconds for it. Otherwise if you didn't want to do this you'd need to rethink how you're calculating the field values. Instead of making them directly into double values you'd want to actually store the double values to represent the equivalent time of day in hours, minutes, or seconds. For example, 7:30 AM would be 7.5 hours [i.e. 7 + (30 / 60)] or 450 minutes [i.e. (7 * 60) + 30] or 27,000 seconds [i.e. 7.5 * 3600].

[VirginiaWalgren]

Sort of, the raw data I was given has one column as double and the other as Date. The date column I was able to manipulate to keep just the time. but the other column I need to get time out of, even though its not stored correctly. Is there a way to get the time out of that column into a time format?

Right now the math is trying to take 74657- 7:46:35

Which is why some numbers are right and others way not.

[FreddieGibson]

Why don't you just keep the datetime column and calculate the time from it? The approach you're using is only going to give you the correct number when your times are within the same minute. Otherwise it is simply not going to take into account the time is based on 60 and not 100.

[FreddieGibson]

Are you just storing the time values as a double For example 8:30:45 would be 83045? If no, can you explain how your storing the time in your double field?

[VirginiaWalgren]

Yes that is how they are stored.

[VirginiaWalgren]

Perhaps I was unclear in what I need to have done. I can figure out the elapsed time if and only if I can get the TranTi to be converted to some number that the field calculator will recognize as time. I have been told there is a way to do this, but I do not know and cannot find the code to change a time that has been written as 74525 into 7:45:25 and have ArcMap recognize that it is a time once that conversion has happened. 

So the question is how do I change 74525 into 7:45:25 am?

The original column is in double.

[TimWitt2 · Accepted solution]

Virginia,

maybe this can help you? 31877 - Convert a text field to a date field

Tim