Skip site navigation (1) Skip section navigation (2)

Re: date_trunc on date is immutable?

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 (view raw or flat)
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?

In response to

Responses

pgsql-general by date

Next:From: donniehanDate: 2009-12-25 08:58:45
Subject: Why grantor is owner in this case?
Previous:From: Greg StarkDate: 2009-12-25 01:47:04
Subject: Re: date_trunc on date is immutable?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group