Running time intervals based off of an identifier

Esteban_G

Howdy! I'm trying to work in a view of a dataset that records actions taken on a work ticket. I'd like to add a column that shows the difference in time between two rows of data, depending on if they share the same ticket ID or not. I'm using the timestamp format for the datetime of the action and when I copy and paste it, it looks like this: 2024-11-18T05:43:34. Could someone assist me in figuring out the beast mode to add this is a column, or maybe suggest an approach to get this information another way?

Ticket Number	Action Time
303839	2024-11-18T05:43:34
303839	2024-11-18T05:43:32
303839	2024-11-18T05:39:52
303838	2024-11-18T04:49:11
303838	2024-11-18T04:49:10
303838	2024-11-18T04:49:10
303838	2024-11-18T04:49:10
303838	2024-11-18T04:48:51
303837	2024-11-18T04:47:56
303837	2024-11-18T04:47:56
303837	2024-11-18T04:47:56
303837	2024-11-18T04:47:53
303837	2024-11-18T04:47:36

GrantSmith

You can use a window function to do a lag

LAG(`Action Time`) OVER (PARTITION BY `Ticket Number` ORDER BY `Action Time`)

This can also be calculated using a Rank and Window tile in a Magic ETL

Then taking that you can calculate the time difference:

SEC_TO_TIME(UNIX_TIMESTAMP(`Action Time`) - UNIX_TIMESTAMP(`Prior Action Time`))

UNIX_TIMESTAMP will convert the timestamp to the number of seconds since 1970-01-01. Subtracting the two gives the number of seconds. SEC_TO_TIME converts the number of seconds to a human-readable format of hours, minutes, and seconds.

GrantSmith

You can use a window function to do a lag

LAG(`Action Time`) OVER (PARTITION BY `Ticket Number` ORDER BY `Action Time`)

This can also be calculated using a Rank and Window tile in a Magic ETL

Then taking that you can calculate the time difference:

SEC_TO_TIME(UNIX_TIMESTAMP(`Action Time`) - UNIX_TIMESTAMP(`Prior Action Time`))

UNIX_TIMESTAMP will convert the timestamp to the number of seconds since 1970-01-01. Subtracting the two gives the number of seconds. SEC_TO_TIME converts the number of seconds to a human-readable format of hours, minutes, and seconds.