| Lists: | pgsql-hackers |
|---|
| From: | Craig Ringer <craig(at)2ndquadrant(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | TODO item: immutable date_trunc with timezone arg |
| Date: | 2014-07-01 03:49:43 |
| Message-ID: | 53B22FD7.4040408@2ndquadrant.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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
| From: | Noah Misch <noah(at)leadboat(dot)com> |
|---|---|
| To: | Craig Ringer <craig(at)2ndquadrant(dot)com> |
| Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: TODO item: immutable date_trunc with timezone arg |
| Date: | 2014-07-02 03:10:16 |
| Message-ID: | 20140702031016.GA1597174@tornado.leadboat.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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
| From: | Craig Ringer <craig(at)2ndquadrant(dot)com> |
|---|---|
| To: | Noah Misch <noah(at)leadboat(dot)com> |
| Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: TODO item: immutable date_trunc with timezone arg |
| Date: | 2014-07-02 03:21:30 |
| Message-ID: | 53B37ABA.2070506@2ndquadrant.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-hackers |
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