Re: Immutable way to cast timestamp TEXT to DATE? (for index) - Mailing list pgsql-general
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: 150393Planning time: 0.401 msExecution 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 msExecution 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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-general by date: