Re: [HACKERS] 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 -- 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
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
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