Magicdawn
0%

MySQL timezone 总结

发表于 分类于

mysql doc

https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

  • @@system_time_zone 是系统的 timezone, Mysql 存储之后不再改变
  • @@time_zone 是 mysql 全局 timezone, 默认值为 "SYSTEM" , 表示使用系统的 timezone 设置
  • @@session.time_zone 是连接级别的 timezone 设置

使用

  • SET global time_zone = '+08:00' 设置全局 time zone
  • SET @@session.time_zone = '+08:00' / set time_zone = '' 设置连接级别 time zone

规则

The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIMEcolumns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

The current values of the global and client-specific time zones can be retrieved like this:

1
SELECT @@global.time_zone, @@session.time_zone;
  • time_zone 设置会影响 zone-sensitive 值的显示和存储, 例如 now() / curtime() 函数, 以及 TIMESTAMP 列. 为 TIMESTAMP 列设置值时, 会将值从当前时区转化为 UTC 时区值进行存储, 取值时会从 UTC 转为当前时区的值.
  • time_zone 设置不会影响 utc_timestamp() 函数的值, 以及 DATE / TIME / DATETIME 列的存储取值. 只有从 TIMESTAMP 类型为这些类型赋值时才会应用 time_zone 设置

sails-mysql models

get node-mysql

select 取值, 从 DB 拿 datetime 类型的值时, mysql 会返回字符串, 由 node-mysql parse 成 JavaScript Date 对象, parse 的过程会考虑 timezone, 于是 node-mysql 有个 timezone option, default = local

https://github.com/mysqljs/mysql#connection-options

get sails-mysql

** 对 sails-mysql 来说, timezone 设置传递不过去 v0.12 **

对于这种情况 parse 得到的 Date 的本地时间 = 数据库时间, 这是语言 / 框架层面的问题, 可以在应用层改进.

例如 server-time-zone = utc, local = +8:00. 数据库 date = 2000-01-01 12:00:00, 在 Js 会成为本地的 12 点, 要达到意图, 可以使用 moment 重做一次

1
2
3
// o = js Date
const original = moment(o).format("YYYY-MM-DD HH:mm:ss");
const realDate = moment.utc(original).toDate(); // UTC 12:00, +8 时区的 20:00

set node-mysql

对于 node-mysql 来说, 需要使用 time-zone 设置将 Date object 序列化成 string

sails autoCreatedAt / autoUpdatedAt

与 MySQL 之间的交互, 还是通过 string YYYY-MM-DD HH:mm:ss 格式进行传递

mysql defaults CURRENT_TIMESTAMP

mysql 级的默认值, 但是会 TIMESTAMP 会受时区影响

mysql ON UPDATE CURRENT_TIMESTAMP

在更新的时候, 自动将字段置位 CURRENT_TIMESTAMP , 又是与 time_zone 相关的.

如果在 sails 层做了 updatedAt: defaultsTo = utc_now(), 一个随便的更新, 又会根据 time_zone 处理成 client 时区的值.

Timestamp

signed int32 max value = 2^32 - 1 = 21,4748,3647

now = Math.floor(Date.now() / 1000) = 14,9511,5745 大概到 20 年后 int32 就会溢出 :)