On Mon, 9 Oct 2006, Tom Lane wrote: > It's not clear to me why we have width_bucket operating on numeric and > not float8 --- that seems like an oversight, if not outright > misunderstanding of the type hierarchy.
Would that make the below a lot faster? > But if we had the float8 > version, I think Jeremy's problem would be solved just by applying > the float8 version to "extract(epoch from timestamp)". I don't really > see the use-case for putting N versions of the function in there. I found the function I used before I implemented the C version. It was significantly slower, which is why I wrote the C version. -- given a date range and a number of buckets, round the given date to one -- of the buckets such that any number of dates within the date range passed -- in to this function will only return up to the number of buckets unique -- values CREATE OR REPLACE FUNCTION date_width_bucket (tm TIMESTAMP WITHOUT TIME ZONE, low TIMESTAMP WITHOUT TIME ZONE, high TIMESTAMP WITHOUT TIME ZONE, nbuckets INTEGER ) RETURNS TIMESTAMP WITHOUT TIME ZONE AS $$ SELECT ((EXTRACT(epoch FROM $3) - EXTRACT(epoch FROM $2)) / $4) * (width_bucket(EXTRACT(epoch FROM $1)::NUMERIC, EXTRACT(epoch FROM $2)::NUMERIC, EXTRACT(epoch FROM $3)::NUMERIC, $4) - 1) * '1 second'::INTERVAL + $2; $$ LANGUAGE sql IMMUTABLE STRICT; -- I don't think they could put him in a mental hospital. On the other hand, if he were already in, I don't think they'd let him out. ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match