Re: [GENERAL] How to use daterange type?
2013/3/17 Csanyi Pal csanyi...@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
Re: [GENERAL] 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
x...@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
