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:

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

what is the point of this? just get now in your code and then make the list in your code rather then sql. — Eluvatar
– Eluvatar, Commented Sep 27, 2013 at 20:39
generate_series( (now()-'1 week::interval)::date, now()::date) — wildplasser
– wildplasser, Commented Sep 27, 2013 at 20:42
@Eluvatar: a possible use case is an outer join to be able to count things that "aren't there" — user330315
– user330315, Commented Sep 27, 2013 at 20:49

Erwin Brandstetter · Accepted Answer · 2013-09-30 12:32:55Z

4

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.

edited Sep 30, 2013 at 12:32

answered Sep 27, 2013 at 20:41

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

7 Comments

user330315 Over a year ago

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

Erwin Brandstetter Over a year ago

@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

user330315 Over a year ago

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)

mu is too short Over a year ago

Are there hidden time zone problems in those casts?

Erwin Brandstetter Over a year ago

@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.

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related