Skip to main content
S
stevenh6272
May 7, 2024
Solved

Timestamp subtraction ignores seconds

  • May 7, 2024
  • 7 replies
  • 0 views

I am trying to calculate duration given two timestamps.  When I subtract the two, it appears the subtraction is happening with minute-level granularity (i.e. the seconds are being ignored).  Is this the correct subtraction functionality?

Here's a comparison I have done trying to subtract various time intervals...

a!localVariables(
  local!epsilon: 0.0000115740740740741, /* one second */
  local!times: { 
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:00:00 AM", expected: 0 },
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:00:01 AM", expected: local!epsilon },
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:00:10 AM", expected: local!epsilon * 10 },
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:00:30 AM", expected: local!epsilon * 30 },
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:00:50 AM", expected: local!epsilon * 50 },
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:00:59 AM", expected: local!epsilon * 59 },
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:00:59.999 AM", expected: local!epsilon * 59.999 },
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:01:00 AM", expected: local!epsilon * 60 * 1 },
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:01:30 AM", expected: local!epsilon * 60 * 1.5 },
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:02:00 AM", expected: local!epsilon * 60 * 2 },
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:04:00 AM", expected: local!epsilon * 60 * 4 },
    { start: "1/1/2000 8:00:00 AM", end: "1/1/2000 8:08:00 AM", expected: local!epsilon * 60 * 8 }
  },
  a!forEach(
    items: local!times,
    expression: a!localVariables(
      local!actual: toDateTime(fv!item.end) - toDateTime(fv!item.start),
      local!actualNumeric: todecimal(local!actual),
      {
        expected: tointervalds(fv!item.expected),
        actual: local!actual,
        nearlySame: abs(local!actualNumeric - todecimal(fv!item.expected)) < local!epsilon
      }
    )
  )
)

All of these should result in nearlySame being true.  But only the ones that are quantized to the minute boundary end up being true.

    Best answer by stevenh6272

    The subtraction works when it has the seconds... 

    The problem is the seconds are not considered when the string is converted to a DateTime.  It is not a problem with toDateTime.... This looks more like a parser error.... when Appian interprets the string to convert it to the original dateTime() command.

    7 replies

    stefanhelzle0001
    stefanhelzle0001
    Brainy
    May 7, 2024

    This feels more like your are running into some rounding, numeric precision and number conversation issues somewhere.

    S
    stevenh6272Author
    May 7, 2024

    Appian seems not to like working with seconds in the first place (i.e. it will not display seconds by default).  It appears that toDateTime is ignoring the seconds.

          {
        start: toDateTime(fv!item.start),
        end: toDateTime(fv!item.end),
        expected: tointervalds(fv!item.expected),
        actual: local!actual,
        nearlySame: abs(local!actualNumeric - todecimal(fv!item.expected)) < local!epsilon
      }

    For what should be a 30 second interval, the output is this

    looking at it with Expression view....
    {start: fn!todatetime(fn!datetime(2000, 1, 1, 8, 0, 0, 0)), end: fn!todatetime(fn!datetime(2000, 1, 1, 8, 0, 0, 0)), expected: fn!internalize("0000000100000028000000000000000300000002000000003F36C16C16C16C25000000010000001D0000000100000002"), actual: fn!internalize("0000000100000028000000000000000300000002000000000000000000000000000000010000001D0000000100000002"), nearlySame: false},

    You can see in the end timestamp the seconds are already truncated.  This value comes from toDateTime("1/1/2000 8:00:30 AM").

    So is toDateTIme broken?  Should it be ignoring seconds?

    S
    stevenh6272AuthorAnswer
    May 7, 2024

    The subtraction works when it has the seconds... 

    The problem is the seconds are not considered when the string is converted to a DateTime.  It is not a problem with toDateTime.... This looks more like a parser error.... when Appian interprets the string to convert it to the original dateTime() command.

      Terms & ConditionsAccessibility statement