Calculating Time difference in Seconds Beast Mode

agolla2

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

GrantSmith

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.

Sean_Tully

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.

GrantSmith

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.

Sean_Tully

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.

agolla2

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

agolla2

Thanks, the beast modes are now working.