Re: date_trunc on date is immutable?

Greg Stark <gsstark(at)mit(dot)edu> writes:
> 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.

Given the way the question was phrased, I think the real situation is
that the OP has a column of type date. There isn't any date_trunc()
function on date; there are such functions for timestamp with and
without timezone. The parser prefers the former because with-tz is a
preferred type, so what he's really got is
date_trunc('month', datecol::timestamptz)
which is doubly not immutable: both the cast and the trunc function
are timezone-sensitive.

So one possible answer is to make sure the cast is to without-tz:
date_trunc('month', datecol::timestamp)
which in fact is indexable. However:

> 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.

This point is still a problem, because he'd need the same explicit
cast in the queries he wants to use the index. It might be worth making
a special-purpose function monthof(date) or something like that to
reduce the notational burden.

(More generally, I wonder if it is worth creating a built-in date_trunc
for date input, just to avoid this gotcha. At least in this context,
it seems like date->timestamp ought to be a preferable promotion over
date->timestamptz.)

regards, tom lane