CODEWITHSUNDEEP
sqldatetimepostgresql
tatsu
tatsu

Reputation: 115

SQL generating a set of dates

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.

Upvotes: 4

Views: 208

Answers (1)

Quassnoi
Quassnoi

Reputation: 425843

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

Upvotes: 4

Related Questions