Received: from student.merz-akademie.de (73.ptr.merz-akademie.de [195.226.105.73] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0E3epp98885 for ; Sat, 13 Jan 2001 22:40:51 -0500 (EST) (envelope-from alvar.freude@merz-akademie.de) Received: from merz-akademie.de (news.merz-akademie.de [195.226.105.70]) by student.merz-akademie.de (8.9.3/8.9.3) with ESMTP id EAA30155 for ; Sun, 14 Jan 2001 04:40:46 +0100 Message-ID: <3A612100.D68ABDE8@merz-akademie.de> Date: Sun, 14 Jan 2001 04:46:08 +0100 From: Alvar Freude Organization: http://a-blast.org/ X-Mailer: Mozilla 4.7 [de] (WinNT; I) X-Accept-Language: de,en MIME-Version: 1.0 To: Postgres SQL list Subject: Using INDEX on date/time values // Re: how to select a time frame on timestamp rows. References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/130 X-Sequence-Number: 791 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