469,271 Members | 1,731 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
3 8598
On Mon, Oct 13, 2003 at 13:49:07 -0700,
Patrick Hatcher <PH******@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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #2
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 <je**@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #3
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 <je**@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Noyb | last post: by
2 posts views Thread by Aad Aldus | last post: by
4 posts views Thread by peashoe | last post: by
2 posts views Thread by Jerry | last post: by
2 posts views Thread by hph | last post: by
1 post views Thread by Graham Feeley | last post: by
10 posts views Thread by satishrajana | last post: by
7 posts views Thread by sheri | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.