Hi all,
I recently did a query to subtract one DATETIME column from another, but I
got odd results for one row and correct results for others. These queries
expand upon what I was doing:
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)
mysql> SELECT TIME_TO_SEC('00:02:01');
+-------------------------+
| TIME_TO_SEC('00:02:01') |
+-------------------------+
| 121 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT SEC_TO_TIME(201);
+------------------+
| SEC_TO_TIME(201) |
+------------------+
| 00:03:21 |
+------------------+
1 row 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!
Do I have a bug here? Or is this not how I should be subtracting/adding
dates? I'm using version 3.23.32-log, intstalled from official MySQL RPMs.
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
