Difference in minutes and seconds between two date

Maxime_F67
Maxime_F67 Registered Posts: 2 ✭✭✭✭
Hi everyone,

How can I get the difference in "hours" "minutes" AND "seconds" (example: 14:30:24) from two date?



Example:

Col 1: 2018-01-01T09:50:15.000Z

Col 2: 2018-01-01T10:07:55.000Z

Col 3 (difference): 00:17:40

Thank so much for your help

Maxime

Answers

  • Paulinesco
    Paulinesco Dataiker Alumni, Registered Posts: 4 ✭✭✭✭
    Hello Maxime,

    You can for example use a Prepare recipe to compute the time difference between two columns (these dates have to be parsed). For this, you can use the processor "Compute difference between two dates" in the Prepare recipe and select the two columns.

    You can define the granularity of the output right in the new step (from years to seconds).



    I hope this helps!

    Have a nice day.

    Pauline
  • Maxime_F67
    Maxime_F67 Registered Posts: 2 ✭✭✭✭
    Hello,
    Thank you for your answer but with the processor "Compute difference between two dates" the output time unit is hours or minutes or seconds but cannot be hours:minutes:seconds.
  • Paulinesco
    Paulinesco Dataiker Alumni, Registered Posts: 4 ✭✭✭✭
    Indeed! Thanks for replying. I tried this on my side and it seems to be working stillusing the formula in a Prepare recipe.
    The idea is to rebuild the date with hours:minutes:secondes, from the time difference in seconds.

    "00"[0,2-(floor(diff(date1, date2, "seconds")/3600)).length()] + (floor(diff(date1, date2, "seconds")/3600))
    + ':'
    + "00"[0,2- (floor(mod(diff(date1, date2, "seconds"), 3600)/60)).length()] + (floor(mod(diff(date1, date2, "seconds"), 3600)/60))
    + ':'
    + "00"[0,2- (mod(mod(diff(date1, date2, "seconds"), 3600),60)).length()] + (mod(mod(diff(date1, date2, "seconds"), 3600),60))

    You can obviously use a Python recipe instead of the formula to rebuild this field too.

    I hope this helps.
    Have a nice day,
    Pauline
  • ThierryPrevost
    ThierryPrevost Registered Posts: 1 ✭✭✭

    I adapted the code and it works fine:
    concat(get("00", 0, 2 - length(floor(diff(ended_at, started_at, "Seconds") / 3600))), concat(floor(diff(ended_at, started_at, "Seconds") / 3600)),":",get("00", 0, 2 - length(floor(mod(diff(ended_at, started_at, "Seconds"), 3600)/60))), concat(floor(mod(diff(ended_at, started_at, "Seconds"), 3600)/60)),":",get("00", 0, 2 - length(mod(mod(diff(ended_at, started_at, "Seconds"), 3600),60))), concat(mod(mod(diff(ended_at, started_at, "Seconds"), 3600),60)))

Setup Info
    Tags
      Help me…