CODEWITHSUNDEEP
sqlpostgresql
Shvalb
Shvalb

Reputation: 1923

Select by time range and segment results by 1 hour

I need help with the following requirement:

I have the following table:

id | status  | created_at             | closed_at
1     'OPEN'   '2019-05-08T12:30:24Z'   null
2     'CLOSED' '2019-05-08T12:50:22Z'   '2019-05-08T13:05:53Z'
3     'CLOSED' '2019-05-08T13:20:00Z'   '2019-05-08T13:40:12Z'
4     'CLOSED' '2019-05-08T13:55:47Z'   '2019-05-08T14:05:36Z'
5     'OPEN'   '2019-05-08T14:15:57Z'   null
6     'CLOSED' '2019-05-08T14:30:29Z'   '2019-05-08T14:40:00Z'
7     'CLOSED' '2019-05-08T14:55:38Z'   '2019-05-08T15:05:51Z'

For time range created_at='2019-05-08T13:00:00Z' closed_at ='2019-05-08T15:00:00Z' The output should be:

timestamp             |    id
2019-05-08 13:00:00      1,2,3,4
2019-05-08 14:00:00      1,4,5,6,7

Notice that 1 exists at 13:00 and at 14:00 because it's created_at starts at 12:30 and it doesn't have a closed_at (null) so it "lives" throughout the entire time range.

The closest I could come up with is the following:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' +
    INTERVAL '1 second' * round(extract('epoch' from created_at) / 3600) * 3600 as created_at,
    id
FROM issues 
WHERE (created_at BETWEEN '2019-05-08T13:00:00Z' AND '2019-05-08T15:00:00Z') OR 
    (closed_at BETWEEN '2019-05-08T13:00:00Z' AND '2019-05-08T15:00:00Z') OR
    (status='OPEN')
GROUP BY 
    created_at,id
ORDER BY created_at;

which gives

"2019-05-08 13:00:00+00"    "3"
"2019-05-08 13:00:00+00"    "2"
"2019-05-08 13:00:00+00"    "1"
"2019-05-08 14:00:00+00"    "4"
"2019-05-08 14:00:00+00"    "5"
"2019-05-08 15:00:00+00"    "6"
"2019-05-08 15:00:00+00"    "7"

Is that even possible to do from a select query?

Upvotes: 0

Views: 58

Answers (1)

Jeremy
Jeremy

Reputation: 6713

Assuming you meant created_at = '2019-05-08T13:00:00Z', you should be able to do this pretty easily with tstzranges.

WITH time_ranges as 
   (SELECT tstzrange(t, t + '1 hour'::interval, '[]') as t_range 
    FROM generate_series('2019-05-08T13:00:00Z'::timestamptz, 
                         '2019-05-08T14:00:00Z'::timestamptz, 
                         '1 hour'::interval) g(t))
,
test_values AS (
  SELECT * from (values
  (1, '2019-05-08T12:30:24Z'::timestamptz, null::timestamptz),
  (2, '2019-05-08T12:50:22Z', '2019-05-08T13:05:53Z'),
  (3, '2019-05-08T13:20:00Z', '2019-05-08T13:40:12Z'),
  (4, '2019-05-08T13:55:47Z', '2019-05-08T14:05:36Z'), 
  (5, '2019-05-08T14:15:57Z', null), 
  (6, '2019-05-08T14:30:29Z', '2019-05-08T14:40:00Z'), 
  (7, '2019-05-08T14:55:38Z', '2019-05-08T15:05:51Z')
  ) v(id, created_at, closed_at)
)
select lower(t_range), string_agg(id::text, ',' ORDER BY id)
FROM time_ranges
JOIN test_values on tstzrange(created_at, closed_at, '[]') && t_range
GROUP BY lower(t_range);
         lower          | string_agg
------------------------+------------
 2019-05-08 13:00:00+00 | 1,2,3,4
 2019-05-08 14:00:00+00 | 1,4,5,6,7
(2 rows)

The only somewhat tricky part is joining on the overlap between the tstzrange from created_at to closed_at and the given hours. You don't need the status column at all.

Upvotes: 2

Related Questions