From: | "D(dot) Duccini" <duccini(at)backpack(dot)com> |
---|---|
To: | Bruno Wolff III <bruno(at)wolff(dot)to> |
Cc: | pgsql-sql(at)postgresql(dot)org, Pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: date_trunc'd timestamp index possible? |
Date: | 2004-10-01 18:28:47 |
Message-ID: | Pine.GSO.4.10.10410011327580.26562-100000@solarwind |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-novice pgsql-sql |
> 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(at)backpack(dot)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!"
-----------------------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-10-01 18:33:47 | Re: Vacuum writes on empty system |
Previous Message | Bruno Wolff III | 2004-10-01 18:28:30 | Re: date_trunc'd timestamp index possible? |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2004-10-01 18:44:37 | Re: date_trunc'd timestamp index possible? |
Previous Message | Bruno Wolff III | 2004-10-01 18:28:30 | Re: date_trunc'd timestamp index possible? |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2004-10-01 18:44:37 | Re: date_trunc'd timestamp index possible? |
Previous Message | Bruno Wolff III | 2004-10-01 18:28:30 | Re: date_trunc'd timestamp index possible? |