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(dot)freude(at)merz-akademie(dot)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