Basil Hussain wrote:
>
> Hi,
>
> >> mysql> SELECT impression, click, click - impression AS diff, DATE_SUB(click,
> >> INTERVAL click - impression SECOND) AS calc FROM eventlog;
> >> +---------------------+---------------------+------+---------------------+
> >> | impression | click | diff | calc |
> >> +---------------------+---------------------+------+---------------------+
> >> | 2001-02-22 12:07:03 | 2001-02-22 12:09:04 | 201 | 2001-02-22 12:05:43 |
> >> | 2001-02-22 12:14:39 | 2001-02-22 12:14:44 | 5 | 2001-02-22 12:14:39 |
> >> | 2001-02-22 12:16:13 | 2001-02-22 12:16:17 | 4 | 2001-02-22 12:16:13 |
> >> +---------------------+---------------------+------+---------------------+
> >> 3 rows in set (0.00 sec)
>
> >> As you can see, the difference between the two dates on the first record is
> >> clearly 2 minutes, 1 second - which works out as 121 seconds. However, MySQL
> >> thinks it's 201 seconds, which is actually 3 minutes 21 seconds - wrong!
>
> > 2 min and 1 sec, like 0201 ?
>
> Ah, now I see why it's "201"! Just to check it's not a coincidence though, I
> got MySQL to calculate the difference between now and 60 seconds ago:
>
> mysql> SELECT NOW() - DATE_SUB(NOW(), INTERVAL 60 SECOND) AS calc;
> +------+
> | calc |
> +------+
> | 100 |
> +------+
> 1 row in set (0.00 sec)
>
> So, this makes sense, according to the above.
>
> But, the question remains - why the hell does MySQL return the result of the
> subtraction/addition in this format? It's completely un-intelligable. I
> probably would have guessed if it was "0201" or "000201", but it's just
> stupid how it does it. To prove this, I just did another test:
>
> mysql> SELECT NOW() - DATE_SUB(NOW(), INTERVAL 7 MONTH) AS calc;
> +------------+
> | calc |
> +------------+
> | 9500000000 |
> +------------+
> 1 row in set (0.00 sec)
>
> What the hell is "9500000000" supposed to represent? Can anyone explain the
> rational behind this?
>
> Anyway, this is clearly not gonna be suitable for subtracting/adding dates,
> so I suppose I'll have to resort to something ugly like converting each date
> to a timestamp before subtracting.
>
> Regards,
>
> ------------------------------------------------
> Basil Hussain ([EMAIL PROTECTED])
select now()-0, and you will see the answer is YYYYMMDDhhmmss.
Now, represent 2 dates as strings of 14 numeric characters,
and subtract them as numbers.
Does it mean anything? Well, since the numbering system changes about 5
times as you move from left to right through the string, and the math is
done base 10, I think it would be difficult to make much sense of the
result.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
