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

date range to set of dates expansion

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: date range to set of dates expansion
Date: 2012-01-19 07:13:34
Message-ID: 201201190713.34333.gary.stainburn@ringways.co.uk (view raw or flat)
Thread:
Lists: pgsql-sql
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.

Also, is there a better method?

CREATE FUNCTION date_range(fdate date, tdate date) RETURNS SETOF date
    AS $$
DECLARE
  wdate date;
BEGIN
  return next fdate;
  wdate:=fdate+1;
  while wdate <= tdate LOOP
    return next wdate;
    wdate:=wdate+1;
  end LOOP;
  return;
END;
$$
    LANGUAGE plpgsql;

-- 
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk 

Responses

pgsql-sql by date

Next:From: hari.fuchsDate: 2012-01-19 08:32:27
Subject: Re: date range to set of dates expansion
Previous:From: Pavel StehuleDate: 2012-01-18 09:25:27
Subject: Re: PostgreSQL Function

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