How to round up when using generate_series()?

Question

I've have a Postgres database and I'm using Grafana to visualise some data.

For instance, my Grafana panel has the following variables where the values are dynamic:

• $__timeFrom() = '2023-10-21T00:00:00Z'
• $__timeTo() = '2023-10-21T00:25:00Z'
• $__interval = '15m'

These variables are used to generate a time series table, where the time is generated like this:

SELECT *
FROM generate_series('2023-10-21T00:00:00Z'::timestamp, '2023-10-21T00:25:00Z'::timestamp, '15m')
ORDER BY 1;

The above query gives me the following slots:

• 2023-10-21 00:00:00.000000
• 2023-10-21 00:15:00.000000

But I would like it to round up and generate a slot for the remaining time, by having a third slot with either the end date or the next value in the interval:

• 2023-10-21 00:00:00.000000
• 2023-10-21 00:15:00.000000
• 2023-10-21 00:25:00.000000 / 2023-10-21 00:30:00.000000

How can I solve this?

Answer 1

... by having a third slot with either the end date or the next value in the interval:

To get the latter (bold emphasis), get the "floor" for the next full interval with date_bin() and add one stride:

SELECT generate_series(a, date_bin(intv, z, a) + intv, intv);

See:

• What is the fastest way to truncate timestamps to 5 minutes in Postgres?

That goes one step too far when the end falls on the full interval exactly. To also fix that corner case:

SELECT generate_series(a, CASE WHEN date_bin(intv, z, a) = z THEN z ELSE date_bin(intv, z, a) + intv END, intv)

Demo with input values:

SELECT generate_series(a, CASE WHEN date_bin(intv, z, a) = z THEN z ELSE date_bin(intv, z, a) + intv END, intv)
FROM  (
   VALUES (timestamp '2023-10-21 00:00'
         , timestamp '2023-10-21 00:25'
         , interval '15 min')
   ) input(a, z, intv);

fiddle

A bit more verbose than nbk's smart solution with UNION, but computing the ceiling once should be faster than running a UNION over a possibly large set.
Also, this ends on the next interval, while nbk ends on the given value. You may prefer one or the other.

Answer 2

that depends what result you need

SELECT *
FROM generate_series('2023-10-21T00:00:00Z'::timestamp, '2023-10-21T00:25:00Z'::timestamp + interval '15 minute', '15m')
ORDER BY 1;

generate_series
2023-10-21 00:00:00
2023-10-21 00:15:00
2023-10-21 00:30:00

SELECT 3

SELECT *
FROM generate_series('2023-10-21T00:00:00Z'::timestamp, '2023-10-21T00:25:00Z'::timestamp, '15m')
  UNION SELECT '2023-10-21T00:25:00Z'::timestamp
ORDER BY 1;

generate_series
2023-10-21 00:00:00
2023-10-21 00:15:00
2023-10-21 00:25:00

SELECT 3

fiddle