| Lists: | Postg무지개 토토SQL |
|---|
| From: | "Patrick Hatcher" <PHatcher(at)macys(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Index on timestamp to date field |
| Date: | 2003-10-13 20:49:07 |
| Message-ID: | OFF4DD9C81.1DB45A63-ON88256DBE.00713BDB-88256DBE.00730621@fds.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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
| From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
|---|---|
| To: | Patrick Hatcher <PHatcher(at)macys(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Index on timestamp to date field |
| Date: | 2003-10-14 02:10:30 |
| Message-ID: | 20031014021030.GA32083@wolff.to |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | Postg무지개 토토SQL |
On Mon, Oct 13, 2003 at 13:49:07 -0700,
Patrick Hatcher <PHatcher(at)macys(dot)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.
| From: | Jeff <threshar(at)torgo(dot)978(dot)org> |
|---|---|
| To: | Patrick Hatcher <PHatcher(at)macys(dot)com> |
| Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Index on timestamp to date field |
| Date: | 2003-10-14 12:53:36 |
| Message-ID: | Pine.BSF.4.44.0310140851410.1994-100000@torgo.978.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
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(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/