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])
---------------------------------------------------------------------
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
