I just came across this code I wrote about a year ago which implements a
function equivilant to width_bucket for timestamps.
I wrote this when I was trying to plot some data over time, and I had more
points than I needed. This function allowed me to create a pre-determined
number of "bins" to average the data inside of so that I could get a sane
number of points. Part of the problem was that there were so many data
points, that a sql implementation of the function (or plpgsql, I forget,
it was a year ago) was painfully slow. This C function provided much
better performance than any other means at my disposal.
I wanted to share this code since it may be useful for someone else, but I
don't know exactly what to do with it. So I am putting it out there, and
asking what the proper home for such a function might be. I believe it
would be generally useful for people, but it is so small that it hardly
seems like a reasonable pgFoundry project. Maybe there is a home for such
a thing in the core distribution in a future release?
The code can be found at
http://www.jdrake.com/postgresql/bintimestamp.tar.gz for a buildable PGXS
module, or I attached just the C code. There is no documentation, the
parameters work the same as the width_bucket function. The code is not
necessarily the most readable in the world, I was trying to get as much
speed out of it as possible, since I was calling it over a million times
as a group by value.
Thanks for any pointers...
--
Fortune's Office Door Sign of the Week:
Incorrigible punster -- Do not incorrige./*****************************************************************************
* file: $RCSfile: bintimestamp.c,v $ $Revision: 1.1 $
* module: timestamp
* authors: jeremyd
* last mod: $Author: jeremyd $ at $Date: 2005/10/28 20:26:38 $
*
* created: Fri Oct 28 13:26:38 PDT 2005
*
*****************************************************************************/
#include <string.h>
#include <math.h>
#include "postgres.h"
#include "fmgr.h"
#include "libpq/pqformat.h"
#include "utils/builtins.h"
#include "funcapi.h"
#include "utils/timestamp.h"
#ifndef JROUND
# define JROUND(x) (x)
#endif
Datum timestamp_get_bin_size(PG_FUNCTION_ARGS);
Datum timestamp_bin(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(timestamp_get_bin_size);
Datum
timestamp_get_bin_size(PG_FUNCTION_ARGS)
{
Timestamp start = PG_GETARG_TIMESTAMP(0);
Timestamp stop = PG_GETARG_TIMESTAMP(1);
int32 nbuckets = PG_GETARG_INT32(2);
Interval * retval = (Interval *)palloc (sizeof(Interval));
if (!retval)
{
ereport(ERROR, (errcode(ERRCODE_OUT_OF_MEMORY),
errmsg("insufficient memory for Interval allocation")));
PG_RETURN_NULL();
}
memset (retval, 0, sizeof(Interval));
retval->time = JROUND ((stop - start) / nbuckets);
PG_RETURN_INTERVAL_P(retval);
}
PG_FUNCTION_INFO_V1(timestamp_bin);
Datum
timestamp_bin(PG_FUNCTION_ARGS)
{
/*Timestamp op = PG_GETARG_TIMESTAMP(0);*/
Timestamp start = PG_GETARG_TIMESTAMP(1);
/*Timestamp stop = PG_GETARG_TIMESTAMP(2);*/
Timestamp binsz;
/*int32 nbuckets = PG_GETARG_INT32(3)*/;
binsz = (PG_GETARG_TIMESTAMP(2) - start) / PG_GETARG_INT32(3);
PG_RETURN_TIMESTAMP(JROUND((int)((PG_GETARG_TIMESTAMP(0) - start) /
binsz) * binsz + start));
}
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
