MySQL select formatted date from millisecond field
If you want to get the microsecond from a field, use %f,
select FROM_UNIXTIME(DATE_COLUMN/1000,'%Y-%M-%d %H:%i:%s %f') from TABLE_NAME;+-------------------------------------------------------+| FROM_UNIXTIME(CREATETIME/1000,'%Y-%M-%d %H:%i:%s %f') |+-------------------------------------------------------+| 2016-March-18 16:02:54 342000 |+-------------------------------------------------------+
Source : MYSQL DATE_FORMAT
CREATE DEFINER=`root`@`localhost` FUNCTION `ConvertTimeDelta`(duration int) RETURNS varchar(20) CHARSET utf8mb4 DETERMINISTICBEGIN DECLARE time_delta VARCHAR(20); DECLARE date_format VARCHAR(20); SET date_format = IF (duration > 3599999, '%H:%i:%s', '%i:%s'); SET time_delta = TIME_FORMAT(SEC_TO_TIME(duration/1000), date_format); RETURN time_delta;END
For exampleselect ConvertTimeDelta(4800240)result = 01:20:00select ConvertTimeDelta(35076)result = 00:35