//--------------------------------------------------------------
// Calculate Day name from datetime value
//--------------------------------------------------------------
void ModFunc( sqlite3_context *context, int argc, sqlite3_value **argv
)
{
// retrieve first parameter
long l = sqlite3_value_int64( argv[0] );
// calculate modulo 60 (to return 0 - 59)
l %= 60;
// return as a string, or change to a number if you like
sqlite3_result_text( context, p, 3, free );
}
main()
{
// connect to database
rc = sqlite3_open( "somedata.db", &db );
if ( rc )
throw exception( string("Can't open database: ") +
sqlite3_errmsg( db ) );
// define function used in select
rc = sqlite3_create_function( db, "ModSec", 1, SQLITE_UTF8,
NULL, ModFunc, NULL, NULL );
if ( rc )
{
// log the details here for later debugging
sqlite3_close( db );
throw exception( string("Can't define function: ") +
sqlite3_errmsg( db ) );
}
// Assuming you have a table named 'x'
// and a field named 'when' that has a unix style
// long integer date. The 'group by' will return one
// row for each event that happened N seconds into each minute.
string sql = "SELECT ModSec(when), COUNT(*) FROM x GROUP BY
ModSec(when)";
rc = sqlite3_exec( db, sql.c_str(), PeopleCallback, 0, &zErrMsg
);
if ( rc != SQLITE_OK )
{
sqlite3_close( db );
throw ConException( string("SQL failed: '") + sql +
string("'") + zErrMsg );
}
sqlite3_close( db );
}
--- Lloyd Thomas <> wrote:
> Jay,
> -------------------------------------------------------------------
> |You could select and group by the modulo of the seconds of each|
> |date and get your nice groupings very simply. If you can write a
> |
> |user defined function in whatever language you're using
> |
> |you might try that.
> |
> -------------------------------------------------------------------
> How would you do that in C?
>
> ----- Original Message -----
> From: "Jay" <[EMAIL PROTECTED]>
> To: <[email protected]>; <[EMAIL PROTECTED]>
> Sent: Tuesday, February 22, 2005 2:00 AM
> Subject: Re: [sqlite] tricky date time problem
>
>
> >
> > --- Lloyd Thomas <[EMAIL PROTECTED]> wrote:
> >
> >> I have a query which calculates the number of events during an
> hour
> >> by the
> >> minute. It needs to work out which minute has the most events and
> >> the
> >> average events during that hour. So it should return an array of
> 60
> >> results
> >> for an hour where I can use the MAX() feature in php to find the
> peak
> >
> > It's too bad Sqlite doesn't have the modulo operator, you could
> > select and group by the modulo of the seconds of each date and get
> > your nice groupings very simply. If you can write a user defined
> > function in whatever language you're using you might try that.
> >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > The all-new My Yahoo! - What will yours do?
> > http://my.yahoo.com
>
>
=====
---------------------------------
"Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of
the ancient tomb of the petrified pharaoh, he vowed there would be no curse on
him like on that other Lord, unless you count his marriage to Lady Tarlington
who, when the lost treasure was found, will be dumped faster than that basket
in the bulrushes."
Melissa Rhodes
---------------------------------
The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's
Call
http://www.lulu.com/content/77264
__________________________________
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250
