Postgres Pro
Downloads
Home   >   mailing lists

Thread: date is not immutable ?

date is not immutable ?

From
"Gaetano Mendola"
Date:
Hi all,
I'm trying to create an index on a timestamptz field just extracting the
date:

# create index idx_user_logs_login_time_date on user_logs (
date(login_time) );
ERROR:  DefineIndex: index function must be marked IMMUTABLE


why date is not marked immutable ?


Regards
Gaetano

Re: date is not immutable ?

From
"Gaetano Mendola"
Date:
"Zachary Beane" <xach@xach.com> wrote in message
news:slrnb6sdcb.25l.xach@localhost.localdomain...
> In article <b4labj$15lq$1@news.hub.org>, Gaetano Mendola wrote:
> > Hi all,
> > I'm trying to create an index on a timestamptz field just extracting the
> > date:
> >
> > # create index idx_user_logs_login_time_date on user_logs (
> > date(login_time) );
> > ERROR:  DefineIndex: index function must be marked IMMUTABLE
> >
> >
> > why date is not marked immutable ?
>
> It is influenced by the local time zone.

Do you know another way to improve the select:

SELECT * FROM T WHERE date(login_time) = now()::date;


Regards
Gaetano

Re: date is not immutable ?

From
xach@xach.com (Zachary Beane)
Date:
In article <b4labj$15lq$1@news.hub.org>, Gaetano Mendola wrote:
> Hi all,
> I'm trying to create an index on a timestamptz field just extracting the
> date:
>
> # create index idx_user_logs_login_time_date on user_logs (
> date(login_time) );
> ERROR:  DefineIndex: index function must be marked IMMUTABLE
>
>
> why date is not marked immutable ?

It is influenced by the local time zone.

Zach

Re: date is not immutable ?

From
Tom Lane
Date:
"Gaetano Mendola" <mendola@bigfoot.com> writes:
> I'm trying to create an index on a timestamptz field just extracting the
> date:
> # create index idx_user_logs_login_time_date on user_logs (
> date(login_time) );
> ERROR:  DefineIndex: index function must be marked IMMUTABLE

> why date is not marked immutable ?

Because the conversion depends on your timezone setting.

            regards, tom lane