Re: TODO item: immutable date_trunc with timezone arg

On 07/02/2014 11:10 AM, Noah Misch wrote:
> 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.

I think it'd be a usability boost, but given the example above, probably
not worth the effort.

Thanks for pointing that out - useful one for the archives.

-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services