pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (2+ messages)  latest in thread

Message-ID: <3A612100.D68ABDE8@merz-akademie.de>
Permalink:  ../3A612100.D68ABDE8@merz-akademie.de/
Also on:    postgresql.org/message-id/3A612100.D68ABDE8@merz-akademie.de

 · 

reply

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