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?
Date: Fri, 1 Oct 2004 13:28:47 -0500 (CDT)
Message-ID: <Pine.GSO.4.10.10410011327580.26562-100000@solarwind> (raw)
In-Reply-To: <20041001182830.GA20624@wolff.to>
> 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!"
-----------------------------------------------------------------------------
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: duccini@backpack.com, bruno@wolff.to, pgsql-novice@postgresql.org
Subject: Re: date_trunc'd timestamp index possible?
In-Reply-To: <Pine.GSO.4.10.10410011327580.26562-100000@solarwind>
* 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