Storing ELAPSED Time in Attribute Table?

327
4
Jump to solution
03-14-2024 04:05 PM
Labels (1)
VinceE
by
Occasional Contributor II

I would like to store elapsed time as an attribute in a feature class, displayed as HH:MM (drive time, for example). I have tried several approaches, and none of them are great.

  1. "Text" Data Type - User inputs elapsed time as HH:MM, and an Attribute Rule checks that the input fits this format. Works fine, but won't sort in ascending/descending order obviously, and can't easily add/subtract time from the data. Visually good, but the "data" doesn't really mean anything.
  2. "Time Only" Data Type - This isn't really appropriate either, as my "time" data isn't wall time, but elapsed time. This field type doesn't allow me to store 0:30, for example. Also, stored time can't be larger than 23:59.
  3. "Short" Data Type - I have this set to display as ##:##, so if a user inputs 337, this shows as 3:37. This field type allows sorting, which is ideal. But, the underlying data is now pretty meaningless once it's separated from it's nice HH:MM display.

So I'm hoping to come up with a way of entering elapsed time as "HH:MM" in a field that also stores the underlying data in a meaningful way (quantifiable, sortable, etc.). I would be happy to use Attribute Rules to assist with this.

0 Kudos
1 Solution

Accepted Solutions
SeaRM
by
New Contributor III

I understand you. So, I can suggest you adding two fields: Double or Integer field for storing values in seconds or minutes; and another Text field where with Calculate Field functionality or something else you can save your values in a readable way. Then, you may use numeric values for sorting and calculating, and Text values for reading and understanding.

But I understand that it's not an easy way, too. It may take some time and some efforts to do. It's not a perfect solution, but it might work.

View solution in original post

4 Replies
SeaRM
by
New Contributor III

What about storing just as values in seconds in a Double field type?

0 Kudos
VinceE
by
Occasional Contributor II

For "data" reasons, a great solution--at the resolution I require, even minutes would work. My issue is that if a user is transcribing a record from elsewhere, say "7:37", they have to do the math to convert those hours into minutes (or hours and minutes into seconds, if seconds are my unit). Not a huge deal, but an extra step, and possibly error-prone.

The other thing is quickly reading the table. I want to be able to read these records at a glance. Number of seconds (beyond a minute or two) means virtually nothing to me, and even minutes can get difficult: 407 minutes takes me a little while to process. So I'd really want that to show as HH:MM. And I don't know of a table "display" setting that would allow the math required to calculate that from seconds/minutes alone, and an Attribute Rule would... change the actual underlying data value? Plus, an Attribute Rule returning a value with the colon would be problematic with a DOUBLE field type.

Storing as minutes is probably going to be the only solution here, but it's annoying that it can't be displayed in an easily readable way. My best bet might be to use an Attribute Rule to calculate the minutes into a SHORT/DOUBLE field from a TEXT field. So the user can type in 5:49 or whatever in the TEXT field, and then an Attribute Rule calculates the raw number of minutes into a different numeric field, and then valid sorting can take place on the MINUTES field.

Appreciate the comment, thanks!

0 Kudos
SeaRM
by
New Contributor III

I understand you. So, I can suggest you adding two fields: Double or Integer field for storing values in seconds or minutes; and another Text field where with Calculate Field functionality or something else you can save your values in a readable way. Then, you may use numeric values for sorting and calculating, and Text values for reading and understanding.

But I understand that it's not an easy way, too. It may take some time and some efforts to do. It's not a perfect solution, but it might work.

VinceE
by
Occasional Contributor II

Appreciate the input, thanks! This option will have to do for now. Here's what my implementation looks like:

HHMM is a TEXT field that the user types in, HH is a FLOAT field that gets calculated. Figured reading HH would be more intuitive to understand than MM, but the concept is the same.

VinceE_0-1710477379729.png

I try to use the same "template" for Attribute Rules, so this is probably overly verbose, but here's the automatic calculation from HH:MM text to HH float:

 

