On Mon, Sep 27, 2004 at 19:14:09 -0500,
"D. Duccini" <duccini@backpack.com> wrote:
>
> I'm trying to create a index from a timestamp+tz field and want the index
> to be date_trunc'd down to just the date
>
> when i try to do a
>
> create idxfoo on foo (date(footime));
>
> i get a
>
> ERROR: DefineIndex: index function must be marked IMMUTABLE
>
> and it chokes on when i try to use the date_trunc() function as well
>
> create idxfoo on foo (date_trunc('day',footime));
>
> ERROR: parser: parse error at or near "'day'" at character 53
>
> Any suggestions/workarounds (other than creating additional date-only
> columns in the schema and indexing those???)
The reason this doesn't work is that the timestamp to date conversion
depends on the time zone setting. In theory you should be able to avoid
this by specifying the time zone to check the date in. I tried something
like the following which I think should work, but doesn't:
create idxfoo on foo (date(timezone('UTC',footime)));
The conversion of the timestamp stored in footime should be immutable
and then taking the date should work. I did find that date of a timestamp
without time zone is treated as immutable.
I am not sure how to check if the supplied function for converting
a timestamp with time zone to a timestamp without timezone using a
specified time zone is immutable. I think this function should be
immutable, but that it probably isn't.