Re: Most efficient way of selecting by date?

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?

Thanks,

Simon

On Tue, Sep 09, 2003 at 21:30:31 +0100,
Simon Willison <cs1spw(at)bath(dot)ac(dot)uk> wrote:
>
> 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?

Probably by using a range. Something like:
select whatever from wherever
where added < '2003-01-03' and added >= '2003-01-02';

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

Hi,

I'd like to know 2 things that should be added to the FAQ:

How many tables can you create in a database?

How many databases can you create on a single PostgreSQL server?

A potential customer asked me this and I didn't know the answers.

Thanks,

Marc

On Fri, 2003-09-12 at 11:05, M. Bastin wrote:
> Hi,
>
> I'd like to know 2 things that should be added to the FAQ:
>
> How many tables can you create in a database?
>
> How many databases can you create on a single PostgreSQL server?

I think that to both questions the answer is that there is no practical
limit within the database. A table takes up some rows in system tables,
and it creates files on disk for its data and its indexes. A database
creates a sub-directory containing its tables. Since tables are able to
hold many millions of rows, I cannot imagine there could be any
practical limit there.

You will run out of inodes or disk space long before you run out of
database resources.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Let the wicked forsake his way, and the unrighteous
man his thoughts; and let him return unto the LORD,
and He will have mercy upon him; and to our God, for
he will abundantly pardon." Isaiah 55:7