/*
Converts a text HH:MM format field and converts it to a float HH field.
*/
//---------------------------------------------------------------------//
// FUNCTIONS
function calculateHH(hhmm) {
    // Set up results dict. If all checks pass, return this as-is.
    var result = Dictionary("VALUE", null, "ERROR", null)

    if (IsEmpty(hhmm)) {
        return result
    }
    Console(hhmm)
    var timeArr = Split(hhmm, ":")
    var hh = Number(timeArr[0]) + (Number(timeArr[1]) / 60)

    Console(`${hhmm} --> ${hh}`)
    result["VALUE"] = Round(hh, 2)
    return result
}
//---------------------------------------------------------------------//
// INPUTS
var hhmm = $feature.Hiking_HHMM
//---------------------------------------------------------------------//
// MAIN
var fldUpdates = Dictionary()

// Convert HHMM text to MM float ----------------
var result = calculateHH(hhmm)
if (!IsEmpty(result["ERROR"]))
    {return {"errorMessage": `ERROR: ${result}`}}
else
    {fldUpdates["Hiking_HH"] = result["VALUE"]}

Console({"result": {"attributes": fldUpdates}})
return {"result": {"attributes": fldUpdates}}

 

 

And my solution to verifying the initial HH:MM inputs:

 

/*
Script provides validation for a TEXT field that should contain a time duration
  in the format H?H?:MM. There are several checks, since RegEx is not available.
1) Blank strings are not allowed; null values are fine
2) Format must contain a colon--as in 3:30, 12:04, :37
3) Values on either side of the colon must be numeric
4) Hours are optional, but must be 0 - 23
5) Minutes must always be two characters, and numeric values 0 - 59

This script is meant to be used as Calculation type AR, rather than a
Constaint AR.
*/
//---------------------------------------------------------------------//
// FUNCTIONS
function timeValidate (time) {
    // Set up results dict. If all checks pass, return this as-is.
    var result = Dictionary("VALUE", time, "ERROR", null)
    
    // First check empty, then null. Can't do both simultaneously nicely.
    if ((TypeOf(time) == "String") && Trim(time) == "") {
        result["ERROR"] = "Blank input is not allowed."
        return result
    }
    else if (IsEmpty(time)) {
        return result
    }

    // Console(timeArray, Count(timeArray));
    // Must contain one ":", yields two elements with Split().
    if (Count(timeArray) != 2) {
        result["ERROR"] = "Format must contain one colon."
        return result
    }

    // Check if both values in the array are numbers.
    for (var i in timeArray) {
        var tPart = timeArray[i]
        Console(`PART: ${i}, VAL: ${tPart}, ASNUM: ${Number(tPart)}, ISNAN: ${IsNan(Number(tPart))}`)

        // If Number returns NaN, "Casting a non-numeric text or undefined to a number".
        if (IsNan(Number(tPart))) {
            result["ERROR"] = "At least one value is not numeric."
            return result
        }

        // Hours validation, the first array item.
        if (i == 0) {
            Console(`VALIDATING HOURS: ${tPart}`)
            // Comparison operators coerce Text to Number for comparison.
            if (tPart > 23 || tPart < 0) {
                result["ERROR"] = "Hours must be 0-23."
                return result
            }
        }
        // Minutes validation, the second array item.
        else if (i == 1) {
            Console(`VALIDATING MINUTES: ${tPart}`)
            if (Count(tPart) != 2) {
                result["ERROR"] = "Minutes must be two digits."
                return result
            }
            else if (tPart > 59 || tPart < 0) {
                result["ERROR"] = "Minutes must be 0-59."
                return result
            }
        }
    }
    return result
}

//---------------------------------------------------------------------//
// INPUTS
// Expects($feature, "Shape_Length", "MPH", "Miles")
var time = $feature.Hiking_HHMM

// DERIVED
var timeArray = Split(time, ":")
Console(`TIME ARR: ${timeArray}`)

//---------------------------------------------------------------------//
// MAIN
var fldUpdates = Dictionary()

// result = {"VALUE": time, "ERROR": error string OR null}
var result = timeValidate(time)

// If an error is returned in results dict, show and make no changes.
if (!IsEmpty(result["ERROR"]))
    {return {"errorMessage": `ERROR: ${result} Please use H?H?:MM format.`}}
// Else, add the field update information to the running updates log.
else
    {fldUpdates["Hiking_HHMM"] = time}

Console({"result": {"attributes": fldUpdates}})
return {"result": {"attributes": fldUpdates}}