Skip site navigation (1) Skip section navigation (2)

Re: date range to set of dates expansion

From: hari(dot)fuchs(at)gmail(dot)com
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: date range to set of dates expansion
Date: 2012-01-19 08:32:27
Message-ID: 87mx9kks84.fsf@hf.protecting.net (view raw or flat)
Thread:
Lists: pgsql-sql
Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> writes:

> Hi,
>
> How can I expand a date range in a table to a set of date records?
>
> I have a table of availabilities thus:
>
>   Column   |            Type             |                         Modifiers
> -----------+-----------------------------+------------------------------------------------------------
>  aid       | integer                     | not null default 
> nextval('availability_aid_seq'::regclass)
>  asid      | integer                     | not null
>  asdate    | date                        | not null
>  afdate    | date                        | not null
>  adays     | integer                     |
>  acomments | text                        |
>
>
> asdate is the start date
> afdate is the finish date
>
> How can I expand this to a set of
>
>   Column   |            Type             |                         Modifiers
> -----------+-----------------------------+------------------------------------------------------------
>  aid       | integer                     | not null 
>  asid      | integer                     | not null
>  adate    | date                        | not null
>  acomments | text                        |
>
> i.e.
>
>  aid | asid |   asdate   |   afdate   | adays |      acomments
> -----+------+------------+------------+-------+--------------------
>   12 |    1 | 2007-08-11 | 2007-08-12 |     1 | Early finish Sunday
>
> Becomes
>
>  aid | asid |   asdate   |      acomments
> -----+------+------------+--------------------
>   12 |    1 | 2007-08-11 | Early finish Sunday
>   12 |    1 | 2007-08-12 | Early finish Sunday
>
> I have a function date_range to return a set of dates, but so far I can't get 
> a valid view to work.

Why don't you just use the built-in PostgreSQL function for that?

SELECT aid, asid,
       generate_series (asdate, afdate, INTERVAL '1 day')::date AS asdate,
       acomments
FROM tbl


In response to

Responses

pgsql-sql by date

Next:From: Rehan SaleemDate: 2012-01-19 09:57:09
Subject: how to return whole table from Function not just the id integer column
Previous:From: Gary StainburnDate: 2012-01-19 07:13:34
Subject: date range to set of dates expansion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group