Re: [8.1.4] Create index on timestamp fails - Mailing list pgsql-general

On Aug 22, 2006, at 5:35 PM, Tom Lane wrote:

> "Arturo Perez" <aperez@hayesinc.com> writes:
>> I have a table with an column:
>>     entry_date | timestamp with time zone| not null
>
>> And when I try to create an index on it like so:
>>     create index entry_date_idx on =
>> user_tracking(date_part('year',entry_date));
>
>> I get a
>>     ERROR: functions in index expression must be marked IMMUTABLE
>
>> According to the mailing lists, this has been working since 7.4.
>
> I seriously doubt that.  date_part on a timestamptz is stable, not
> immutable, and AFAICT has been marked that way since 7.3.  The problem
> is that the results depend on your current TimeZone setting --- for
> instance, 2AM 2006-01-01 in London is 9PM 2005-12-31 where I live.
>
> If you only need day precision, try storing entry_date as a date
> instead
> of a timestamptz.  Or perhaps consider timestamp without tz.  But you
> need something that's not timezone-dependent to make this work.
>
>             regards, tom lane


Ah, I knew it was something I was overlooking.  Thanks a ton.  We need
sub-day granularity (it's for a sort of weblog).  Without a TZ sounds
llke
a winner.

Thanks again,
arturo