Professional Web Applications Themes

Finding number of months between dates - MySQL

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

  1. #1

    Default Finding number of months between dates

    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

    laredotornado@zipmail.com Guest

  2. #2

    Default Re: Finding number of months between dates

    com wrote: 

    How are you defining a month?


    Paul Guest

  3. #3

    Default Re: Finding number of months between dates

    On Jul 9, 5:23 pm, "Paul Lautman" <com> wrote: 


    >
    > How are you defining a month?[/ref]

    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

    laredotornado@zipmail.com Guest

  4. #4

    Default Re: Finding number of months between dates

    On 10 Jul, 16:59, "com"
    <com> wrote: [/ref]
    > [/ref]
    > [/ref]

    >
    > 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[/ref]

    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?

    Captain Guest

  5. #5

    Default Re: Finding number of months between dates

    On Jul 10, 11:30 am, Captain Paralytic <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]




    >
    > 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 -[/ref]

    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

    laredotornado@zipmail.com Guest

  6. #6

    Default Re: Finding number of months between dates

    com wrote: 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >>
    >> 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 -[/ref]
    >
    > 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[/ref]

    You said "To answer your questions", and then answered only one of them!


    Paul Guest

  7. #7

    Default Re: Finding number of months between dates

    On Jul 10, 4:28 pm, "Paul Lautman" <com>
    wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > You said "To answer your questions", and then answered only one of them!- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Oops. The answer to the other one
     
    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. -

    laredotornado@zipmail.com Guest

  8. #8

    Default Re: Finding number of months between dates

    com wrote: 
    >> You said "To answer your questions", and then answered only one of them!- Hide quoted text -
    >>
    >> - Show quoted text -[/ref]
    >
    > Oops. The answer to the other one

    > 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. -
    >[/ref]
    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
    Adam Guest

Similar Threads

  1. Months and dates issues
    By reya276 in forum Coldfusion - Advanced Techniques
    Replies: 5
    Last Post: November 16th, 06:33 PM
  2. Replies: 4
    Last Post: February 16th, 06:10 AM
  3. Finding dates via a portal
    By DavidF in forum FileMaker
    Replies: 2
    Last Post: January 26th, 06:58 AM
  4. Calculating the number of months
    By Ben in forum Microsoft Access
    Replies: 2
    Last Post: August 8th, 12:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139