Обсуждение: Return count between timestamps

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 ‘elegant’ system 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?

Many thanks

Jonathan

- - - - - - - - - - - - - - - - - -

Nixon.
+44 (0)1736 758600

www.nixondesign.com

White’s Warehouse
Foundry Square
Hayle
Cornwall
TR27 4HH UK

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°

Andreas Kretschmer <akretschmer@spamfence.net> schrieb:

> 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.

Btw.:

you can create a trigger like this:
(with a fixed limit of 6 places)


create or replace function check_parking() returns trigger as $$
declare counter int;
begin
        select into counter max(x.sum) from (select (NEW.entry + (g.n||'days')::interval)::date,
                sum(case when (NEW.entry + (g.n||'days')::interval)::date between a.entry and a.exit then 1 else 0 end)
        from parking a, generate_series(0,NEW.exit-NEW.entry) g(n) group by g.n order by 1) as x;
        if counter > 5 then -- limit -1!!!
                raise exception 'not possible';
        else
                return NEW;
        end if;
end;
$$ language plpgsql;

create trigger parking_check before insert or update on parking for each row execute procedure check_parking();

Now a test:

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)

test=# insert into parking values (4,'2006/03/02','2006/03/20');
INSERT 0 1
test=# insert into parking values (5,'2006/03/12','2006/03/20');
INSERT 0 1
test=# insert into parking values (5,'2006/03/10','2006/03/20');
INSERT 0 1
test=# insert into parking values (6,'2006/03/10','2006/03/20');
INSERT 0 1
test=# insert into parking values (7,'2006/03/10','2006/03/20');
ERROR:  not possible


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°