pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Using INDEX on date/time values  // Re: how to select a time frame on timestamp rows.
2+ messages / 2 participants
[nested] [flat]

* Using INDEX on date/time values  // Re: how to select a time frame on timestamp rows.
@ 2001-01-14 03:46  Alvar Freude <alvar.freude@merz-akademie.de>
  0 siblings, 1 reply; 2+ messages in thread

From: Alvar Freude @ 2001-01-14 03:46 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Using INDEX on date/time values // Re: how to select a time frame on timestamp rows.
@ 2001-01-15 01:51  Mike Castle <dalgoda@ix.netcom.com>
  parent: Alvar Freude <alvar.freude@merz-akademie.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Mike Castle @ 2001-01-15 01:51 UTC (permalink / raw)
  To: pgsql-sql

On Sun, Jan 14, 2001 at 04:46:08AM +0100, Alvar Freude wrote:
> 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?


What about select blah from foo where month >= 2000-11-01 and month < 2000-12-01

Fix up as appropriate.

mrc
-- 
       Mike Castle       Life is like a clock:  You can work constantly
  dalgoda@ix.netcom.com  and be right all the time, or not work at all
www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc
    We are all of us living in the shadow of Manhattan.  -- Watchmen



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-01-15 01:51 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-14 03:46 Using INDEX on date/time values  // Re: how to select a time frame on timestamp rows. Alvar Freude <alvar.freude@merz-akademie.de>
2001-01-15 01:51 ` Re: Using INDEX on date/time values // Re: how to select a time frame on timestamp rows. Mike Castle <dalgoda@ix.netcom.com>

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