From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | Kian Wright <kian(dot)wright(at)senioreducators(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: date_trunc on date is immutable? |
Date: | 2009-12-25 01:58:38 |
Message-ID: | dcc563d10912241758m61faa0a4gdc3a2647526f1921@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | donniehan | 2009-12-25 08:58:45 | Why grantor is owner in this case? |
Previous Message | Greg Stark | 2009-12-25 01:47:04 | Re: date_trunc on date is immutable? |