pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing 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>
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>
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-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 ` Mike Castle <dalgoda@ix.netcom.com>
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