CODEWITHSUNDEEP
sqlpostgresql
morne
morne

Reputation: 4189

Postgres date subtraction in a query

I want to do a dynamic query which always takes an interval of todays_date and todays_date - 30

SELECT day::date
FROM   generate_series('2014-08-01'::date, '2014-09-14'::date, interval '1 week') day

But with current date, something like this

SELECT day::date
FROM   generate_series(CURRENT_DATE, CURRENT_DATE - 30, interval '1 week') day

Upvotes: 0

Views: 172

Answers (1)

krisku
krisku

Reputation: 3993

You had it almost right. Try this (for an incrementing series):

SELECT day::date
FROM   generate_series(CURRENT_DATE - interval '30 days', CURRENT_DATE, interval '1 week') day

Or if you really want to go backward:

SELECT day::date
FROM   generate_series(CURRENT_DATE, CURRENT_DATE - interval '30 days', -interval '1 week') day

Upvotes: 1

Related Questions