Re: width_bucket function for timestamps

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.