From: | Jeremy Drake <pgsql(at)jdrake(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Jim C(dot) Nasby" <jim(at)nasby(dot)net>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: width_bucket function for timestamps |
Date: | 2006-10-09 21:11:59 |
Message-ID: | Pine.BSO.4.64.0610091409050.31431@resin.csoft.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2006-10-09 21:51:59 | Re: width_bucket function for timestamps |
Previous Message | Aaron Bono | 2006-10-09 19:57:28 | Re: [HACKERS] timestamp subtraction (was Re: formatting intervals with to_char) |