Re: [HACKERS] TODO item: immutable date_trunc with timezone arg

2014-07-01 Thread Noah Misch
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] TODO item: immutable date_trunc with timezone arg

2014-07-01 Thread Craig Ringer
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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] TODO item: immutable date_trunc with timezone arg

2014-06-30 Thread Craig Ringer
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.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers