A MySQL forum reader recently asked how to express millisecond speed trial results as hr:min:sec plus a fraction.
Since version 5.6.4, MySQL will store fractional seconds in Time, DateTime and TimeStamp columns, so it's no longer necessary to store fractional seconds in separate columns. Here's an example of such calculations:
drop table if exists t; create table t( i smallint unsigned primary key auto_increment, t time(3) ); set @minsec = Sec_To_Time( Floor( 114560/1000 )); set @thou = Floor( 1000 * ((114560/1000) - Floor( 114560/1000 ) )); select @minsec, @thou; insert into t (t)( concat( @minsec, '.', @thou )); select i,t from t; +------+--------------+ | i | t | +------+--------------+ | 1 | 00:01:54.560 | +------+--------------+If such functionality was needed once, very likely it'll be needed again and again, so get rid of the need to repeat the details of that calculation by encapsulating it in a function: set global log_bin_trust_function_creators=1; drop function if exists extendedtime; delimiter go create function extendedtime( pval int, pfactor int unsigned ) returns char(16) begin set @minsec = Sec_To_Time( Floor( pval/pfactor )); set @fract = Floor( pfactor * ((pval/pfactor) - Floor( pval/pfactor ) )); return concat( @minsec, '.', @fract ); end; go delimiter ; insert into t (t) values (extendedtime(3423,100)),(extendedtime(114560,1000)) ; select i,t from t; +---+--------------+ | i | t | +---+--------------+ | 1 | 00:01:54.560 | | 2 | 00:00:34.230 | | 3 | 00:01:54.560 | +---+--------------+ Last updated 6 Feb 2013 |