简单介绍

MySQL的数据类型中,datetime和timestamp是有点区别的两种字段类型。datetime存储的时间与时区无关,timestamp存储的时间总是为UTC。所以说,datetime的时间戳需要应用去解释所在的时区,而timestamp则没有应用依赖性,它是一个精确的戳记无所谓应用处于哪个时区。

最佳策略

实际的场景中,你的insert/update动作会依据服务器系统时区和mysql配置的不同而带来不同的效果:对于系统时区为utc,或者mysql daemon服务配置为utc,则datetime/timestamp中都是存储的utc时间戳。如果不是这样的场景,则提交insert/update时,需要将datetime字段转换为utc时间戳。

当保证了你的db中只有utc时间戳之后,显示时你需要将时间转换到用户时区。

如果使用nodejs moment来包装时间戳,假定通过mysql driver拿到的datetime形如:”2018-03-09T12:37:59.012009Z“的格式,那么简单地:

  var dt = '2018-03-09T12:37:59.012009Z'
  console.log(moment(dt).fromNow()); // xxx天前
  console.log(moment(dt).format('YYYY-MM-DD HH:mm:ssZZ')); // 2018-03-09 20:37:59+0800

注意到用户时区有浏览器默认值提供,moment()识别到附带时区指示的时间戳UTC值后能够正确地格式化到用户的时区。

测试mysql的时区设定

可以使用SQL语句来测试或者修改mysql的时区设定:

SELECT @@global.time_zone, @@session.time_zone;

SET GLOBAL time_zone = '+08:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
SET @@global.time_zone = '+00:00';

SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";

SET time_zone = "+08:00";
SELECT * FROM hz_1;
SELECT * FROM hz_user;

CREATE TABLE `hz_1` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `worktime` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
 `city` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
 `created_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
 `updated_at` timestamp(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

如果你在应用中使用mysql时区修正的话,呈现层或许会简单一点。但我并不建议你这么做。应该去做的是在日志中打印mysql的当前时区设定值,这可以帮助你在部署时检测生产环境的正确性。

在很多情况下,mysql可能只会接受 SET time_zone = “+00:00”; 这一种格式,而不能接受 SET time_zone = ‘Europe/Helsinki’; 这一种格式,这和服务器端的配置有关系。

补充

要想支持 SET time_zone = ‘Europe/Helsinki’; 格式,需要建立 mysql.timezone 等系统表。这一组系统表在不同操作系统环境中的安装方法各自不同,具体请查阅:

https://dev.mysql.com/downloads/timezones.html

 

小结

综上,我们可以在持久层约定:

  • 任何时间戳都是时区无依赖的 UTC 时间戳

而在呈现层,则可以随意解释这些时间戳记,只需要考虑用户设定的时区即可。