From: Alvar Freude <alvar.freude@merz-akademie.de>
To: Postgres SQL list <pgsql-sql@postgresql.org>
Subject: Using INDEX on date/time values // Re: how to select a time frame on timestamp rows.
Date: Sun, 14 Jan 2001 04:46:08 +0100
Message-ID: <3A612100.D68ABDE8@merz-akademie.de> (raw)
References: <Pine.LNX.4.30.0101140208531.965-100000@peter.localdomain>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: alvar.freude@merz-akademie.de
Subject: Re: Using INDEX on date/time values // Re: how to select a time frame on timestamp rows.
In-Reply-To: <3A612100.D68ABDE8@merz-akademie.de>
References: <Pine.LNX.4.30.0101140208531.965-100000@peter.localdomain> <3A612100.D68ABDE8@merz-akademie.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox