How do I generate a time series in PostgreSQL?

Question

If you're looking to generate a date series, see this question

Let's say I want to generate a series for every 5 minutes for 24 hours. How do I do that in PostgreSQL?

PostgreSQL can generate_series() from a timestamp, but not from time.

Is it better to pick an arbitrary timestamp, or is there another way to generate the series?

Answer 1

To optimize:

SELECT x::time
FROM   generate_series(timestamp '2000-01-01'
                     , timestamp '2000-01-02'
                     , interval  '5 min') t(x);

The date is irrelevant, so use arbitrary timestamp constants. The cast to time is very cheap.
This includes lower and upper bound, so we get '00:00' twice. Use '2000-01-01 23:59' as upper bound to get it once only.

Related:

• Generating time series between two dates in PostgreSQL

Answer 2

Not sure if this is the best way, but we can use generate_series to generate the min-offset from 00:00:00 and then simply call make_interval(mins=>) to get the interval from it.

SELECT make_interval(mins=>x)::time
FROM generate_series(0, 60*24-5, 5) AS t(x);

Answer 3

I liked @EvanCarroll way, but yet another option -

select  x::time
from    generate_series 
            (current_date,current_date + '1 day - 1 second'::interval,'5 minute') as t(x);

Answer 4

Another way:

SELECT '00:00:00'::time + x * '1 minute'::interval
FROM generate_series(0, 60*24, 5) AS t(x);