From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Return count between timestamps |
Date: | 2006-03-19 09:41:52 |
Message-ID: | 20060319094151.GA6441@KanotixBox |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Jonathan Davies <jonathan(at)nixondesign(dot)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 group by 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°
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2006-03-19 11:51:13 | Re: Return count between timestamps |
Previous Message | shakil tanvir | 2006-03-19 04:23:53 | Re: Multiple insertion |