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?
In response to
pgsql-general by date
|Next:||From: donniehan||Date: 2009-12-25 08:58:45|
|Subject: Why grantor is owner in this case?|
|Previous:||From: Greg Stark||Date: 2009-12-25 01:47:04|
|Subject: Re: date_trunc on date is immutable?|