Question 269969 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to round up when using generate_series()?

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    … 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:

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search