Here is an example using sec_to_time. Note that the results are inconsistent and sometimes inaccurate. It seems that when the difference is less than one minute, the result is correct, when it is over one minute, the result is incorrect.
select ident, transfer_start, transfer_end, sec_to_time(transfer_end - transfer_start) as 'Transfer Time1', (transfer_end - transfer_start) as 'Transfer Time2' from queue where ident > 1300 order by queue_time -------------- +-------+---------------------+---------------------+----------------+------ ----------+ | ident | transfer_start | transfer_end | Transfer Time1 | Transfer Time2 | +-------+---------------------+---------------------+----------------+------ ----------+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +-------+---------------------+---------------------+----------------+------ ----------+ 15 rows in set (0.00 sec) Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc > You probably want SEC_TO_TIME: > > http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html > > SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table > > ____________________________________________________________ > Eamon Daly > > > > ----- Original Message ----- > From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, June 11, 2004 1:57 PM > Subject: Date/Time Difference Calculations > > > > I'm using MySQL version 4.0.18. I have two datetime columns in the same > > table, one that represents a start time and the other that represents an > end > > time. I would like to write a query that will show the difference between > > these two columns in a HH:MM:SS format. The values of the two columns as > > inserted into the table are never be separated by more than a few hours, > but > > could span a day boundary, i.e the start time could be late one day end > the > > end time early the next day, so the date will have to be taken into > > consideration for the calculation. Looking at the docs, it appears that > > version 4.1.x has a lot more date/time functions, but I'm wondering if > > something similar can be arrived at under the version that I am using. > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]