Skip site navigation (1) Skip section navigation (2)

Re: BUG #5852: Function date_trunc is not IMMUTABLE

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Oleg" <serovov(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5852: Function date_trunc is not IMMUTABLE
Date: 2011-01-27 14:40:04
Message-ID: 21812.1296139204@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Oleg" <serovov(at)gmail(dot)com> writes:
> Please mark function date_trunc as IMMUTABLE for using in index.

The version for timestamp without time zone already is marked immutable.
The version for timestamp with time zone can't be marked immutable,
because it isn't: its behavior depends on the timezone setting.

Depending on what it is you hope to accomplish, you might be able to do
something like this:

regression=# create table t1 (f1 timestamptz);
CREATE TABLE
regression=# create index ii on t1 (date_trunc('day', f1 AT TIME ZONE 'UTC'));
CREATE INDEX

This is immutable because the truncation happens with respect to a fixed
timezone.  (Doesn't have to be UTC, any constant timezone name will do.)

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Xiaobo GuDate: 2011-01-27 14:55:12
Subject: Re: Is there a way to build PostgreSQL client libraries with MinGW
Previous:From: Andrew DunstanDate: 2011-01-27 14:36:31
Subject: Re: Is there a way to build PostgreSQL client libraries with MinGW

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group