How to get up to last record from Generate series from 00 - 23:59 time but with 15 minutes interval-CodePudding

I am trying to use generate_series in postgres so that I can get all the last record from 00:00 - 23:59 with 15 minutes interval, the problem is, I only receive 23:45 for the last record, how does it work with 00:00 - 23:59 so that I can get the last record which is 23:59

Note that time here is actually from the column but I put the value here directly.

Here is the query,

generate_series(('00:00:00'   '2021-10-03'::date)::timestamp,('23:59:00'   '2021-10-03'::date )::timestamp, interval '15m') as "time"

CodePudding user response：

Can we do it like this:

SELECT generate_series('2021-10-03 00:00'::timestamp,'2021-10-03 23:59', '15 minute')
union 
SELECT generate_series('2021-10-03 23:59'::timestamp,'2021-10-03 23:59', '1 minute');

SELECT generate_series('2021-10-03 00:00'::timestamp,'2021-10-03 23:59', '15 minute')
union 
SELECT '2021-10-03 23:59';

Not sure if it is correct way or not.

CodePudding user response：

Instead of creating a single value for each create a time zone range (tsrange). In creating the range indicate it is to include the lower bound but not the upper bound.

select tsrange( ts, ts interval '15 min', '[)') timestamp_range
  from generate_series('2021-10-03 00:00'::timestamp  -- ts 1
                      ,'2021-10-03 23:59'::timestamp  -- ts 2 
                      ,'15 minute'
                      )  gn(ts);

The ranges can be extended as you want just by changing the ts 1 and/or ts 2.