From: Brian Erickson (erickson.briangmail.com)
Date: Thu Mar 03 2005 - 18:23:41 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]