 | |
Re: date_trunc'd timestamp index possible?
comp.databases.postgresql.novice comp.databases.postgresql.novice
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: date_trunc'd timestamp index possible? -
10-01-2004
, 01:28 PM
|
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???) |
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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)
#2
| |||
| |||
|
|
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. |
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!"
-----------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly
#3
| |||
| |||
|
|
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. |
instead of immutable. I think at least a couple of these should be
marked as immutable and I will try reporting this as a bug.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly
#4
| |||
| |||
|
|
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. |
regression=# select provolatile from pg_proc where oid = 'timezone(text,timestamptz)'::regprocedure;
provolatile
-------------
s
(1 row)
regression=#
This is a thinko that's already been corrected for 8.0:
regression=# select provolatile from pg_proc where oid = 'timezone(text,timestamptz)'::regprocedure;
provolatile
-------------
i
(1 row)
regression=#
If you wanted you could just UPDATE pg_proc to correct this mistake.
Another possibility is to create a function that's an IMMUTABLE
wrapper around the standard function.
Looking at this, I realize that date_trunc() is mismarked: the
timestamptz variant is strongly dependent on the timezone setting
and so should be STABLE not IMMUTABLE. Ooops.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
#5
| |||
| |||
|
|
I think we found a way around it! CREATE OR REPLACE FUNCTION date_immutable( timestamptz ) RETURNS date AS 'SELECT date( $1 ) ;' LANGUAGE 'sql' IMMUTABLE ; |
date(timestamptz) is immutable does not make it so. The above
*will* break the first time someone uses the table with a different
timezone setting.
What you can do safely is date(footime AT TIME ZONE 'something'),
since this nails down the zone in which the date is interpreted.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.