Re: Return count between timestamps
От | Andreas Kretschmer |
---|---|
Тема | Re: Return count between timestamps |
Дата | |
Msg-id | 20060319094151.GA6441@KanotixBox обсуждение исходный текст |
Ответ на | Return count between timestamps ("Jonathan Davies" <jonathan@nixondesign.com>) |
Ответы |
Re: Return count between timestamps
(Andreas Kretschmer <akretschmer@spamfence.net>)
|
Список | pgsql-novice |
Jonathan Davies <jonathan@nixondesign.com> schrieb: > > Hi > I have a car parking reservation table that stores 2 timestamps entry_date and > exit_date. > There is a maximum number of car parking places, and I want to check that on > each day between the 2 requested reservation dates, the count of the existing > records does not exceed the maximum. > So I need some elegantsystem of getting a count of all the existing bookings > for each of the days between the entry date and the exit date. > Unfortunately I have no idea how to GROUP by the dates between&. > SELECT COUNT(id) as num_places FROM reservations r WHERE (('$entry_date' > > r.entry_date) AND ('$entry_date' < r.exit_date)) OR (('$exit_date' > > r.entry_date) AND ('$exit_date' < r.exit_date)); > This obviously only returns the sum total is there anyway I get this on a day > by day basis? Yes. Example: test=# select * from parking ; id | entry | exit ----+------------+------------ 1 | 2006-03-01 | 2006-03-30 2 | 2006-03-15 | 2006-04-15 3 | 2006-03-30 | 2006-04-30 (3 rows) Now, i want to know all reservations between 2006/03/13 and the next 20 days, i create a table callend calendar: test=# create table calendar as select ('2006/03/13'::date + (generate_series(0,20) ||'days')::interval) as datum; SELECT Now, i can calculate the reservations for each day: test=# select a.datum, sum(case when a.datum between b.entry and b.exit then 1 else 0 end) from calendar a, parking b groupby a.datum order by 1; datum | sum ---------------------+----- 2006-03-13 00:00:00 | 1 2006-03-14 00:00:00 | 1 2006-03-15 00:00:00 | 2 2006-03-16 00:00:00 | 2 2006-03-17 00:00:00 | 2 2006-03-18 00:00:00 | 2 2006-03-19 00:00:00 | 2 2006-03-20 00:00:00 | 2 2006-03-21 00:00:00 | 2 2006-03-22 00:00:00 | 2 2006-03-23 00:00:00 | 2 2006-03-24 00:00:00 | 2 2006-03-25 00:00:00 | 2 2006-03-26 00:00:00 | 2 2006-03-27 00:00:00 | 2 2006-03-28 00:00:00 | 2 2006-03-29 00:00:00 | 2 2006-03-30 00:00:00 | 3 2006-03-31 00:00:00 | 2 2006-04-01 00:00:00 | 2 2006-04-02 00:00:00 | 2 (21 rows) HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
В списке pgsql-novice по дате отправления: