How to use daterange type?

2013/3/17 Csanyi Pal <csanyipal@gmail.com>:

Hi,

I'm using postgresql 9.2.

I'm trying to figure out how can I use daterange type in my database
that is supposed to be a school calendar.

I did the followings at the postgresql command prompt:

create database schoolcalendar;
create table semester_1 ( schooldays daterange );
insert into semester_1 values ( '[2012-09-01, 2012-12-24]' );

So how can I use this table further eg. to get dates of the school days
but without Saturdays and Sundays?

I don't think there's a built-in way of doing that. You could write a function
which takes the daterange as an argument and iterates between the
daterange's lower and upper bounds but skipping dates which are
Saturdays and Sundays.

Regards

Ian Barwick

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mar 16, 2013, at 2:05 PM, Csanyi Pal wrote:

So how can I use this table further eg. to get dates of the school days
but without Saturdays and Sundays?

You can't do that directly (that kind of calendar operation is outside of the scope of a range type). You can, however, easily write selects that handle that:

postgres=# SELECT count(*)
postgres-# FROM generate_series(lower('[2012-09-01, 2012-12-24]'::daterange)::timestamp, upper('[2012-09-01, 2012-12-24]'::daterange)::timestamp, '1 day') as day
postgres-# WHERE EXTRACT(dow FROM day) BETWEEN 1 AND 5;
count
-------
82
(1 row)

In cases where you have more complex calendars (like lists of bank holidays), you could join against a table of them, or use a function that determines whether or not a particular day is holiday or not.

--
-- Christophe Pettus
xof@thebuild.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general