Date: Fri, 1 Oct 2004 13:28:47 -0500 (CDT)
From: "D. Duccini" <duccini@backpack.com>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org,
	Pgsql-novice <pgsql-novice@postgresql.org>
Subject: Re: date_trunc'd timestamp index possible?
In-Reply-To: <20041001182830.GA20624@wolff.to>
Message-ID: <Pine.GSO.4.10.10410011327580.26562-100000@solarwind>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


> 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.

I think we found a way around it!


CREATE OR REPLACE FUNCTION date_immutable( timestamptz ) RETURNS date AS
'SELECT date( $1 ) ;' LANGUAGE 'sql' IMMUTABLE ;

CREATE INDEX "new_event_dt" ON "the_events" USING btree (
date_immutable( "event_dt_tm" ) ) ;


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------