Skip to main content
Question

Vertica Query to find and subtract skips in time

  • January 27, 2022
  • 1 reply
  • 2 views
slc1axj
Forum|alt.badge.img+1

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.

    1 reply

    mosheg
    Forum|alt.badge.img+2
    • mosheg
    • Participating Frequently
    • January 27, 2022
    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)
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement