2

I have a record of session durations with start and end timestamps:

user_id | session_id | session_start                 | session_end
--------+------------+-------------------------------+------------------------------
1       | 1          | 2021-02-25 10:10:00.000 +0100 | 2021-02-25 10:20:00.000 +0100
1       | 2          | 2021-02-25 10:50:00.000 +0100 | 2021-02-25 10:55:00.000 +0100
1       | 3          | 2021-02-25 11:40:00.000 +0100 | 2021-02-25 12:30:00.000 +0100

Getting the duration of each session is as simple as subtracting the two timestamps. Now, I would like to represent the session duration with wall clock hourly buckets, summed per user.

The main problem here are sessions where the interval spans over multiple hours. A session that starts at 11:40 and ends at 12:30 should be represented with a bucket for 11:00 with 20 minutes and a bucket for 12:00 with 30 minutes:

user_id | bucket   | duration
--------+----------+---------
1       | 00:00:00 | 00:00:00
1       | 01:00:00 | 00:00:00
...
1       | 10:00:00 | 00:15:00
1       | 11:00:00 | 00:20:00
1       | 12:00:00 | 00:30:00

I tried using time_series and date_trunc, but wasn't successful.

Ideally, the bucket would also include the date, which could possibly also simplify the logic. If not, selecting one day at a time would be also fine.

user_id | bucket              | duration
--------+---------------------+----------
1       | 2021-02-25 00:00:00 | 00:00:00
1       | 2021-02-25 01:00:00 | 00:00:00
...
1       | 2021-02-25 10:00:00 | 00:15:00
1       | 2021-02-25 11:00:00 | 00:20:00
1       | 2021-02-25 12:00:00 | 00:30:00

I will use the results of the query to produce a heatmap with users on one axis and hours on the other.

3
  • Hi, and welcome to dba.se! Can these periods run for over an hour?
    – Vérace
    Jan 19, 2022 at 14:16
  • Also, what is the finest granularity of these periods? Is it in 10 minute slots - or can be down to 1 minute? Or even seconds? Fractions of seconds?
    – Vérace
    Jan 19, 2022 at 14:34
  • The periods can run for multiple hours. 1 minute granularity is enough. It's not critical if 00:01:50 gets rounded to 00:01:00.
    – amq
    Jan 19, 2022 at 15:48

2 Answers 2

1

Generate a series of timestamp ranges, join with your data, calculate the overlap and aggregate:

SELECT user_id,
       bucket,
       coalesce(sum(upper(inters) - lower(inters)), 0) AS duration
FROM (SELECT user_id,
             lower(ranges.r) AS bucket,
             tsrange(tab.start, tab.end, '[)') * ranges.r AS inters
      FROM (SELECT tsrange(st, st + '1 hour'::interval, '[)') AS r
            FROM generate_series(
                    '2021-01-01 00:00:00'::timestamp,
                    '2021-12-31 23:00:00'::timestamp,
                    '1 hour'::interval
                 ) AS g(st)
           ) AS ranges
         LEFT JOIN tab
            ON tsrange(tab.start, tab.end, '[)') * ranges.r <> 'empty'
     ) AS intersections
GROUP BY tab.user_id, bucket;
2
  • How could I get rows for all ranges - also where there is no corresponding tab entry (where duration i zero, where user_id is null)?
    – amq
    Jan 20, 2022 at 17:36
  • Use an outer join. Jan 20, 2022 at 17:53
1

You can do something like this - all of the code below is available on the fiddle here. The solution makes use of the PostgreSQL range types - an extremely powerful tool, especially for this sort of work. I also use a materialised calendar table, generated as explained here.

CREATE TABLE test
(
  user_id    INTEGER   NOT NULL,
  session_id INTEGER   NOT NULL,
  s_start    TIMESTAMP NOT NULL,
  s_end      TIMESTAMP NOT NULL     
);

and then populate it:

INSERT INTO test VALUES
(1, 1, '2021-02-25 10:10:00.000 +0100',   '2021-02-25 10:20:00.000 +0100'),

(1, 2, '2021-02-25 10:50:00.000 +0100',   '2021-02-25 10:55:00.000 +0100'),

(1, 3, '2021-02-25 11:40:00.000 +0100',   '2021-02-25 12:30:00.000 +0100'),

(1, 4, '2021-02-26 11:46:00 +0100', '2021-02-26 11:57:00 +0100');  
-- added for testing - not 4 minutes and 7 minutes.

I added the last record for testing - the time slot's boundaries don't match the 10 minutes of the calendar table.

Now, you have to have a calendar table - this is to JOIN with your time slots and perform calculations.

CREATE TABLE calendar_range
(
  dr          TSTZRANGE NOT NULL,
  minute_slot SMALLINT NOT NULL,
  hour_slot   SMALLINT NOT NULL
);

Now, this takes 50 bytes per record, so for ~ 100MB, you'll have > 30 years of records - or you may care to generate it dynamically as per the other answer - your storage, CPU and RAM will tell you what to do here - I would suggest that a permanent calendar table is the better solution, especially if you are doing this sort of calculation regularly! It will also be more performant.

I've left some of my first queries on the fiddle - this is the final one:

INSERT INTO calendar_range (dr, minute_slot, hour_slot)
SELECT
  TSTZRANGE
  (
    '2021-01-01 00:00:00'::TIMESTAMPTZ 
      + (m ||    ' MINUTE')::INTERVAL,
    
    '2021-01-01 00:10:00'::TIMESTAMPTZ
      + (m ||   ' MINUTE')::INTERVAL,

    '[)'
  ) AS tsr,

  (m % 60)/10 AS slot,
  
  DATE_PART
  (
    'HOUR',     
    '2021-01-01 00:00:00'::TIMESTAMPTZ 
      + (m ||    ' MINUTE')::INTERVAL 
  ) AS t_hour

FROM
  GENERATE_SERIES(0,  100000,  10) AS t(m);

Just to check our calendar_range table:

SELECT * FROM calendar_range;

Result:

dr  minute_slot     hour_slot
["2021-01-01 00:00:00+00","2021-01-01 00:10:00+00")     0   0
["2021-01-01 00:10:00+00","2021-01-01 00:20:00+00")     1   0
["2021-01-01 00:20:00+00","2021-01-01 00:30:00+00")     2   0
...
... snipped for brevity
...

So, we have a range (inclusive at the start, exclusive at the end boundary), starting in 2021 and continuing for 100 days - enough to cover the sample data from the question.

And then we do a SELECT and JOIN the calendar table to the actual timeslot data as follows:

SELECT
  t.user_id, t.session_id, 
  t.s_start::TIME, t.s_end::TIME,
 
  CASE
    WHEN (LOWER(cr.dr) >= t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN 10
    ELSE 
      CASE
        WHEN (LOWER(cr.dr) < t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN
          EXTRACT(EPOCH FROM (UPPER(cr.dr) - t.s_start)) / 60
        ELSE EXTRACT(EPOCH FROM (t.s_end - LOWER(cr.dr))) / 60
      END
  END AS cas,
  
  cr.dr,
  
  cr.minute_slot,
  cr.hour_slot

FROM test t
JOIN calendar_range cr
  ON TSTZRANGE(t.s_start, t.s_end, '[)') && cr.dr;

Note the use of the && OVERLAPS operator.

Result:

user_id     session_id  s_start     s_end   cas     dr  minute_slot     hour_slot
1   1   2021-02-25 10:10:00     2021-02-25 10:20:00     10  ["2021-02-25 10:10:00+00","2021-02-25 10:20:00+00")     1   10
1   2   2021-02-25 10:50:00     2021-02-25 10:55:00     5   ["2021-02-25 10:50:00+00","2021-02-25 11:00:00+00")     5   10
1   3   2021-02-25 11:40:00     2021-02-25 12:30:00     10  ["2021-02-25 11:40:00+00","2021-02-25 11:50:00+00")     4   11
...
... snipped for brevity
...
1   4   2021-02-26 11:46:00     2021-02-26 11:57:00     4   ["2021-02-26 11:40:00+00","2021-02-26 11:50:00+00")     4   11
1   4   2021-02-26 11:46:00     2021-02-26 11:57:00     7   ["2021-02-26 11:50:00+00","2021-02-26 12:00:00+00")     5   11

The slots are of 10 minutes duration (as per the question - for 1 hour long slots, check out the fiddle here) . I've also included an hour_slot field if you're interested in finding out which hour of the day your event are occurring. You can, of course, vary this according to your requirements - the principle is the same - you can SUM() and GROUP BY your various slots as needed.

Just note that the slot from 11:46 to 11:57 has been accounted for correctly - 4 minutes in the slot from 11:40 - 11:50 and 7 minutes in the slot 11:50 - 12:00. Be sure and check edge cases as they can be easily missed.

Just be sure that you understand the Inclusive/Exclusive bounds notation (beginning and ending square ([]) and round (())) brackets - and how to use them - any confusion can be the source of subtle, tricky-to-find bugs!

Finally, you appear to be using TIMESTAMPs with TIME ZONE - i.e. the TIMESTAMPTZ and their corresponding range types - this is a good thing! You should always use UTC for anything to do with timestamps - and not storing, for example, offsets - since this can vary according to DST (Daylight Savings Time).

Following comments:

How could I get rows for all ranges - also where there is no corresponding tab entry (where duration is zero, where user_id is null)?

SELECT
  COALESCE(t.user_id, 0) AS u_id, COALESCE(t.session_id, 0) AS s_id, 
  t.s_start::TIME, t.s_end::TIME,
  COALESCE(
  
  CASE
    WHEN (LOWER(cr.dr) >= t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN 10
    ELSE 
      CASE
        WHEN (LOWER(cr.dr) < t.s_start) AND (UPPER(cr.dr) <= t.s_end) THEN
          EXTRACT(EPOCH FROM (UPPER(cr.dr) - t.s_start)) / 60
        ELSE EXTRACT(EPOCH FROM (t.s_end - LOWER(cr.dr))) / 60
      END
  END, 0) AS "No. mins",
  
  cr.dr,
  
  cr.minute_slot,
  cr.hour_slot

FROM calendar_range cr
LEFT OUTER JOIN test t
  ON TSTZRANGE(t.s_start, t.s_end, '[)') && cr.dr
WHERE LOWER(cr.dr) >= '2021-02-25 10:00:00'
  AND LOWER(cr.dr) <  '2021-02-25 13:30:00';

Result:

enter image description here

Note the 5 minutes counted for session 2. Also, the range was restricted using the WHERE clause - so as not to have too many empty records. I used an image here rather than posting text because with all of the NULLs, it's very difficult to align records.

Ah, I think my examples gave an impression that I'm looking for 10-minute slots, sorry for that! I was only looking for 1-hour slots

The principle for hourly slots is exactly the same - see the fiddle here.

3
  • Why is minute_slot needed? Wouldn't something like this also work? ``` SELECT n / 24 AS day, n % 24 AS hour, '2020-01-01 00:00:00'::TIMESTAMPTZ + (m || ' DAY')::INTERVAL + (n || ' HOUR')::INTERVAL AS x, '2020-01-01 00:00:00'::TIMESTAMPTZ + (m || ' DAY')::INTERVAL + (n || ' HOUR')::INTERVAL + '1 HOUR'::INTERVAL AS y FROM GENERATE_SERIES(0, 100, 1) AS t(m), GENERATE_SERIES(0, 100, 1) AS u(n); ```
    – amq
    Jan 20, 2022 at 14:58
  • Ah, I think my examples gave an impression that I'm looking for 10-minute slots, sorry for that! I was only looking for 1-hour slots
    – amq
    Jan 20, 2022 at 15:09
  • @amq - see my (now) final answer - it tackles the points you raised in comments about hour-long slots (same principle, different calculation) and how to show empty slots (LEFT OUTER JOIN).
    – Vérace
    Jan 21, 2022 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.