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]
-----------------------------------------------------------------------------
