Re: date_trunc on date is immutable?

On Fri, Dec 25, 2009 at 12:56 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Thu, Dec 24, 2009 at 4:36 PM, Kian Wright
> <kian(dot)wright(at)senioreducators(dot)com> wrote:
>> I'm trying to create an index on the month and year of a date field (in
>> 8.3), and I'm getting the "functions in index expression must be marked
>> IMMUTABLE" error message.
>
> If applied to a timestamp, it is immutable.  If it's a timestamp with
> timezone it's not, because the timezone can change, which would change
> the index.

Put another way, a given point in time doesn't necessarily lie in a
particular month or on a particular day because it depends what time
zone the system is set to. So right now it's a day earlier or later in
part of the globe.

To do what you want define the index on date_trunc('month',
appl_recvd_date at time zone 'America/Los_Angeles') or something like that.

You'll have to make sure your queries have the same expression in them
though :( It won't work if you just happen to have the system time
zone set to the matching time zone.

--
greg