From: Bruno Wolff III <bruno@wolff.to>
To: D. Duccini <duccini@backpack.com>
Cc: pgsql-sql@postgresql.org, Pgsql-novice <pgsql-novice@postgresql.org>
Subject: Re: date_trunc'd timestamp index possible?
Date: Fri, 1 Oct 2004 13:28:30 -0500
Message-ID: <20041001182830.GA20624@wolff.to> (raw)
In-Reply-To: <Pine.GSO.4.10.10409271910480.22691-100000@solarwind>
References: <web-1583353@davinci.ethosmedia.com>
<Pine.GSO.4.10.10409271910480.22691-100000@solarwind>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, duccini@backpack.com, pgsql-novice@postgresql.org
Subject: Re: date_trunc'd timestamp index possible?
In-Reply-To: <20041001182830.GA20624@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox