Thread: date_trunc on date is immutable?

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.

I thought dates were immutable, and didn't think that DATE_TRUNC did anything to change that.  These all fail:
  create index enrollments_app_recvd_month_idx on enrollments ( date_trunc('month', appl_recvd_date) );
  create index enrollments_app_recvd_month_idx on enrollments ( (date_trunc('month', appl_recvd_date) at time zone 'pst') );
  create index enrollments_app_recvd_month_idx on enrollments ( to_char(appl_recvd_date, 'YYYYMM') );
  create index enrollments_app_recvd_month_idx on enrollments ( (to_char(extract(year from appl_recvd_date), '0000') || to_char(extract( month from appl_recvd_date), '00')) );

After much experimentation, I finally was able to get this to work:
  create index enrollments_app_recvd_month_idx on enrollments ( (cast(extract(year from appl_recvd_date) as text) || cast(extract(month from appl_recvd_date) as text)) );

I am guessing to_char is mutable because the format string could use a locale specific character, and PG doesn't bother to check the format string when determining whether a function call is immutable. But I'm lost on why date_trunc is mutable, especially after applying a specific time zone. Am I missing something here?

On Thu, Dec 24, 2009 at 4:36 PM, Kian Wright
<kian.wright@senioreducators.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.

On Fri, Dec 25, 2009 at 12:56 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Dec 24, 2009 at 4:36 PM, Kian Wright
> <kian.wright@senioreducators.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.

--
greg

On Thu, Dec 24, 2009 at 6:47 PM, Greg Stark <gsstark@mit.edu> wrote:
> On Fri, Dec 25, 2009 at 12:56 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Thu, Dec 24, 2009 at 4:36 PM, Kian Wright
>> <kian.wright@senioreducators.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?

On Fri, Dec 25, 2009 at 1:58 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Isn't it the client timezone and not the system timezone that actually
> sets the tz the tstz is set to on retrieval?

It's the GUC:

stark=> set timezone = 'America/Los_Angeles';
SET
stark=> select now();
              now
-------------------------------
 2009-12-25 06:44:33.238187-08
(1 row)


I'm not sure if and how we initialize the GUC on a new connection
though. It might vary depending on the driver you use too.


--
greg

Greg Stark <gsstark@mit.edu> writes:
> On Fri, Dec 25, 2009 at 12:56 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Thu, Dec 24, 2009 at 4:36 PM, Kian Wright
>> <kian.wright@senioreducators.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