Skip to main content
Inspiring
May 7, 2024 5:18 PM UTC
Solved

Timestamp subtraction ignores seconds

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.

    Inspiring
    May 7, 2024 5:46 PM UTC

    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
    Brainy
    May 7, 2024 5:29 PM UTC

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

    Inspiring
    May 7, 2024 5:37 PM UTC

    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?

    stevenh6272AuthorAnswer
    Inspiring
    May 7, 2024 5:46 PM UTC

    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.