Re: Most efficient way of selecting by date?

On Tue, 2003-09-09 at 15:30, Simon Willison wrote:
> Hi all,
>
> I have a table which includes a field for the date and time an item was
> added to it. What's the most efficient way of selecting all of the items
> added on a specific date (this is a really basic question but I'm new to
> Postgres)? Also, would it be worth putting an index on the date column
> as most of my selects will be using it?

If you need to record the date and time a record was inserted, but
will usually be querying by date, how about 2 fields?

CREATE TABLE foo (
<blah>
INSERT_DATE DATE,
INSERT_TIME TIME );

CREATE INDEX foo_insdt_idx ON foo (INSERT_DATE, INSERT_TIME);

Then, this will go pretty fast:
SELECT * FROM foo WHERE INSERT_DATE = '2003-09-11';

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

4 degrees from Vladimir Putin