Re: TODO item: immutable date_trunc with timezone arg

On Tue, Jul 01, 2014 at 11:49:43AM +0800, Craig Ringer wrote:
> Currently it's unsafe/not possible to use some funtions, like
> date_trunc, in immutable functions and expression indexes.
>
> It'd be really useful to have an immutable version that took the
> timezone as an argument. Maybe this is a worthwhile beginner TODO item
> for the wiki?
>
> I'm sure there are other funcs that could use TimeZone-insensitive
> variants too.

You can achieve it today with the AT TIME ZONE operator:

CREATE INDEX ON t ((date_trunc('week', col_name AT TIME ZONE 'Asia/Macau')));

If "version of date_part having a timezone argument" is the desired interface,
one can wrap that idiom in a one-line SQL function. I doubt adding such a
wrapper to core is worth the weight.

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com