Using two timestamp columns in SQL to generate counts at a "point-in_time"

Question

I have a Postgres database with a ticket table. Each ticket has a 'created_at' timestamp and a 'resolved_at' timestamp. I would eventually like to create a time series visualization to track the number of 'unresolved' tickets over time.

For example, I would like to look at the last 24 hours and see how many tickets were unresolved at the end of each hour, i.e. how many were created_at < "point-in-time" AND resolved_at > "point-in-time".

I have no idea how I begin to query this sort of information. How would I reference the multiple "point-in-time values" in my query?

Example setup:

CREATE TABLE tickets (
    id int,
    created_at timestamp,
    resolved_at timestamp
);

INSERT INTO tickets VALUES
(1, '2019-10-01 01:30:00'::timestamp, '2019-10-01 05:45:00'::timestamp),
(2, '2019-10-01 02:30:00'::timestamp, '2019-10-01 05:45:00'::timestamp),
(3, '2019-10-01 03:30:00'::timestamp, '2019-10-01 05:45:00'::timestamp),
(4, '2019-10-01 04:30:00'::timestamp, '2019-10-01 05:45:00'::timestamp),
(5, '2019-10-01 05:30:00'::timestamp, '2019-10-01 05:45:00'::timestamp);

I can see from looking at this data that at 01:00 we have 0 unresolved, at 02:00 1 unresolved (ticket 1), ... , 05:00 4 unresolved, 06:00 0 unresolved (all the tickets get resolved by 05:45). I'm not sure how to query for this information

Welcome! I'm a relative newcomer here, and I'll be the first to warn you that you're likely to get scolded for posting images of text. It's easier for people to help if they can see, copy, and work with your example. Can you edit your question to include the table definition, sample data, and target output as text? Chances are, that will get you a good answer quickly. https://stackoverflow.com/help/how-to-ask — Morris de Oryx, Oct 12 '19 at 19:58
thanks for the tip, will try to make it a bit easier to work with — jjl_1994, Oct 13 '19 at 10:41

score 1 · Accepted Answer · answered Oct 13 '19 at 12:39

We will find a solution in three logical steps. First, get a series of events in time:

select created_at as event_time, 1 as change
from tickets
union all
select resolved_at, -1
from tickets
order by 1

     event_time      | change 
---------------------+--------
 2019-10-01 01:30:00 |      1
 2019-10-01 02:30:00 |      1
 2019-10-01 03:30:00 |      1
 2019-10-01 04:30:00 |      1
 2019-10-01 05:30:00 |      1
 2019-10-01 05:45:00 |     -1
 2019-10-01 05:45:00 |     -1
 2019-10-01 05:45:00 |     -1
 2019-10-01 05:45:00 |     -1
 2019-10-01 05:45:00 |     -1
(10 rows)

Next, round the times to full hours and sum the changes:

select 
    date_trunc('hour', event_time+ interval '1h') as time_point, 
    sum(change) as change
from (
    select created_at as event_time, 1 as change
    from tickets
    union all
    select resolved_at, -1
    from tickets
    ) s
group by 1
order by 1

     time_point      | change 
---------------------+--------
 2019-10-01 02:00:00 |      1
 2019-10-01 03:00:00 |      1
 2019-10-01 04:00:00 |      1
 2019-10-01 05:00:00 |      1
 2019-10-01 06:00:00 |     -4
(5 rows)

Finally, get a cumulative sum of changes in consecutive periods:

select 
    time_point, 
    sum(change) over (order by time_point) as unresolved
from (
    select
        date_trunc('hour', event_time+ interval '1h') as time_point, 
        sum(change) as change
    from (
        select created_at as event_time, 1 as change
        from tickets
        union all
        select resolved_at, -1
        from tickets
        ) s
    group by 1
    ) s 
order by 1

     time_point      | unresolved 
---------------------+------------
 2019-10-01 02:00:00 |          1
 2019-10-01 03:00:00 |          2
 2019-10-01 04:00:00 |          3
 2019-10-01 05:00:00 |          4
 2019-10-01 06:00:00 |          0
(5 rows)

In the final query you can use a timestamp series (generated by the function) to avoid gaps:

select 
    time_point, 
    coalesce(sum(change) over (order by time_point), 0) as unresolved
from 
    generate_series(timestamp '2019-10-01 01:00', '2019-10-01 06:00', interval '1h') as time_point
left join (
    select
        date_trunc('hour', event_time+ interval '1h') as time_point, 
        sum(change) as change
    from (
        select created_at as event_time, 1 as change
        from tickets
        union all
        select resolved_at, -1
        from tickets
        ) s
    group by 1
    ) s using(time_point)
order by 1

Using two timestamp columns in SQL to generate counts at a "point-in_time"

1 Answers1