8

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?

12

To optimize:

SELECT x::time
FROM   generate_series(timestamp '2000-01-01 00:00'
                     , timestamp '2000-01-02 00:00'
                     , 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:

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

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

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);
  • This will only work sometimes in someplaces. It's subject to DST borking. See my answer here for more information. – Evan Carroll Jun 12 '17 at 18:19
3

Another way:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.