Обсуждение: How to use daterange type?
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? -- Regards from Pal
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
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, oruse a function that determines whether or not a particular day is holiday or not. -- -- Christophe Pettus xof@thebuild.com