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

On 1/4/19 2:21 PM, Ken Tanzer wrote:

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

2014-10-23T00:00:00

I'd like to be able to create an index on the date portion of this field
(as a date), because I have lots of queries that are searching for
particular dates or ranges.

I've tried various ways of getting to a date (::date, LEFT(x,10)::date,
etc.), but all of them throw the error "functions in index expression
must be marked IMMUTABLE."

?:
'2014-10-23T00:00:00'::timestamp

Can you also show the actual index expression?

Is there any way, hacky or otherwise, people know of to do this?  Thanks
in advance.

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
(253) 245-3801

Subscribe to the mailing list
<mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, Jan 4, 2019 at 2:27 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 1/4/19 2:21 PM, Ken Tanzer wrote:

I've tried various ways of getting to a date (::date, LEFT(x,10)::date,
etc.), but all of them throw the error "functions in index expression
must be marked IMMUTABLE."

?:
'2014-10-23T00:00:00'::timestamp

Can you also show the actual index expression?

Here's a few of the ones I tried:

ag_reach=> CREATE INDEX ON export_hch_encounter_history
(CAST("Service_Date" AS date));
ERROR: functions in index expression must be marked IMMUTABLE
ag_reach=> CREATE INDEX ON export_hch_encounter_history
(CAST(LEFT("Service_Date",10) AS date));
ERROR: functions in index expression must be marked IMMUTABLE
ag_reach=> CREATE INDEX ON export_hch_encounter_history
(CAST("Service_Date"::timestamp AS date));
ERROR: functions in index expression must be marked IMMUTABLE
ag_reach=> CREATE INDEX ON export_hch_encounter_history
(("Service_Date"::date));
ERROR: functions in index expression must be marked
IMMUTABLEag_reach=> CREATE INDEX ON export_hch_encounter_history
((LEFT("Service_Date",10)::date));
ERROR: functions in index expression must be marked IMMUTABLE

--

AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

"Ken" == Ken Tanzer <ken.tanzer@gmail.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)

On Fri, Jan 4, 2019 at 2:54 PM Andrew Gierth <andrew@tao11.riddles.org.uk>
wrote:

"Ken" == Ken Tanzer <ken.tanzer@gmail.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?

I actually can't, or rather don't want to. The underlying data this is
drawn from is actually a date field, but this particular table keeps a
history of what we actually transmitted to another organization, and I want
to keep it as an exact replication of what we sent.

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;

Yeah, I thought I might have to do that, but when I create that index it
still doesn't seem to use the index for queries. I also found the
(immutable) make_date function, with the same problem. I can see why the
planner wouldn't know how to use them:

CREATE INDEX ON export_hch_encounter_history ( iso_date("Service_Date"));
CREATE INDEX ON export_hch_encounter_history (
make_date(LEFT("Service_Date",4)::int,SUBSTRING("Service_Date",6,2)::int,SUBSTRING("Service_Date",9,2)::int));

EXPLAIN ANALYZE SELECT * FROM export_hch_encounter_history WHERE
"Service_Date"::date BETWEEN '2018-01-01'::date AND
'2018-12-31'::date;

QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on export_hch_encounter_history (cost=0.00..19458.53
rows=885 width=656) (actual time=117.246..253.583 rows=26548 loops=1)
Filter: ((("Service_Date")::date >= '2018-01-01'::date) AND
(("Service_Date")::date <= '2018-12-31'::date))
Rows Removed by Filter: 150393
Planning time: 0.401 ms
Execution time: 284.036 ms
(5 rows)

As opposed to casting the WHERE clause parameters:

EXPLAIN ANALYZE SELECT * FROM export_hch_encounter_history WHERE
"Service_Date" BETWEEN '2018-01-01'::text AND '2018-12-31'::text;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------
Bitmap Heap Scan on export_hch_encounter_history
(cost=799.91..16639.67 rows=26487 width=656) (actual
time=15.611..51.309 rows=26548 loops=1)
Recheck Cond: (("Service_Date" >= '2018-01-01'::text) AND
("Service_Date" <= '2018-12-31'::text))
Heap Blocks: exact=2432
-> Bitmap Index Scan on
"export_hch_encounter_history_Service_Date_idx" (cost=0.00..793.29
rows=26487 width=0) (actual time=15.250..15.252 rows=26
548 loops=1)
Index Cond: (("Service_Date" >= '2018-01-01'::text) AND
("Service_Date" <= '2018-12-31'::text))
Planning time: 0.739 ms
Execution time: 80.523 ms
(7 rows)

Thanks for your help and response!

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

From: Ken Tanzer <ken.tanzer@gmail.com>

On Fri, Jan 4, 2019 at 2:54 PM Andrew Gierth <andrew@tao11.riddles.org.uk<mailto:andrew@tao11.riddles.org.uk>> wrote:

"Ken" == Ken Tanzer <ken.tanzer@gmail.com<mailto:ken.tanzer@gmail.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?

I actually can't, or rather don't want to. The underlying data this is drawn from is actually a date field, but this particular table keeps a history of what we actually transmitted to another organization, and I want to keep it as an exact replication of what we sent.

If it’s not too painful, add another column to your table of type DATE, and on INSERT shove your “timestamp” into that, converting/casting as needed, then index that. So at the cost of an extra 4 bytes per row, you can have both your “transmission” value and an indexable value.

Kevin
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.

"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)