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

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

> >>>>> "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?
>
>
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/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

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