Looking at the results further reveals that a numeric subtraction is being performed on the two datetime fields rather than a date-type subtraction. Any thoughts on how to perform a date subtraction in version 4.0.18?
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 ----- Original Message ----- From: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 11, 2004 14:29 Subject: Re: Date/Time Difference Calculations > 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] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
