From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ian Cass <ian.cass@mblox.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Date indexing
Date: Tue, 23 Apr 2002 09:56:38 -0400
Message-ID: <11066.1019570198@sss.pgh.pa.us> (raw)
In-Reply-To: <005501c1eaaa$46eb2500$6602a8c0@salamander>
References: <005501c1eaaa$46eb2500$6602a8c0@salamander>
"Ian Cass" <ian.cass@mblox.com> writes:
> I'll only ever be referencing this data to a granularity of 1 day, so is
> there a way I can use a function to index this so that the date column in
> the index is text containing just DD/MM/YYYY?
Don't use text, use a date.
regression=# create table foo (f1 timestamp without time zone);
CREATE
regression=# create index fooi on foo(date(f1));
CREATE
regression=# explain select * from foo where date(f1) = current_date;
QUERY PLAN
-----------------------------------------------------------------
Index Scan using fooi on foo (cost=0.00..17.09 rows=5 width=8)
Index Cond: (date(f1) = date('now'::text))
(2 rows)
(This is with current devel sources, as you can possibly tell from the
EXPLAIN format, but should work fine in 7.2; less sure about 7.1.)
Note that a coercion from timestamp *with* timezone to date will
not be allowed as an index function, because it's dependent on
external information --- viz, your timezone. So I had to use
timestamp without time zone in this example.
One might also wonder why you're not just storing the column as type
date rather than timestamp in the first place, if you're certain you
do not need finer resolution.
regards, tom lane
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, ian.cass@mblox.com
Subject: Re: Date indexing
In-Reply-To: <11066.1019570198@sss.pgh.pa.us>
* 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