Re: Date indexing

"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