Re: date_trunc on date is immutable?

On Thu, Dec 24, 2009 at 6:47 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:
> 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.

Isn't it the client timezone and not the system timezone that actually
sets the tz the tstz is set to on retrieval?