X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AD2B232A21E for ; Fri, 1 Oct 2004 19:18:50 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26458-04 for ; Fri, 1 Oct 2004 18:18:32 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id C9DC532A21B for ; Fri, 1 Oct 2004 19:18:31 +0100 (BST) Received: (qmail 21351 invoked by uid 500); 1 Oct 2004 18:28:30 -0000 Date: Fri, 1 Oct 2004 13:28:30 -0500 From: Bruno Wolff III To: "D. Duccini" Cc: pgsql-sql@postgresql.org, Pgsql-novice Subject: Re: date_trunc'd timestamp index possible? Message-ID: <20041001182830.GA20624@wolff.to> Mail-Followup-To: "D. Duccini" , pgsql-sql@postgresql.org, Pgsql-novice References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/8 X-Sequence-Number: 11093 On Mon, Sep 27, 2004 at 19:14:09 -0500, "D. Duccini" 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.