We have a table that has two datetime fields (start_time and end_time). We'd like to be able to calculate the number of seconds between these two fields in a query. However, a simple "end_time - start_time" does not yield a correct result.
SELECT start_time, end_time, end_time - start_time FROM mailings_sendstats order by start_time desc; +---------------------+---------------------+-----------------------+ | start_time | end_time | end_time - start_time | +---------------------+---------------------+-----------------------+ | 2005-03-02 19:08:00 | 2005-03-02 19:08:53 | 53 | | 2005-03-02 19:06:00 | 2005-03-02 19:07:31 | 131 | | 2005-03-02 19:04:00 | 2005-03-02 19:04:55 | 55 | | 2005-03-02 19:02:00 | 2005-03-02 19:02:53 | 53 | | 2005-03-02 19:00:00 | 2005-03-02 19:01:10 | 110 | | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 | | 2005-03-02 18:56:00 | 2005-03-02 18:57:10 | 110 | | 2005-03-02 18:54:00 | 2005-03-02 18:55:20 | 120 | | 2005-03-02 18:52:00 | 2005-03-02 18:53:22 | 122 | | 2005-03-02 18:50:00 | 2005-03-02 18:51:26 | 126 | | 2005-03-02 18:48:00 | 2005-03-02 18:48:58 | 58 | | 2005-03-02 18:46:00 | 2005-03-02 18:47:28 | 128 | etc As you can see, if the time difference is less than 1 minute, a correct result is returned. If the difference is 1 minute and 30 seconds, '130' is returned instead of 90. I tried playing with the end_time - start_time conversion using different calculations. What I came up with is below. mysql> SELECT start_time, end_time, -> end_time - start_time as cur, -> FLOOR((end_time - start_time) / 100) as num, -> (end_time - start_time) % 100 as mod, -> (FLOOR((end_time - start_time) / 100) * 60) + (end_time - start_time) % 100 AS seconds -> FROM mailings_sendstats -> ORDER BY id desc; +---------------------+---------------------+-----+------+------+---------+ | start_time | end_time | cur | num | mod | seconds | +---------------------+---------------------+-----+------+------+---------+ | 2005-03-02 19:10:00 | 2005-03-02 19:11:07 | 107 | 1 | 7 | 67 | | 2005-03-02 19:08:00 | 2005-03-02 19:08:53 | 53 | 0 | 53 | 53 | | 2005-03-02 19:06:00 | 2005-03-02 19:07:31 | 131 | 1 | 31 | 91 | | 2005-03-02 19:04:00 | 2005-03-02 19:04:55 | 55 | 0 | 55 | 55 | | 2005-03-02 19:02:00 | 2005-03-02 19:02:53 | 53 | 0 | 53 | 53 | | 2005-03-02 19:00:00 | 2005-03-02 19:01:10 | 110 | 1 | 10 | 70 | | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 | 1 | 80 | 140 | | 2005-03-02 18:56:00 | 2005-03-02 18:57:10 | 110 | 1 | 10 | 70 | | 2005-03-02 18:54:00 | 2005-03-02 18:55:20 | 120 | 1 | 20 | 80 | | 2005-03-02 18:52:00 | 2005-03-02 18:53:22 | 122 | 1 | 22 | 82 | | 2005-03-02 18:50:00 | 2005-03-02 18:51:26 | 126 | 1 | 26 | 86 | | 2005-03-02 18:48:00 | 2005-03-02 18:48:58 | 58 | 0 | 58 | 58 | | 2005-03-02 18:46:00 | 2005-03-02 18:47:28 | 128 | 1 | 28 | 88 | etc First, I calculated the number of minutes (num), then seconds (mod), then total seconds (seconds). If you scroll through the results, you'll see most of them are accurate. However, when the beginning time is just before a new minute, the entire calculation is thrown off. Examples: | 2005-03-02 18:57:59 | 2005-03-02 18:59:39 | 180 | 1 | 80 | 140 | | 2005-03-02 18:37:59 | 2005-03-02 18:38:53 | 94 | 0 | 94 | 94 | So my question is, how can I fix this? I know there are several date/time functions that I could probably use, but they were not introduced until version 4.1 and I am stuck with version 3.23. We also modified the table to use timestamp(14) fields instead of datetime fields, but the same result occurs. Ultimately: a) Can someone think of a way to modify the query above so that it produces *correct* results every time (maybe I'm missing something simple after working on this for so long) b) We're using this table to track execution time of PHP Cron scripts. We may be approaching this entirely the wrong way. If someone has other ideas, please let me know. Thank you very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]