Forum Discussion

CauseAndEffect's avatar
6 years ago
Solved

Counting the time between two date

Hey people!

 

We want to measure time time between two dates (two separate columns with the same format)

 

Format1 : YYYY-MM-DD HH:MM:SS

Format2:  YYYY-MM-DD HH:MM:SS

 

The needed output would be a column showing: HH:MM:SS with Date/time format, to be able to make calculations.

 

Tried the datefiff function but the return is only a singular whole number.

 
Time DIFF = 2019-09-21 10:16:39; 2019-09-21 10:21:22; MINUTE)
Output: 5
 

Wanted output: 00:04:43

I'm able to achieve this by using a function I found online: 

 
But this returns the time as text, and therefore I cannot make any calculations.
 
Thankful for any tips or insights!

 

 

 

 

3 Replies

  • Relised that the topic was moved, here is the function:

     

    Duration = 
    // Duration formatting 
    // * @konstatinos 1/25/2016
    // * Given a number of seconds, returns a format of "hh:mm:ss"
    //
    // We start with a duration in number of seconds
    VAR Duration = [Duration in Seconds]
    // There are 3,600 seconds in an hour
    VAR Hours =
        INT ( Duration / 3600)
    // There are 60 seconds in a minute
    VAR Minutes =
        INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
    // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
    VAR Seconds =
        ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
    // These intermediate variables ensure that we have leading zero's concatenated onto single digits
    // Hours with leading zeros
    VAR H =
        IF ( LEN ( Hours ) = 1, 
            CONCATENATE ( "0", Hours ),
            CONCATENATE ( "", Hours )
          )
    // Minutes with leading zeros
    VAR M =
        IF (
            LEN ( Minutes ) = 1,
            CONCATENATE ( "0", Minutes ),
            CONCATENATE ( "", Minutes )
        )
    // Seconds with leading zeros
    VAR S =
        IF (
            LEN ( Seconds ) = 1,
            CONCATENATE ( "0", Seconds ),
            CONCATENATE ( "", Seconds )
        )
    // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss"
    RETURN
        CONCATENATE (
            H,
            CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
        )