Thread: Index on timestamp to date field

I have a timestamp field where I find I'm doing a lot of searching by date
(YYYY-MM-DD) or using this field as a match to another table that has a
date format.  I wanted to create an index on the  timestamp field using a
date format.  Is this possible?  I tried:
 CREATE INDEX test_2 ON table1 USING btree to_char(field2, 'MM-DD-YYYY');
but I get an error message error at or new to_char

TIA

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-422-1610 office
HatcherPT - AIM

On Mon, Oct 13, 2003 at 13:49:07 -0700,
  Patrick Hatcher <PHatcher@macys.com> wrote:
> I have a timestamp field where I find I'm doing a lot of searching by date
> (YYYY-MM-DD) or using this field as a match to another table that has a
> date format.  I wanted to create an index on the  timestamp field using a
> date format.  Is this possible?  I tried:
>  CREATE INDEX test_2 ON table1 USING btree to_char(field2, 'MM-DD-YYYY');
> but I get an error message error at or new to_char

You can't create general functional indexes until 7.4.
However you might be able to use an index on the timestamp if you cast
the character strings to a timestamp value.

On Mon, 13 Oct 2003, Patrick Hatcher wrote:

> I have a timestamp field where I find I'm doing a lot of searching by date
> (YYYY-MM-DD) or using this field as a match to another table that has a
> date format.  I wanted to create an index on the  timestamp field using a
> date format.  Is this possible?  I tried:
>  CREATE INDEX test_2 ON table1 USING btree to_char(field2, 'MM-DD-YYYY');
> but I get an error message error at or new to_char
>
> TIA
>

So you don't want a oh. lets call it a "real index" on the timestamp
field?

create index ts_index on mytable(mytimestamp);

Or can you not change the way the dates are input?

Or is the "mytimestamp" field a varchar containing a date string?

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/