Options
Vertica Query to find and subtract skips in time
slc1axj
Vertica Customer ✭
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
-
Options
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