Finding number of months between dates
This is a discussion on Finding number of months between dates within the MySQL forums, part of the Database Server Software category; --> Hi, Using MySQL 5.0, how would I calculate the number of months between two columns of type TIMESTAMP? The ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 11:12 AM
| |||
| |||
 Re: Finding number of months between dates laredotornado@zipmail.com wrote: > Hi, > > Using MySQL 5.0, how would I calculate the number of months between > two columns of type TIMESTAMP? The columns are named EXPIRATION_DATE > and ACTIVATION_DATE. > > Thanks, - Dave How are you defining a month?      |
|
02-28-2008, 11:12 AM
| |||
| |||
| Re: Finding number of months between dates On Jul 9, 5:23 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote: > laredotorn...@zipmail.com wrote: > > Hi, > > > Using MySQL 5.0, how would I calculate the number of months between > > two columns of type TIMESTAMP? The columns are named EXPIRATION_DATE > > and ACTIVATION_DATE. > > > Thanks, - Dave > > How are you defining a month? What I'm looking for is that I execute the statement below: INSERT INTO EC_SUBSCRIPTIONS (USER_ID, SUBSCRIPTION_ID, ACTIVATION_DATE, EXPIRATION_DATE) VALUES (22, 15, '2007-01-01', DATE_ADD('2007-01-01' INTERVAL 3 MONTH)) I would want to write a SELECT statement that would give me the value "3" from somehow subtracting ACTIVATION_DATE from EXPIRATION_DATE. Thanks, - Dave |
|
02-28-2008, 11:12 AM
| |||
| |||
| Re: Finding number of months between dates On 10 Jul, 16:59, "laredotorn...@zipmail.com" <laredotorn...@zipmail.com> wrote: > On Jul 9, 5:23 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote: > > > laredotorn...@zipmail.com wrote: > > > Hi, > > > > Using MySQL 5.0, how would I calculate the number of months between > > > two columns of type TIMESTAMP? The columns are named EXPIRATION_DATE > > > and ACTIVATION_DATE. > > > > Thanks, - Dave > > > How are you defining a month? > > What I'm looking for is that I execute the statement below: > > INSERT INTO EC_SUBSCRIPTIONS (USER_ID, SUBSCRIPTION_ID, > ACTIVATION_DATE, EXPIRATION_DATE) VALUES (22, 15, '2007-01-01', > DATE_ADD('2007-01-01' INTERVAL 3 MONTH)) > > I would want to write a SELECT statement that would give me the value > "3" from somehow subtracting ACTIVATION_DATE from EXPIRATION_DATE. > > Thanks, - Dave The problem is that this operation is not commutative in this respect. For example in MySQL terms, 2007-03-30 + INTERVAL 1 MONTH gives 2007-04-30, but suppose you subtracted 2007-03-30 from 2007-04-28, would you want the answer to be 0? Then we have from the manual: 1998-01-30 + INTERVAL 1 MONTH equals 1998-02-28, so you'd want 1998-02-28 minus 1998-01-30 to be 1 in this case, or 0 as in the case above? |
|
02-28-2008, 11:12 AM
| |||
| |||
| Re: Finding number of months between dates On Jul 10, 11:30 am, Captain Paralytic <paul_laut...@yahoo.com> wrote: > On 10 Jul, 16:59, "laredotorn...@zipmail.com" > > > > > > <laredotorn...@zipmail.com> wrote: > > On Jul 9, 5:23 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote: > > > > laredotorn...@zipmail.com wrote: > > > > Hi, > > > > > Using MySQL 5.0, how would I calculate the number of months between > > > > two columns of type TIMESTAMP? The columns are named EXPIRATION_DATE > > > > and ACTIVATION_DATE. > > > > > Thanks, - Dave > > > > How are you defining a month? > > > What I'm looking for is that I execute the statement below: > > > INSERT INTO EC_SUBSCRIPTIONS (USER_ID, SUBSCRIPTION_ID, > > ACTIVATION_DATE, EXPIRATION_DATE) VALUES (22, 15, '2007-01-01', > > DATE_ADD('2007-01-01' INTERVAL 3 MONTH)) > > > I would want to write a SELECT statement that would give me the value > > "3" from somehow subtracting ACTIVATION_DATE from EXPIRATION_DATE. > > > Thanks, - Dave > > The problem is that this operation is not commutative in this respect. > > For example in MySQL terms, 2007-03-30 + INTERVAL 1 MONTH gives > 2007-04-30, but suppose you subtracted 2007-03-30 from 2007-04-28, > would you want the answer to be 0? > > Then we have from the manual: 1998-01-30 + INTERVAL 1 MONTH equals > 1998-02-28, so you'd want 1998-02-28 minus 1998-01-30 to be 1 in this > case, or 0 as in the case above?- Hide quoted text - > > - Show quoted text - So am I reading that what I'm asking is impossible? To answer your questions, I would want 1998-02-28 minus 1998-01-30 to be 1 (since 1998-01-30 + INTERVAL 1 MONTH equals 1998-02-28). - Dave |
|
02-28-2008, 11:12 AM
| |||
| |||
| Re: Finding number of months between dates laredotornado@zipmail.com wrote: > On Jul 10, 11:30 am, Captain Paralytic <paul_laut...@yahoo.com> wrote: >> On 10 Jul, 16:59, "laredotorn...@zipmail.com" >> >> >> >> >> >> <laredotorn...@zipmail.com> wrote: >>> On Jul 9, 5:23 pm, "Paul Lautman" <paul.laut...@btinternet.com> >>> wrote: >> >>>> laredotorn...@zipmail.com wrote: >>>>> Hi, >> >>>>> Using MySQL 5.0, how would I calculate the number of months >>>>> between two columns of type TIMESTAMP? The columns are named >>>>> EXPIRATION_DATE and ACTIVATION_DATE. >> >>>>> Thanks, - Dave >> >>>> How are you defining a month? >> >>> What I'm looking for is that I execute the statement below: >> >>> INSERT INTO EC_SUBSCRIPTIONS (USER_ID, SUBSCRIPTION_ID, >>> ACTIVATION_DATE, EXPIRATION_DATE) VALUES (22, 15, '2007-01-01', >>> DATE_ADD('2007-01-01' INTERVAL 3 MONTH)) >> >>> I would want to write a SELECT statement that would give me the >>> value "3" from somehow subtracting ACTIVATION_DATE from >>> EXPIRATION_DATE. >> >>> Thanks, - Dave >> >> The problem is that this operation is not commutative in this >> respect. >> >> For example in MySQL terms, 2007-03-30 + INTERVAL 1 MONTH gives >> 2007-04-30, but suppose you subtracted 2007-03-30 from 2007-04-28, >> would you want the answer to be 0? >> >> Then we have from the manual: 1998-01-30 + INTERVAL 1 MONTH equals >> 1998-02-28, so you'd want 1998-02-28 minus 1998-01-30 to be 1 in this >> case, or 0 as in the case above?- Hide quoted text - >> >> - Show quoted text - > > So am I reading that what I'm asking is impossible? > > To answer your questions, I would want 1998-02-28 minus 1998-01-30 to > be 1 (since 1998-01-30 + INTERVAL 1 MONTH equals 1998-02-28). > > - Dave You said "To answer your questions", and then answered only one of them! |
|
02-28-2008, 11:12 AM
| |||
| |||
| Re: Finding number of months between dates On Jul 10, 4:28 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote: > laredotorn...@zipmail.com wrote: > > On Jul 10, 11:30 am, Captain Paralytic <paul_laut...@yahoo.com> wrote: > >> On 10 Jul, 16:59, "laredotorn...@zipmail.com" > > >> <laredotorn...@zipmail.com> wrote: > >>> On Jul 9, 5:23 pm, "Paul Lautman" <paul.laut...@btinternet.com> > >>> wrote: > > >>>> laredotorn...@zipmail.com wrote: > >>>>> Hi, > > >>>>> Using MySQL 5.0, how would I calculate the number of months > >>>>> between two columns of type TIMESTAMP? The columns are named > >>>>> EXPIRATION_DATE and ACTIVATION_DATE. > > >>>>> Thanks, - Dave > > >>>> How are you defining a month? > > >>> What I'm looking for is that I execute the statement below: > > >>> INSERT INTO EC_SUBSCRIPTIONS (USER_ID, SUBSCRIPTION_ID, > >>> ACTIVATION_DATE, EXPIRATION_DATE) VALUES (22, 15, '2007-01-01', > >>> DATE_ADD('2007-01-01' INTERVAL 3 MONTH)) > > >>> I would want to write a SELECT statement that would give me the > >>> value "3" from somehow subtracting ACTIVATION_DATE from > >>> EXPIRATION_DATE. > > >>> Thanks, - Dave > > >> The problem is that this operation is not commutative in this > >> respect. > > >> For example in MySQL terms, 2007-03-30 + INTERVAL 1 MONTH gives > >> 2007-04-30, but suppose you subtracted 2007-03-30 from 2007-04-28, > >> would you want the answer to be 0? > > >> Then we have from the manual: 1998-01-30 + INTERVAL 1 MONTH equals > >> 1998-02-28, so you'd want 1998-02-28 minus 1998-01-30 to be 1 in this > >> case, or 0 as in the case above?- Hide quoted text - > > >> - Show quoted text - > > > So am I reading that what I'm asking is impossible? > > > To answer your questions, I would want 1998-02-28 minus 1998-01-30 to > > be 1 (since 1998-01-30 + INTERVAL 1 MONTH equals 1998-02-28). > > > - Dave > > You said "To answer your questions", and then answered only one of them!- Hide quoted text - > > - Show quoted text - Oops. The answer to the other one > For example in MySQL terms, 2007-03-30 + INTERVAL 1 MONTH gives 2007-04-30, but suppose you subtracted 2007-03-30 from 2007-04-28, would you want the answer to be 0? is yes because 2007-03-30 + INTERVAL 1 MONTH does not equal 2007-04-28. - |
|
02-28-2008, 11:12 AM
| ||||
| ||||
| Re: Finding number of months between dates laredotornado@zipmail.com wrote: > On Jul 10, 4:28 pm, "Paul Lautman" <paul.laut...@btinternet.com> > wrote: >> laredotorn...@zipmail.com wrote: >>> On Jul 10, 11:30 am, Captain Paralytic <paul_laut...@yahoo.com> wrote: >>>> On 10 Jul, 16:59, "laredotorn...@zipmail.com" >>>> <laredotorn...@zipmail.com> wrote: >>>>> On Jul 9, 5:23 pm, "Paul Lautman" <paul.laut...@btinternet.com> >>>>> wrote: >>>>>> laredotorn...@zipmail.com wrote: >>>>>>> Hi, >>>>>>> Using MySQL 5.0, how would I calculate the number of months >>>>>>> between two columns of type TIMESTAMP? The columns are named >>>>>>> EXPIRATION_DATE and ACTIVATION_DATE. >>>>>>> Thanks, - Dave >>>>>> How are you defining a month? >>>>> What I'm looking for is that I execute the statement below: >>>>> INSERT INTO EC_SUBSCRIPTIONS (USER_ID, SUBSCRIPTION_ID, >>>>> ACTIVATION_DATE, EXPIRATION_DATE) VALUES (22, 15, '2007-01-01', >>>>> DATE_ADD('2007-01-01' INTERVAL 3 MONTH)) >>>>> I would want to write a SELECT statement that would give me the >>>>> value "3" from somehow subtracting ACTIVATION_DATE from >>>>> EXPIRATION_DATE. >>>>> Thanks, - Dave >>>> The problem is that this operation is not commutative in this >>>> respect. >>>> For example in MySQL terms, 2007-03-30 + INTERVAL 1 MONTH gives >>>> 2007-04-30, but suppose you subtracted 2007-03-30 from 2007-04-28, >>>> would you want the answer to be 0? >>>> Then we have from the manual: 1998-01-30 + INTERVAL 1 MONTH equals >>>> 1998-02-28, so you'd want 1998-02-28 minus 1998-01-30 to be 1 in this >>>> case, or 0 as in the case above?- Hide quoted text - >>>> - Show quoted text - >>> So am I reading that what I'm asking is impossible? >>> To answer your questions, I would want 1998-02-28 minus 1998-01-30 to >>> be 1 (since 1998-01-30 + INTERVAL 1 MONTH equals 1998-02-28). >>> - Dave >> You said "To answer your questions", and then answered only one of them!- Hide quoted text - >> >> - Show quoted text - > > Oops. The answer to the other one > >> For example in MySQL terms, 2007-03-30 + INTERVAL 1 MONTH gives > 2007-04-30, but suppose you subtracted 2007-03-30 from 2007-04-28, > would you want the answer to be 0? > > is yes because 2007-03-30 + INTERVAL 1 MONTH does not equal > 2007-04-28. - > If you want the actual months difference without concern to days you could convert the two dates into months and get the difference: select (year('2007-04-01')*12 + month('2007-04-01')) - year('2007-01-01' )*12 + month('2007-01-01')); Adam |
Linear Mode
