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] -
[sqlite] Date arithmetic question
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