Perhaps some enterprising soul could write the datediff function and put it
in the wiki for everyone to use. In fact a separate area just for
user-written functions might be quite helpful.
For what it's worth, I have one that truncates a time (stored in time_t
format) down to the start of an hour:
void GetStartOfHourUTF16(sqlite3_context* context, int numArgs,
sqlite3_value** args)
{
_ASSERT(1 == numArgs);
__int64 timeVal = 0;
switch( sqlite3_value_type(args[0]) )
{
default:
case SQLITE_NULL:
case SQLITE_BLOB: _ASSERT(0); break;
case SQLITE_FLOAT: timeVal = (__int64)sqlite3_value_double(args[0]);
break;
case SQLITE_TEXT: timeVal =
_wtoi64((wchar_t*)sqlite3_value_text16(args[0])); break;
case SQLITE_INTEGER: timeVal = sqlite3_value_int64(args[0]); break;
}
timeVal /= 3600; //take it down to hours
timeVal *= 3600; //have now stripped of minutes and seconds
sqlite3_result_int64(context, timeVal);
}
> -----Original Message-----
> From: Fowler, Jeff [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 17, 2008 10:59 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Date arithmetic question
>
> Guys,
>
> I guess I'm the newest SQLite person on this email list and I know I'm
definitely the
> dumbest. It seems like a lot of you are trying to justify why two dates
that are one
> minute apart can have a function say they're one month apart. Don't look
at it that
> way. Back when mainframes and dinosaurs ruled the world I used to be a
fairly
> decent programmer, now I run a small company, so today I'm coming from a
> business standpoint not a programmer one.
>
> To give some background, we're in the process of embedding SQLite into our
> application, and yes it's an awesome product. Our software sits on top of
huge data
> warehouses (hundreds of millions of rows) which are typically either
Oracle or SQL
> Server. We run queries against these databases and store the result sets
in SQLite,
> where we run subsequent queries to filter & format the output.
>
> A huge number of businesses are date driven. Publishers want to renew
> subscriptions before they expire. Insurance companies need to renew
policies. Our
> largest client sells service contracts which have a start & end date. So
it's an
> everyday occurrence for a manager to want to know how many customers will
> expire within the next three months, or what an average contract length is
in
> months.
>
> My request was for a new date function that returns the difference, or
"calendar
> interval" if you prefer, between two dates. Without such a function we
must say:
> WHERE (strftime('%Y', LaborEndDate)*12+strftime('%m', LaborEndDate)) -
> (strftime('%Y', LaborStartDate)*12+strftime('%m', LaborStartDate)) < 3
>
> Wow. This is quite a mouthfull for something that's so commonly needed,
and it's
> harder to generate SQL automatically when a user clicks a checkbox.
Clearly it's far
> simpler (and easier to program) if we could say:
> WHERE datediff('month', LaborEndDate, LaborStartDate) < 3
>
> Datediff also supports years, weeks, days, hours, seconds, and
milliseconds. It's just
> a quick & easy way to make life easier. But.. of course I know the SQLite
team can't
> slap in every enhancement that somebody suggests; so I just wanted to
explain
> why this would be useful for some of us. We do have a vested interest in
the
> product!
>
> - Jeff
>
>
> ________________________________
>
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thu 1/17/2008 9:21 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Date arithmetic question
>
>
>
> Gerry Snyder <[EMAIL PROTECTED]> wrote:
> > [EMAIL PROTECTED] wrote:
> > >
> > > So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00')
> > > should return 1 even though the difference is really only 1 second?
> > > Seems goofy to me....
> > >
> > >
> >
> > I have been staring at this until I'm getting goofy.
> >
> > Written as it is, isn't the time interval 1 second short of two days?
> >
> > If you want an interval of 1 second shouldn't it be
> >
> > datediff('month', '2008-02-01 00:00:00','2008-01-31 23:59:59')
> >
> > ?
> >
> >
> > Gerry, more confused than usual (as usual)
> >
>
> Yeah. I got it backwards. Sorry.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
>
----------------------------------------------------------------------------
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
----------------------------------------------------------------------------
-
>
>
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
