The return you are getting is correct for the format you are using. A 90 second difference is in fact 1 minute, 30 seconds(130). To get the time difference in seconds convert the datetime or timestamp to a julian date or unixtime and then process.
SELECT start_time, end_time, UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) FROM mailings_sendstats order by start_time desc; I hope this helps... Pat... CocoNet Corporation SW Florida's 1st ISP ----- Original Message ----- From: "Brian Erickson" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Thursday, March 03, 2005 7:23 PM Subject: # Seconds between two datetime fields > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
