Return list with dates within a specified range from a single query

Question

I'm trying to obtain a list of dates within range, similar to the command NOW() in PostgreSQL, with the only difference that now(), returns only the current date.

If I execute it as follows I obtain:

select now();

2013-09-27 15:27:50.303-05

Or by example, I could do this:

select now() - interval '1' day;

and the result is yesterday

2013-09-27 15:27:50.303-05

What I need is a query that could return a list with every date within a given range, so if I provide 2013-09-20 and 2013-09-27 (I'm nor really interested in hours, only dates) I would like to obtain an output as follows:

2013-09-20
2013-09-21
2013-09-22
2013-09-23
2013-09-24
2013-09-25
2013-09-26
2013-09-27

Any ideas on how to achieve this? By preference without using stored procedures or functions, unless there is no other way ...

Conwell

what is the point of this? just get now in your code and then make the list in your code rather then sql. – Mangrum

generate_series( (now()-'1 week::interval)::date, now()::date) – Matterhorn

@Eluvatar: a possible use case is an outer join to be able to count things that "aren't there" – Southing

Answer 1

Use generate_series(), does exactly what you need:

SELECT generate_series('2013-09-20'::date
                     , '2013-09-27'::date
                     , interval '1 day')::date;

Takes two timestamp variables, but dates are also accepted.
Returns timestamp with time zone, so I cast to date according to your request.

A more verbose, but syntactically clearer version is to use the set returning function (SRF) as FROM item:

SELECT *
FROM   generate_series('2013-09-20'::date
                     , '2013-09-27'::date
                     , interval '1 day')::date;

Consider the comments below.

Mod

Using set returning functions in the select part is somewhat "deprecated". It's better to use them only in the from part. – Southing

@a_horse_with_no_name: Deprecated? Would you have a source for that? I'll offer a source to the contrary: SELECT generate_subscripts('{NULL,1,NULL,2}'::int[], 1) AS s; .. direct quote from the current manual – Mod

Note exactly deprecated (that's why I put it in quotes) - but at least undocumented. There are discussions about this "feature" on the mailing list now and then, e.g. here: postgresql.1045698.n5.nabble.com/… (that's about having more than one srf in the select list though) – Southing

Are there hidden time zone problems in those casts? – Afrika

@muistooshort: No. Unlike the case you link to, my example uses consistent data types. Everything is computed according to local time zone, no corner case. – Mod

@a_horse_with_no_name: I have found a particularly interesting thread in pgsql-hackers dealing with the patch for the upcoming WITH ORDINALITY. It's also worth mentioning that Function Calls in FROM are also not in the SQL standard. – Mod

Yes, I remember those. But still, having a SRF in the select list seems weird. It's like writing select (select col from foo) - which wouldn't be allowed – Southing