Unexpected casts while using date_trunc()

From: Chris Bandy <bandy(dot)chris(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Unexpected casts while using date_trunc()
Date: 2018-05-24 15:14:06
Message-ID: cb08676b-9a02-00da-2a1d-6ab262792004@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have an application generating the following query on a DATE column in
PostgreSQL 10.1:

> SELECT TO_CHAR(DATE_TRUNC('month', jobs.active_until), 'YYYY-MM')
> FROM jobs
> GROUP BY DATE_TRUNC('month', jobs.active_until)
> LIMIT 500

I wanted to support it with an expression index, but was surprised to
find that the DATE_TRUNC call was not immutable:

> CREATE INDEX ON jobs (DATE_TRUNC('month', active_until));
> ERROR: functions in index expression must be marked IMMUTABLE

The documentation explains that DATE is first cast to TIMESTAMP. (As I
understand it, this is an immutable cast; sounds find and appropriate.)

https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

But in my testing, the date value is actually cast to TIMESTAMPTZ:

> SELECT pg_typeof(DATE_TRUNC('month', active_until))
> FROM jobs LIMIT 1;
> pg_typeof
> --------------------------
> timestamp with time zone
> (1 row)

Indeed, casting to TIMESTAMP first allows me to create the index:

> CREATE INDEX ON jobs (DATE_TRUNC('month', active_until::timestamp));

However, this index doesn't help me because the query is generated by an
application outside of my control.

1. It seems to me it is worth mentioning in the docs that DATE_TRUNC
accepts and returns TIMESTAMPTZ. N.B. There's no mention of it in the
table of functions:

https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-TABLE

2. Since this implicit cast to TIMESTAMPTZ is used rather than
TIMESTAMP, it may be worthwhile to add an explicit implementation that
accepts DATE.

(Thanks to Andrew Gierth for explaining the mechanics on IRC.)

-- Chris

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Martin Mueller 2018-05-24 15:15:59 computing z-scores
Previous Message Tom Lane 2018-05-24 14:41:38 Re: PG11 jit failing on ppc64el