SQL generating a set of dates

Question

I am trying to find a way to have a SELECT statement return the set of dates between 2 input dates with a given interval. I would like to be able to easily change the time frame and interval that would be returned, so hard coding something with a series of SELECT ... UNIONs would not be ideal.

For example: I want all the dates at 5 second intervals for the last 60 seconds.

Expected:

            times
---------------------
2009-02-05 08:00:00
2009-02-05 08:00:05
2009-02-05 08:00:10
2009-02-05 08:00:15
2009-02-05 08:00:20
...
2009-02-05 08:00:55

Edit: generate_series(...) can be used in place of a table in the SELECT and simulates a table with a series of numbers in it with a given start value, end value and optionally a step. From there it can be CAST to the type I need for time functions and manipulated during the SELECT.

Thanks Quassnoi.

Quassnoi · Accepted Answer · 2009-02-05 17:05:20Z

4

SELECT CAST (s || ' seconds' AS INTERVAL) + TIMESTAMP 'now'
FROM generate_series(0, -60, -5) s

edited Feb 5, 2009 at 17:05

answered Feb 5, 2009 at 16:49

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Quassnoi Over a year ago

That TIMESTAMP arithmetics is really hard at PostgreSQL :)

Collectives™ on Stack Overflow

SQL generating a set of dates

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related