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

• Evan Carroll: I find something about the arbitrary date to be very ugly. But, I was thinking it would in fact be quicker and better too.
• Erwin Brandstetter: Since the date is irrelevant, it may as well be arbitrary. It's the fastest way.

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);

• Evan Carroll: This will only work sometimes in someplaces. It's subject to DST borking. See my answer here for more information.

Answer 4

Another way:

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