Re: [sqlite] Date arithmetic question
[EMAIL PROTECTED] wrote:
"Virgilio Fornazin" <[EMAIL PROTECTED]> wrote:
DATEDIFF should compute the difference by arithmetic subtracting M/Y in
month case, if I'm not wrong
ex:
DateDiff (month, 1-1-2007, 3-30-2007) will return 2
Its that right ?
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
Weighing in for a second...
Given the context of "month", I suppose it is no more goofy than saying
"see you next year" to someone one second before midnight on New Years
Eve. I'd guess that's why most debt instruments define what they mean
by "month" as a specific, if arbitrary, day in the month.
John
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Date arithmetic question
On Thu, 17 Jan 2008, [EMAIL PROTECTED] wrote:
"Virgilio Fornazin" <[EMAIL PROTECTED]> wrote:
DATEDIFF should compute the difference by arithmetic subtracting M/Y in
month case, if I'm not wrong
ex:
DateDiff (month, 1-1-2007, 3-30-2007) will return 2
Its that right ?
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
--
D. Richard Hipp <[EMAIL PROTECTED]>
The timestamp is a very useful thing, but IMHO, dates are dates, and times
are times. Date arithmentic is very easy given a true Julian date,
calculated as the number of *DAYS* since an "epoch" (Oct 15, 1583?), and
times can be the number of seconds, tenths of seconds, milliseconds or
whatever in a day, but stored as separate variables.
There is much subjective processing in time differences when you start
adding +- n months, and much of it can be avoided by not using the system
type timestamps to store dates, and keeping a date variable and time
variable (both integers).
I wrote a Tcl binding for a Julian date and time library I've used for
years, and made it available, both as C library (BSD/Linux/Windows), and
Tcl Extension (BSD/Windows). There is a unix style man page for
documentation.
http://www.controlq.com/OpenSource/Tcl_Julian.tgz
HTH,
Rob Sciuk
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Date arithmetic question
Writing, adding and using your own functions within SQLite is pretty
easy. That's probably your best bet to solve this problem.
-T
> -Original Message-
> From: Fowler, Jeff [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 17, 2008 11: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]
-
RE: [sqlite] Date arithmetic question
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]
-
Re: [sqlite] Date arithmetic question
Fowler, Jeff wrote:
Hello All,
SQLite newbie here. I've looked through the email archives and website
trying to find out how to compute the difference in months between two
given dates. Each date is in -MM-DD HH:MM:SS format.
The best I've been able to come up with seems rather ugly:
SELECT (strftime( '%Y', date1)*12+strftime('%m',date1)) -
(strftime('%Y',date1)*12+strftime('%m',date1))
Am I on the right track, or is there something obvious I'm missing?
Thanks in advance,
Jeff Fowler
For the other query you reported in a later mail
WHERE (strftime('%Y', LaborEndDate)*12+strftime('%m', LaborEndDate)) -
(strftime('%Y', LaborStartDate)*12+strftime('%m', LaborStartDate)) < 3
I'm using something like this:
WHERE (date(date1, '+3 months') > date(date2))
Paolo
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
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]
-
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]
-
Re: [sqlite] Date arithmetic question
[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)
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Date arithmetic question
On 1/17/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Fowler, Jeff" <[EMAIL PROTECTED]> wrote:
> > Remember gang, if you want to know how many seconds are between two
> > timestamps, you wouldn't ask for a difference in months. You'd say
> > something like DATEDIFF(seconds, t1, t2).
> >
>
> So DATEDIFF doesn't really compute the difference between
> two dates.
right, because to compute the "real" difference is tricky as you have
already pointed out... until someone mandates a 100 day, 10 months
year, a 10 hour day, and so on.
> It computes the number of date measurement
> interval transitions that occur between the two dates.
in other words, the function provides the rope along with mandatory
warnings, and if the user/developer still wishes to hang him/herself,
well, so be it.
>
> >
> > -Original Message-
> > From: Markus Hoenicka [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, January 17, 2008 3:47 PM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Date arithmetic question
> >
> > [EMAIL PROTECTED] writes:
> > > 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
> > >
> >
> > well, this is one second rounded up to the next full month...If that is
> > the kind of information you want to compute, it's probably not that
> > goofy after all.
> >
> > regards,
> > Markus
> >
> > --
> > Markus Hoenicka
> > [EMAIL PROTECTED]
> > (Spam-protected email: replace the quadrupeds with "mhoenicka")
> > http://www.mhoenicka.de
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
> > .
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Date arithmetic question
"Fowler, Jeff" <[EMAIL PROTECTED]> wrote:
> Remember gang, if you want to know how many seconds are between two
> timestamps, you wouldn't ask for a difference in months. You'd say
> something like DATEDIFF(seconds, t1, t2).
>
So DATEDIFF doesn't really compute the difference between
two dates. It computes the number of date measurement
interval transitions that occur between the two dates.
>
> -Original Message-
> From: Markus Hoenicka [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 17, 2008 3:47 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Date arithmetic question
>
> [EMAIL PROTECTED] writes:
> > 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
> >
>
> well, this is one second rounded up to the next full month...If that is
> the kind of information you want to compute, it's probably not that
> goofy after all.
>
> regards,
> Markus
>
> --
> Markus Hoenicka
> [EMAIL PROTECTED]
> (Spam-protected email: replace the quadrupeds with "mhoenicka")
> http://www.mhoenicka.de
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
> .
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Date arithmetic question
Remember gang, if you want to know how many seconds are between two
timestamps, you wouldn't ask for a difference in months. You'd say
something like DATEDIFF(seconds, t1, t2).
- Jeff
-Original Message-
From: Markus Hoenicka [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 17, 2008 3:47 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date arithmetic question
[EMAIL PROTECTED] writes:
> 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
>
well, this is one second rounded up to the next full month...If that is
the kind of information you want to compute, it's probably not that
goofy after all.
regards,
Markus
--
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Date arithmetic question
Not really that goofy, just very specific.
The SQL Server manual describes it this way:
"Returns the number of date and time boundaries crossed between two
specified dates."
Regards,
Noah Hart
-Original Message-
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
--
D. Richard Hipp <[EMAIL PROTECTED]>
CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If you are
not the addressee or authorized to receive this for the addressee, you must not
use, copy, disclose, or take any action based on this message or any
information herein. If you have received this message in error, please advise
the sender immediately by reply e-mail and delete this message. Thank you for
your cooperation.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Date arithmetic question
[EMAIL PROTECTED] writes:
> 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
>
well, this is one second rounded up to the next full month...If that
is the kind of information you want to compute, it's probably not that
goofy after all.
regards,
Markus
--
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Date arithmetic question
"Virgilio Fornazin" <[EMAIL PROTECTED]> wrote:
> DATEDIFF should compute the difference by arithmetic subtracting M/Y in
> month case, if I'm not wrong
>
> ex:
>
> DateDiff (month, 1-1-2007, 3-30-2007) will return 2
>
> Its that right ?
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
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Date arithmetic question
You're right of course - I just noticed the question pertains to the
algorithm, not the function itself. Sorry!
I just ran a simple test using "popular RDBMS product A" on one of our
internal databases, as follows:
select 'year difference:', datediff(yy,'12/31/2007','1/1/2008') --> year
difference: 1
select 'month difference:', datediff(mm,'1/31/2007','2/1/2007') -->
month difference: 1
select 'week difference:', datediff(wk,'1/5/2008','1/6/2008') --> week
difference: 1
So for months, the function basically does the arithmetic I did myself
using the
(strftime( '%Y', date1)*12+strftime('%m',date1)) -
(strftime('%Y',date2)*12+strftime('%m',date2))
Regardless of actual days, it simply decides that if two dates occur in
two consecutive calendar months, they are a month apart. Same w/ year
and week. Of course, for precise accuracy it's better to use days..
- Jeff
-Original Message-
From: Scott Baker [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 17, 2008 3:13 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date arithmetic question
Fowler, Jeff wrote:
> Yes - I've looked over the current date functions. I would propose a
> single function addition that's hugely valuable in the business world.
> SQL Server has a function called "datediff" for date arithmetic. It
> accepts three parameters. The first indicates the unit of scale
> (years, months, weeks, days, etc. - I think it even goes into
milliseconds!).
> The next two parameters are the dates to compute the difference
between.
> It returns an integer.
I don't think you answered Richard's original question. What constitutes
a month? Since month lengths vary, there is no exactly science to "how
many months between these two dates." Otherwise your best bet is what he
already recommended.
SELECT (julianday(date2) - julianday(date1)) / 30.43666 AS Months;
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Date arithmetic question
DATEDIFF should compute the difference by arithmetic subtracting M/Y in month case, if I'm not wrong ex: DateDiff (month, 1-1-2007, 3-30-2007) will return 2 Its that right ? A good reference for trying implementing it should be: http://www.sqlteam.com/article/datediff-function-demystified On Jan 17, 2008 6:12 PM, <[EMAIL PROTECTED]> wrote: > "Fowler, Jeff" <[EMAIL PROTECTED]> wrote: > > Yes - I've looked over the current date functions. I would propose a > > single function addition that's hugely valuable in the business world. > > SQL Server has a function called "datediff" for date arithmetic. It > > accepts three parameters. The first indicates the unit of scale (years, > > months, weeks, days, etc. - I think it even goes into milliseconds!). > > The next two parameters are the dates to compute the difference between. > > It returns an integer. > > > > Fair enough, and simple enough to do for weeks, days, and smaller > intervals. But what algorithm do I use to compute the number of > months difference? Or years? Is there a standard for such a > thing? Anybody know? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] Date arithmetic question
Fowler, Jeff wrote: Yes - I've looked over the current date functions. I would propose a single function addition that's hugely valuable in the business world. SQL Server has a function called "datediff" for date arithmetic. It accepts three parameters. The first indicates the unit of scale (years, months, weeks, days, etc. - I think it even goes into milliseconds!). The next two parameters are the dates to compute the difference between. It returns an integer. I don't think you answered Richard's original question. What constitutes a month? Since month lengths vary, there is no exactly science to "how many months between these two dates." Otherwise your best bet is what he already recommended. SELECT (julianday(date2) - julianday(date1)) / 30.43666 AS Months; -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date arithmetic question
"Fowler, Jeff" <[EMAIL PROTECTED]> wrote: > Yes - I've looked over the current date functions. I would propose a > single function addition that's hugely valuable in the business world. > SQL Server has a function called "datediff" for date arithmetic. It > accepts three parameters. The first indicates the unit of scale (years, > months, weeks, days, etc. - I think it even goes into milliseconds!). > The next two parameters are the dates to compute the difference between. > It returns an integer. > Fair enough, and simple enough to do for weeks, days, and smaller intervals. But what algorithm do I use to compute the number of months difference? Or years? Is there a standard for such a thing? Anybody know? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Date arithmetic question
Yes - I've looked over the current date functions. I would propose a
single function addition that's hugely valuable in the business world.
SQL Server has a function called "datediff" for date arithmetic. It
accepts three parameters. The first indicates the unit of scale (years,
months, weeks, days, etc. - I think it even goes into milliseconds!).
The next two parameters are the dates to compute the difference between.
It returns an integer.
Regards,
- Jeff
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 17, 2008 2:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Date arithmetic question
"Fowler, Jeff" <[EMAIL PROTECTED]> wrote:
> Hello All,
>
> SQLite newbie here. I've looked through the email archives and website
> trying to find out how to compute the difference in months between two
> given dates. Each date is in -MM-DD HH:MM:SS format.
>
> The best I've been able to come up with seems rather ugly:
>
> SELECT (strftime( '%Y', date1)*12+strftime('%m',date1)) -
> (strftime('%Y',date1)*12+strftime('%m',date1))
>
> Am I on the right track, or is there something obvious I'm missing?
>
Computing the number of months between two dates is problematic since
the length of a month varies. Is 2007-01-31 to 2007-02-28 a whole month
even thought it is only 28 days? What about 2007-04-10 to 2007-05-08?
That is also 28 days. Does it count as a whole month too, or is it two
days shy of a whole month?
You can compute the number of days between two days very easily:
SELECT julianday(date2) - julianday(date1);
And I suppose you could divide that value by 30.4366 (which is the
average number of days in a month) to get the number of months.
So the answer to your question is that there is no easy answer to your
question because it depends on how you define the "difference in months"
and there does not appear to be a single intuitive definition for that
question.
Do you have a particular algorithm for "difference in months"
in mind?
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Date arithmetic question
"Fowler, Jeff" <[EMAIL PROTECTED]> wrote:
> Hello All,
>
> SQLite newbie here. I've looked through the email archives and website
> trying to find out how to compute the difference in months between two
> given dates. Each date is in -MM-DD HH:MM:SS format.
>
> The best I've been able to come up with seems rather ugly:
>
> SELECT (strftime( '%Y', date1)*12+strftime('%m',date1)) -
> (strftime('%Y',date1)*12+strftime('%m',date1))
>
> Am I on the right track, or is there something obvious I'm missing?
>
Computing the number of months between two dates is problematic
since the length of a month varies. Is 2007-01-31 to 2007-02-28
a whole month even thought it is only 28 days? What about
2007-04-10 to 2007-05-08? That is also 28 days. Does it count
as a whole month too, or is it two days shy of a whole month?
You can compute the number of days between two days very easily:
SELECT julianday(date2) - julianday(date1);
And I suppose you could divide that value by 30.4366 (which
is the average number of days in a month) to get the number of
months.
So the answer to your question is that there is no easy answer
to your question because it depends on how you define the
"difference in months" and there does not appear to be a
single intuitive definition for that question.
Do you have a particular algorithm for "difference in months"
in mind?
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
