MySQL时间戳格式化

MySQL中的时间戳通常以UNIX时间戳格式存储，这是从1970年1月1日开始的秒数。这种格式虽然便于计算，但对人类不友好。我们需要将其转换为可读的日期时间格式。

基本转换函数

FROM_UNIXTIME()是MySQL中最常用的时间戳转换函数。它把UNIX时间戳转为'YYYY-MM-DD HH:MM:SS'格式：

SELECT FROM_UNIXTIME(1617184800);
-- 输出: 2021-03-31 10:00:00

要获取更灵活的格式，可以结合DATE_FORMAT()函数：

SELECT DATE_FORMAT(FROM_UNIXTIME(1617184800), '%Y年%m月%d日 %H时%i分');
-- 输出: 2021年03月31日 10时00分

常用格式化符号

MySQL提供了丰富的日期格式化符号：

• %Y 四位年份 (2023)
• %y 两位年份 (23)
• %m 月份 (01-12)
• %d 日期 (01-31)
• %H 24小时制小时 (00-23)
• %i 分钟 (00-59)
• %s 秒 (00-59)
• %W 星期名称 (Sunday)
• %a 缩写星期名 (Sun)
• %M 月份名称 (January)
• %b 缩写月份名 (Jan)

实际应用示例

1. 转换为年月日格式：

SELECT DATE_FORMAT(FROM_UNIXTIME(1617184800), '%Y-%m-%d');
-- 输出: 2021-03-31

2. 转换为中文日期格式：

SELECT DATE_FORMAT(FROM_UNIXTIME(1617184800), '%Y年%m月%d日');
-- 输出: 2021年03月31日

3. 只获取时间部分：

SELECT DATE_FORMAT(FROM_UNIXTIME(1617184800), '%H:%i:%s');
-- 输出: 10:00:00

时区处理

MySQL的时间戳转换默认使用系统时区。要转换为特定时区(如北京时间)，可以使用CONVERT_TZ()函数：

SELECT CONVERT_TZ(FROM_UNIXTIME(1617184800), '+00:00', '+08:00');
-- 将UTC时间转为北京时间

性能注意事项

1. 避免在WHERE条件中使用函数转换，这会阻止索引使用：

-- 不好: 无法使用索引
SELECT * FROM table WHERE FROM_UNIXTIME(timestamp) > '2021-01-01';

-- 好: 可以使用索引
SELECT * FROM table WHERE timestamp > UNIX_TIMESTAMP('2021-01-01');

2. 对于频繁查询的格式化时间，考虑添加冗余列存储格式化后的值。

高级技巧

1. 处理毫秒级时间戳：

SELECT DATE_FORMAT(
  FROM_UNIXTIME(1617184800.123), 
  '%Y-%m-%d %H:%i:%s.%f'
);
-- 输出: 2021-03-31 10:00:00.123000

2. 计算两个时间戳的时间差：

SELECT TIMESTAMPDIFF(
  HOUR, 
  FROM_UNIXTIME(1617184800), 
  FROM_UNIXTIME(1617271200)
);
-- 输出: 24 (小时差)

3. 提取特定时间部分：

SELECT EXTRACT(YEAR FROM FROM_UNIXTIME(1617184800));
-- 输出: 2021

通过掌握这些MySQL时间戳格式化技巧，你可以更灵活地处理和显示时间数据，满足各种业务需求。