Using INDEX on date/time values // Re: how to select a time frame on timestamp rows.

Hi,

Peter Eisentraut schrieb:
> 
> >       today i was trying to perform a query on a database using a time stamp
> > field, i need to get all records which belong to year 2000, month 11,
[...]
> select user_name from tbacct where extract(month from acct_timestamp) = 11 ...

is there any way of using an index for selecting some rows, e.g.
selecting all data from one month?

Explain sais, that the isn't used!

 blaster=# explain select id from forum_data where extract(month from
date) = 1; NOTICE:  QUERY PLAN:  Seq Scan on forum_data  (cost=0.00..59.74 rows=3 width=4)



also, I didn't found documentation about "extract" in the PG docs. ups!
;) wher is it, any hints?



In the mailing list archives I found the following hint:
 select * from t1   where d >= (date_trunc('month', timestamp 'today')       - interval '1 month')     and d <
date_trunc('month',timestamp 'today');
 


it also dosn't use index according to explain ...

My test table has ~350 rows.

Is it possible to use indexes?

or is it better to use an indexed int-field with unixtime? (int8!)

Thanks & Ciao Alvar


-- 
Alvar C.H. Freude  |  alvar.freude@merz-akademie.de
   Demo: http://www.online-demonstration.org/  |  Mach mit!
Blast-DE: http://www.assoziations-blaster.de/   |  Blast-Dich-Fit
Blast-EN: http://www.a-blast.org/               |  Blast/english