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

>>>>> "Ken" == Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> writes:

Ken> Hi. I've got a text field in a table that holds this style of
Ken> timestamp:

Ken> 2014-10-23T00:00:00

You can't make this a field of type "timestamp" rather than text?

Casts from text to either date or timestamp are mutable because they
depend on the current DateStyle value (_you_ might know that your values
are always ISO format, but the code doesn't). You can't even avoid this
with to_date or to_timestamp and a fixed format, because those functions
are also mutable since some of the format options are locale-dependent
(again, you might know that you're not using those, but the code
doesn't).

If the column type were timestamp, though, then you could do comparisons
directly, or cast the value to "date" (which is an immutable cast).

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;

--
Andrew (irc:RhodiumToad)