From: hari.fuchs@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: date range to set of dates expansion
Date: Thu, 19 Jan 2012 09:32:27 +0100
Message-ID: <87mx9kks84.fsf@hf.protecting.net> (raw)
References: <201201190713.34333.gary.stainburn@ringways.co.uk>
Gary Stainburn <gary.stainburn@ringways.co.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hari.fuchs@gmail.com
Subject: Re: date range to set of dates expansion
In-Reply-To: <87mx9kks84.fsf@hf.protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox