Re: [GENERAL] selects on datetime

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(at)fine(dot)com>
To: 'pgsql-general(at)postgresql(dot)org' <pgsql-general(at)postgreSQL(dot)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.