Re: TODO item: immutable date_trunc with timezone arg - Mailing list pgsql-hackers

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