format_time() - xiaoboluo768/mysql-system-schema GitHub Wiki

• 将皮秒值转换为其他可读性更好的单位值，根据皮秒值大小，自动转换为ns、us、ms、s、m、h、d、w这些可读单位(返回值由转换过的值+单位组成)，该函数在其他视图中大量使用

• 参数：

  • picoseconds TEXT：要进行单位转换的皮秒文本值

• 返回值：一个TEXT文本值

• 定义语句

DROP FUNCTION IF EXISTS format_time;

DELIMITER $$

CREATE DEFINER='root'@'localhost' FUNCTION format_time (
        -- We feed in and return TEXT here, as aggregates of
        -- picoseconds can return numbers larger than BIGINT UNSIGNED
        picoseconds TEXT
    )
    RETURNS TEXT CHARSET UTF8
    COMMENT '
            Description
            -----------

            Takes a raw picoseconds value, and converts it to a human readable form.
            
            Picoseconds are the precision that all latency values are printed in 
            within Performance Schema, however are not user friendly when wanting
            to scan output from the command line.

            Parameters
            -----------

            picoseconds (TEXT): 
              The raw picoseconds value to convert.

            Returns
            -----------

            TEXT

            Example
            -----------

            mysql> select format_time(342342342342345);
            +------------------------------+
            | format_time(342342342342345) |
            +------------------------------+
            | 00:05:42                    |
            +------------------------------+
            1 row in set (0.00 sec)

            mysql> select format_time(342342342);
            +------------------------+
            | format_time(342342342) |
            +------------------------+
            | 342.34 us              |
            +------------------------+
            1 row in set (0.00 sec)

            mysql> select format_time(34234);
              +--------------------+
            | format_time(34234) |
            +--------------------+
            | 34.23 ns          |
            +--------------------+
            1 row in set (0.00 sec)
            '
    SQL SECURITY INVOKER
    DETERMINISTIC
    NO SQL
BEGIN
  IF picoseconds IS NULL THEN RETURN NULL;
  ELSEIF picoseconds >= 604800000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 604800000000000000, 2), ' w');
  ELSEIF picoseconds >= 86400000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 86400000000000000, 2), ' d');
  ELSEIF picoseconds >= 3600000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 3600000000000000, 2), ' h');
  ELSEIF picoseconds >= 60000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 60000000000000, 2), ' m');
  ELSEIF picoseconds >= 1000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000000, 2), ' s');
  ELSEIF picoseconds >= 1000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000, 2), ' ms');
  ELSEIF picoseconds >= 1000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000, 2), ' us');
  ELSEIF picoseconds >= 1000 THEN RETURN CONCAT(ROUND(picoseconds / 1000, 2), ' ns');
  ELSE RETURN CONCAT(picoseconds, ' ps');
  END IF;
END$$

DELIMITER ;

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-format-time.html

---

上一篇： format_statement()函数 | 下一篇： list_add()函数