Skip site navigation (1) Skip section navigation (2)

Return count between timestamps

From: "Jonathan Davies" <jonathan(at)nixondesign(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Return count between timestamps
Date: 2006-03-13 16:51:07
Message-ID: 20060313165110.2B8D25AF0BB@svr4.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: Richard KutDate: 2006-03-13 18:39:11
Subject: Exception in thread "main" java.lang.OutOfMemoryError
Previous:From: Michael GlaesemannDate: 2006-03-13 14:27:28
Subject: Re: Automatic date/time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group