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:

Best Answer

  • moshegmosheg Administrator
    edited January 27 Answer ✓
    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.