Thread: Re: [GENERAL] selects on datetime

Re: [GENERAL] selects on datetime

From
"K.T."
Date:
Ugg more HTMLized email...
 
If your fields are defined separately as Year, Month, Day, then index on those fields and your query should use the index to find the records matching where clause.
 
If you want a single date field...compose the date field as a string and try the following for a where clause:
    date = '1999-02-01'::date
or if it is datetime
    date = '1999-02-01'::datetime
 
Of course have an index on the date field...
 
-----Original Message-----
From: Scot Brady <ScotB@fine.com>
To: 'pgsql-general@postgresql.org' <pgsql-general@postgreSQL.org>
Date: Tuesday, March 02, 1999 8:47 PM
Subject: [GENERAL] selects on datetime

Hello all,

I have a table w/ a field of type datetime. I would like to do something like:
select count(jobs) for every day of a particular month and year.

my current select statement is:
Select count(job_id) from jobs where date_part('month',sub_dt)=2 and date_part('year',sub_dt)=1999 and date_
part('day',sub_dt)=?";

I then execute the statement filling the placeholder with days 1..31

There has to be a better way to do this since the select has to go through every row in the table. Any ideas?

thanks,
scot b.