Fractional seconds

from the Artful MySQL Tips List

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 2025

Return to the Artful MySQL Tips page