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

Need advice on table structure for DATE & OVERLAPS

From: Fred Janon <fjanon(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Need advice on table structure for DATE & OVERLAPS
Date: 2009-08-19 11:02:52
Message-ID: 2fd0c7810908190402l452fd7cag8c9dca5392cab5f6@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,

I need to create a database to find events that have a start date and an end
date and fall between a certain start date and end date specified by the
user. I first designed my table where the start/end dates are part of the
events table, like this:

CREATE TABLE events
(
  id serial NOT NULL,
  title varchar,
  startdate date NOT NULL,
  enddate date NOT NULL,
  CONSTRAINT pk_id PRIMARY KEY (id)
);

I was planning to use queries using OVERLAPS like this:
SELECT * FROM events WHERE (startdate, enddate) OVERLAPS (DATE
'2009-02-02',DATE '2009-01-03');

Now I wonder if it would be more efficient to have the table 'events'
pointing at another table 'times' containing the tuples (startdate, endate)
and doing the overlap on the 'times' table and finding the events that
points to the 'times'. That would allow re-use of the (startdate,enddate)
tuples.

Thanks

Fred

pgsql-novice by date

Next:From: Sean DavisDate: 2009-08-19 11:11:05
Subject: Re: The efficient method to create trigger
Previous:From: Sean DavisDate: 2009-08-19 11:02:38
Subject: Re: Why PostgreSQL?

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