Close, but time_to_sec requires a time argument, not a datetime argument. My
next iteration is:
select ident,
transfer_start,
transfer_end,
sec_to_time(time_to_sec(substring(transfer_end,12,8)) -
time_to_sec(substring(tra
cast(transfer_end - transfer_start as signed) 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 | 02:29:16 |
22916 |
| 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:01:56 |
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:09:10 |
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 | -23:11:07 |
768893 |
| 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:00:52 |
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 | 00:20:34 |
6074 |
| 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:00:35 |
75 |
| 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:20:14 |
2014 |
| 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 |
13 |
+-------+---------------------+---------------------+----------------+------
----------+
15 rows in set (0.01 sec)
Which appears to be working correctly (Transfer Time1) except for ident =
1309, which spans a date boundary. So, back to the drawing board to figure
out the date boundary issue.
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: "gerald_clark" <[EMAIL PROTECTED]>
To: "Dirk Bremer (NISC)" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, June 11, 2004 14:50
Subject: Re: Date/Time Difference Calculations
> select
sec_to_time(time_to_sec(transfer_end)-time_to_sec(transfer_start));
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
