select sec_to_time(time_to_sec(transfer_end)-time_to_sec(transfer_start));
Dirk Bremer (NISC) wrote:
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]
