Re: Immutable way to cast timestamp TEXT to DATE? (for index)

>>>>> "Ken" == Ken Tanzer <ken.tanzer@gmail.com> writes:

 >> If you absolutely can't change the column type, then one option
 >> would be to do your own fixed-format date parsing function (and
 >> label it immutable), e.g.
 >> 
 >> create function iso_timestamp(text)
 >> returns timestamp without time zone
 >> as $$ select case when $1 ~ '^\d\d\d\d-?\d\d-?\d\dT\d\d:?\d\d:?\d\d$'
 >> then $1::timestamp
 >> else null end $$
 >> set DateStyle = 'ISO,YMD'
 >> language sql immutable strict;
 >> 
 >> or
 >> 
 >> create function iso_date(text)
 >> returns date
 >> as $$ select case when $1 ~ '^\d\d\d\d-?\d\d-?\d\d(?![^T])'
 >> then substring($1 from '^\d\d\d\d-?\d\d-?\d\d')::date
 >> else null end $$
 >> set DateStyle = 'ISO,YMD'
 >> language sql immutable strict;

 Ken> Yeah, I thought I might have to do that, but when I create that
 Ken> index it still doesn't seem to use the index for queries.

It won't use the index unless you use the same function in the query
too.

i.e.

CREATE INDEX ON ... (iso_date("Service_Date"));

SELECT * FROM ... WHERE iso_date("Service_Date") BETWEEN ... AND ...;

-- 
Andrew (irc:RhodiumToad)