com wrote:
How are you defining a month?
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...
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
com wrote:
How are you defining a month?
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
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?
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
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!
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. -
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
Bookmarks