Millisconds and Math

[josborne]

SELECT Version( ) 

5.0.67-community


CREATE TABLE `Results_tbl` (
`Race_ID` int(10) NOT NULL,
`Season` year(4) NOT NULL,
`Rac` varchar(3) NOT NULL default 'RAC',
`Rider_ID` int(5) NOT NULL,
`Position` int(2) default NULL,
`Points` int(2) NOT NULL default '0',
`Race_Time` time default NULL,
`MSecs` char(5) default NULL,
`DNS` binary(1) NOT NULL,
`DNF` binary(1) NOT NULL,
`Outcome` varchar(7) default 'FIN'
) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

 

 

The problem I have has its roots in a deficiency in MySQL.

 

I am trying to deal with doing calculations on time down to millisecond which MySQL doesn’t store or deal with.

 

Basically, I am trying to takes time values such as 00:45:23:078 and subtract it from 00:46:45:985.

 

As a workaround to storing these values, I parse them into a time value (Lap_Time) and an Int value (MSecs) (e.g. 00:45:23 in the first column and 078 in the second column) which I CONCAT in order to display it properly.

 

However, when I try to do any sort of math on that concatenated value, the milliseconds are truncated.

 

I have been successful  at converting these values into a single value is milliseconds as follows:

 

SELECT (TIME_TO_SEC(Lap_Time)*1000) 
+ (get_msecs-put_msecs) 
AS Total_Time
FROM Table 

 

This gives me a value that I can do math on. The problem is that I have no idea how to convert it back to a time value and how to display it properly.

 

It seems logical that the SEC_TO_TIME function would help here but it views the number as seconds rather than milliseconds and produces the wrong value.

 

Alternately, I could divide the number by 1000 and then parse that into two values at the decimal, run the SEC_TO_TIME function then CONCAT() into a single value to display it but I have no idea how to accomplish that.

 

 

It seems that someone must have run into this before as Oracle and MS SQL both has millisecond and microsecond capabilities, there must be standard workarounds for MySQL.

 

[Mchl]

MOD(value,1000) will give you number of milliseconds in this value.

 

Then you can use SEC_TO_TIME((value-MOD(value,1000))/1000)

[josborne]

Thanks you, thank you, thank you.

 

I apologize for taking so long to respond. I have been trying to get this to work in a real world query and it has taken quite a bit of thinking on my part.

 

For me, this query is about at the limit of my current abilities but I thought I'd post it to see if anyone has suggestions for maybe simplifying it.

 

SELECT Race_ID, `Season` , `Rac` , `Rider_ID` , `Position` , `Points` , DNS, `DNF` , CONCAT( Race_Time, MSecs ) AS Race_Time, (

SELECT CONCAT( SEC_TO_TIME( (
(

SELECT (

SELECT (
TIME_TO_SEC( Race_Time ) *1000 ) + ( MSecs *1000 ) ) - ( 
SELECT (
TIME_TO_SEC( Race_Time ) *1000 ) + ( MSecs *1000 ) 
FROM Results_tbl
WHERE Race_ID =36
AND Points =25
)
) - MOD( (

SELECT (

SELECT (
TIME_TO_SEC( Race_Time ) *1000 ) + ( MSecs *1000 ) 
) - ( 
SELECT (
TIME_TO_SEC( Race_Time ) *1000 ) + ( MSecs *1000 ) 
FROM Results_tbl
WHERE Race_ID =36
AND Points =25
)
), 1000
)
) /1000
), ".", MOD( (

SELECT (

SELECT (
TIME_TO_SEC( Race_Time ) *1000 ) + ( MSecs *1000 ) 
) - ( 
SELECT (
TIME_TO_SEC( Race_Time ) *1000 ) + ( MSecs *1000 ) 
FROM Results_tbl
WHERE Race_ID =36
AND Points =25
)
), 1000
)
)
) AS Gap
FROM `Results_tbl` 
WHERE Race_ID =36
ORDER BY Points DESC