mysql皮秒转成年月日时分秒_MySQL performance_schema 中 TIMER_*类字段 的易读转换

performance_schema中有很多的表(语句信息表，事务信息表等)记录执行的SQL的具体信息，执行事务的具体信息，其中都会有一个叫做TIMER_START的字段，这个字段的值易读性很差，官方文档说是皮秒，但也没说怎么转成人类易读的形式，通过以下的方法可以将其转换为人易读的格式。

举例

以performance_schema.events_statements_history_long表为例，摆上对应的SQL语句，可以考虑将其写成函数。

SELECT

*,

DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - TIMER_START*10e-13 second) AS 'start_time',

ROUND(timer_wait*10E-10, 3) AS 'wait in (ms)'

FROM

performance_schema.events_statements_history_long

where thread_id=194277

limit 1\G;

*************************** 1. row ***************************

THREAD_ID: 194277

EVENT_ID: 323

END_EVENT_ID: 354

EVENT_NAME: statement/sql/select

SOURCE:

TIMER_START: 1122588644870331000

TIMER_END: 1122588645297519000

TIMER_WAIT: 427188000

LOCK_TIME: 327000000

SQL_TEXT: select count(*) from mysql.user

DIGEST: a41461c07eca51bcda21d91cf128cfa6

DIGEST_TEXT: SELECT COUNT ( * ) FROM `mysql` . `user`

CURRENT_SCHEMA: NULL

OBJECT_TYPE: NULL

OBJECT_SCHEMA: NULL

OBJECT_NAME: NULL

OBJECT_INSTANCE_BEGIN: NULL

MYSQL_ERRNO: 0

RETURNED_SQLSTATE: NULL

MESSAGE_TEXT: NULL

ERRORS: 0

WARNINGS: 0

ROWS_AFFECTED: 0

ROWS_SENT: 1

ROWS_EXAMINED: 0

CREATED_TMP_DISK_TABLES: 0

CREATED_TMP_TABLES: 0

SELECT_FULL_JOIN: 0

SELECT_FULL_RANGE_JOIN: 0

SELECT_RANGE: 0

SELECT_RANGE_CHECK: 0

SELECT_SCAN: 0

SORT_MERGE_PASSES: 0

SORT_RANGE: 0

SORT_ROWS: 0

SORT_SCAN: 0

NO_INDEX_USED: 0

NO_GOOD_INDEX_USED: 0

NESTING_EVENT_ID: NULL

NESTING_EVENT_TYPE: NULL

NESTING_EVENT_LEVEL: 0

start_time: 2020-10-22 10:07:39.644871

wait in (ms): 0.427

1 row in set (0.10 sec)

关键字段

DATE_SUB(

NOW(),

INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME') - TIMER_START*10e-13 second

) AS 'start_time'

DATE_SUB(指定日期A, 需要减去的时间间隔B)：如现在是2020-10-22 10:15:00.000(参数A)，我指定需要减去的天数为2即INTERVAL 2 DAY(参数B)，就能返回得到2020-10-20 10:15:00.000

结论

timer_*即从数据库正常启动以来经过的时间，但这个时间不一定准确，官方文档内说这个会基于处理器的啥啥啥进行计算，可能会有波动。

笔者有一台数据库的timer_*字段信息用以上方法计算之后，相差特别大(大的离谱那种，时间多了1年)，不知道原因，这台经历过升级，有知道的大佬，欢迎留言。

创建时间转换函数，方便调用

begin;

set global log_bin_trust_function_creators=on;

DELIMITER $$

DROP FUNCTION IF EXISTS timer_to_date$$

CREATE FUNCTION timer_to_date(timer bigint(20) unsigned) RETURNS varchar(50)

BEGIN

DECLARE res_date varchar(50);

DECLARE uptime int;

SET uptime=(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='UPTIME');

SET res_date=DATE_SUB(NOW(),INTERVAL @uptime - timer*10e-13 second);

return res_date;

END $$

delimiter ;

set global log_bin_trust_function_creators=off;

commit;