The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Vertica Query to find and subtract skips in time

I have a table showing username, start and end time throughout the day. I need a query that will find the skip in time that may happen between the end_time and start_time in order. I've attached an example of what I'm looking for.
Tagged:
0
Best Answer
-
mosheg Vertica Employee Administrator
set datestyle to DMY; select *, datediff('second', start_time_utc, lag(end_time_utc) over (order by start_time_utc) ) as skip_time_s from skip_time order by user_name,row; user_name | start_date | start_time_utc | end_time_utc | row | skip_time_s -----------+------------+---------------------+---------------------+-----+------------- TTFIN | 2021-12-26 | 2021-12-21 00:00:00 | 2021-12-21 00:00:00 | 1 | TTFIN | 2021-12-26 | 2021-12-21 00:00:00 | 2021-12-21 08:01:00 | 2 | 0 TTFIN | 2021-12-26 | 2021-12-21 08:01:00 | 2021-12-21 08:01:00 | 3 | 0 TTFIN | 2021-12-26 | 2021-12-21 08:12:00 | 2021-12-21 08:12:00 | 4 | -660 TTFIN | 2021-12-26 | 2021-12-21 08:12:00 | 2021-12-21 08:17:00 | 5 | 0 TTFIN | 2021-12-26 | 2021-12-21 08:34:00 | 2021-12-21 08:34:00 | 6 | 0 TTFIN | 2021-12-26 | 2021-12-21 08:17:00 | 2021-12-21 08:34:00 | 7 | 0 TTFIN | 2021-12-26 | 2021-12-21 08:36:00 | 2021-12-21 08:36:00 | 8 | -120 TTFIN | 2021-12-26 | 2021-12-21 08:36:00 | 2021-12-21 08:37:00 | 9 | 0 TTFIN | 2021-12-26 | 2021-12-21 08:37:00 | 2021-12-21 08:41:00 | 10 | 0 TTFIN | 2021-12-26 | 2021-12-21 08:41:00 | 2021-12-21 08:56:00 | 11 | 0 TTFIN | 2021-12-26 | 2021-12-21 08:56:00 | 2021-12-21 08:57:00 | 12 | 0 TTFIN | 2021-12-26 | 2021-12-21 08:57:00 | 2021-12-21 08:57:00 | 13 | 0 TTFIN | 2021-12-26 | 2021-12-21 14:04:00 | 2021-12-21 14:04:00 | 14 | -18420 TTFIN | 2021-12-26 | 2021-12-21 14:04:00 | 2021-12-21 14:08:00 | 15 | 0 TTFIN | 2021-12-26 | 2021-12-21 14:08:00 | 2021-12-21 15:18:00 | 16 | 0 TTFIN | 2021-12-26 | 2021-12-21 15:18:00 | 2021-12-21 15:31:00 | 17 | 0 TTFIN | 2021-12-26 | 2021-12-21 15:31:00 | 2021-12-21 17:15:00 | 18 | 0 TTFIN | 2021-12-26 | 2021-12-21 17:15:00 | 2021-12-21 17:31:00 | 19 | 0 TTFIN | 2021-12-26 | 2021-12-21 17:31:00 | 2021-12-21 18:50:00 | 20 | 0 TTFIN | 2021-12-26 | 2021-12-21 18:50:00 | 2021-12-22 00:00:00 | 21 | 0 (21 rows) select sum(skip_time_s) from (select datediff('second', start_time_utc, lag(end_time_utc) over (order by start_time_utc) ) as skip_time_s from skip_time) xyz ; sum -------- -19200 (1 row)
1