Re: How to use daterange type?

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(at)thebuild(dot)com