Calculating Time difference in Seconds Beast Mode

agolla2
agolla2 Member
in Beast Mode

Hello,

Is there any way to do the difference between 2 timestamps in seconds using a lead function in beast mode.

For example in below table take the first 2 rows 9:51:47 seconds subtracting with first row that is 9:51:14 seconds and output should be 33 seconds. The column is in String datatype.

Can I achieve this using beast mode or magic etl?

Wed

22 Mar 2023 09:51:14

Wed

22 Mar 2023 09:51:47

Wed

22 Mar 2023 09:59:55

Wed

22 Mar 2023 10:06:16

Tagged:

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can do this with a window function and unix_timestamp

    UNIX_TIMESTAMP(TIMESTAMP(`Timestamp`)) - UNIX_TIMESTAMP(LAG(TIMESTAMP(`Timestamp`)) OVER (ORDER BY TIMESTAMP(`Timestamp`)))

    TIMESTAMP converts your string to an actual timestamp

    UNIX_TIMESTAMP returns the number of seconds since 1/1/1970, taking the difference between the two values gives you the seconds between the timestamps.

    The LAG window function returns the prior row. If you're doing additional aggregation in your card you'll need to add another aggregation to your window function.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Sean_Tully
    Sean_Tully Contributor
    Answer ✓

    I got this to work with a few beast modes. First, I had to convert your string into a timestamp, using the str_to_date function. It looks like this: STR_TO_DATE(Timestamp, '%d %b %Y %T').

    Next, I used the lag function to get the the timestamps on the same row: lag(min(Timestamp), 1) over (order by Timestamp)

    Finally, I converted both timestamps to unix time to get the difference in seconds: (UNIX_TIMESTAMP(Converted Timestamp) - UNIX_TIMESTAMP(Lead Timestamp))

    You could probably do all of this in one beast mode, but I like to do it one step at a time so I can undestand what I'm doing.

    image.png

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can do this with a window function and unix_timestamp

    UNIX_TIMESTAMP(TIMESTAMP(`Timestamp`)) - UNIX_TIMESTAMP(LAG(TIMESTAMP(`Timestamp`)) OVER (ORDER BY TIMESTAMP(`Timestamp`)))

    TIMESTAMP converts your string to an actual timestamp

    UNIX_TIMESTAMP returns the number of seconds since 1/1/1970, taking the difference between the two values gives you the seconds between the timestamps.

    The LAG window function returns the prior row. If you're doing additional aggregation in your card you'll need to add another aggregation to your window function.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Sean_Tully
    Sean_Tully Contributor
    Answer ✓

    I got this to work with a few beast modes. First, I had to convert your string into a timestamp, using the str_to_date function. It looks like this: STR_TO_DATE(Timestamp, '%d %b %Y %T').

    Next, I used the lag function to get the the timestamps on the same row: lag(min(Timestamp), 1) over (order by Timestamp)

    Finally, I converted both timestamps to unix time to get the difference in seconds: (UNIX_TIMESTAMP(Converted Timestamp) - UNIX_TIMESTAMP(Lead Timestamp))

    You could probably do all of this in one beast mode, but I like to do it one step at a time so I can undestand what I'm doing.

    image.png

  • agolla2
    agolla2 Member

    Thank you all @GrantSmith and @Sean_Tully for the answers. I will try these out and will let you know.

  • agolla2
    agolla2 Member

    Thanks, the beast modes are now working.

Categories