Heres a mySql query that does the conversion.
Does anyone know why 0:45:30.2 === NULL ? I can not figure that out.
mysql> SELECT *, IF(time_str RLIKE '[0-9]+:[0-9]+:[0-9]+(\.[0-9]+)?',
IFNULL((HOUR(STR_TO_DATE(time_str, '%h:%i:%s')) * 3600),0) +
IFNULL((MINUTE(STR_TO_DATE(time_str, '%h:%i:%s')) * 60),0) +
(SECOND(STR_TO_DATE(time_str, '%h:%i:%s'))) + IF(INSTR(time_str, ".") > 0,
CAST(CAST(CONCAT('.', SUBSTRING_INDEX(time_str, '.', -1)) AS decimal(6,6)) AS
char(255)), ''), IF(time_str RLIKE '[0-9]+:[0-9]+(\.[0-9]+)?',
(MINUTE(STR_TO_DATE(time_str, '%i:%s')) * 60) + (SECOND(STR_TO_DATE(time_str,
'%i:%s'))) + IF(INSTR(time_str, ".") > 0, CAST(CAST(CONCAT('.',
SUBSTRING_INDEX(time_str, '.', -1)) AS decimal(6,6)) AS char(255)), ''),
IF(time_str RLIKE '[0-9]+(\.[0-9]+)?', (SECOND(STR_TO_DATE(time_str, '%s'))) +
IF(INSTR(time_str, ".") > 0, CAST(CAST(CONCAT('.', SUBSTRING_INDEX(time_str,
'.', -1)) AS decimal(6,6)) AS char(255)), ''), 'NOMATCH'))) AS time_in_sec FROM
time;
+---------+-------------+-------------+
| time_id | time_str | time_in_sec |
+---------+-------------+-------------+
| 1 | 1:00 | 60 |
| 2 | 1:00:00 | 3600 |
| 3 | 1:00:00.234 | 3600.234 |
| 4 | 2:30 | 150 |
| 5 | 2 | 2 |
| 6 | 2.45 | 2.45 |
| 9 | 1:22:45.23 | 4965.23 |
| 10 | 1:40.24 | 100.24 |
| 11 | 0:45:30.2 | NULL |
| 12 | 1:45:30.2 | 6330.2 |
+---------+-------------+-------------+
/*
PLUG: http://plug.org, #utah on irc.freenode.net
Unsubscribe: http://plug.org/mailman/options/plug
Don't fear the penguin.
*/
