【Go-MySQL】time.Time 和 timestamp 类型的时区问题

奇妙海盐

已于 2022-09-28 15:57:11 修改

阅读量2.6k

点赞数

分类专栏：开发问题汇总文章标签： sql mysql golang

于 2022-09-22 10:22:40 首次发布

本文链接：https://blog.csdn.net/ik99s/article/details/126986836

版权

开发问题汇总专栏收录该内容

1 篇文章 0 订阅

订阅专栏

在进行 Golang 开发过程中，遇到 MySQL 时区问题：

当使用 time.Time 类型作为参数进行时间查询时，参数内的时区信息有效
当使用 string 类型作为参数进行时间查询时，参数内的时区信息无效

问题说明

MySQL 版本

MySQL 8.0.29

数据库组件

sqlx

数据库时区设置

Asia/Shanghai

数据库表格信息

表格设计如下：

CREATE TABLE `user`
(
    `id`                 bigint unsigned          NOT NULL AUTO_INCREMENT COMMENT '自增id',
    `uid`                bigint                   NOT NULL COMMENT '用户id',
    `name`               varchar(45)              NOT NULL DEFAULT '' COMMENT '姓名',
    `create_time`        timestamp                NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建的时间',
    `update_time`        timestamp                NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
    PRIMARY KEY (`id`)
) COMMENT ='用户基本信息';

其中，create_time 字段设为 TIMESTAMP 数据类型，之后我们会根据这一字段进行 SQL 查询。

表格内数据如下：

使用 time.Time 类型作为参数进行时间查询

func (m *defaultUserModel) FindByCreateTime(t time.Time) ([]*User, error) {
   query := fmt.Sprintf("select * from %s where `create_time` >= ?", m.table)
   var resp []*User
   err := m.conn.QueryRows(&resp, query, t)
   switch err {
   case nil:
      return resp, nil
   case sqlc.ErrNotFound:
      return nil, ErrNotFound
   default:
      return nil, err
   }
}

参数分别为：

2022-08-31 00:00:00 +0000 UTC
2022-08-31 00:00:00 +0800 CST

在日志中看到如下结果：

可以看到，时区信息在 SQL 查询中得到了有效保留。CST 时间等于同时刻下的 UTC 时间加 8 小时，因此相同时间下，CST 时间比 UTC 时间早 8 小时，因此对应 SQL 查询的结果条数更多。

使用 string 类型作为参数进行时间查询

func (m *defaultUserModel) FindByCreateTimeString(t string) ([]*User, error) {
   query := fmt.Sprintf("select * from %s where `create_time` >= ?", m.table)
   var resp []*User
   err := m.conn.QueryRows(&resp, query, t)
   switch err {
   case nil:
      return resp, nil
   case sqlc.ErrNotFound:
      return nil, ErrNotFound
   default:
      return nil, err
   }
}

参数分别

“2022-08-31 00:00:00 +0000 UTC”
“2022-08-31 00:00:00 +0800 CST”

在日志中看到如下结果：
在这里插入图片描述

可以看到，时区信息在 SQL 查询中未能得到有效保留，不同时区信息的查询结果相同，可见 MySQL 只解析了字符串内的日期和时间，没有解析时区信息。

问题分析

Golang 如何处理 SQL 参数？

经过层层处理，最终由 Go MySQL Driver 中的 interpolateParams 方法，将所有 SQL 参数转化为 string 类型：

func (mc *mysqlConn) interpolateParams(query string, args []driver.Value) (string, error) {
   // Number of ? should be same to len(args)
   if strings.Count(query, "?") != len(args) {
      return "", driver.ErrSkip
   }

   buf, err := mc.buf.takeCompleteBuffer()
   if err != nil {
      // can not take the buffer. Something must be wrong with the connection
      errLog.Print(err)
      return "", ErrInvalidConn
   }
   buf = buf[:0]
   argPos := 0

   for i := 0; i < len(query); i++ {
      q := strings.IndexByte(query[i:], '?')
      if q == -1 {
         buf = append(buf, query[i:]...)
         break
      }
      buf = append(buf, query[i:i+q]...)
      i += q

      arg := args[argPos]
      argPos++

      if arg == nil {
         buf = append(buf, "NULL"...)
         continue
      }

      switch v := arg.(type) {
      case int64:
         buf = strconv.AppendInt(buf, v, 10)
      case uint64:
         // Handle uint64 explicitly because our custom ConvertValue emits unsigned values
         buf = strconv.AppendUint(buf, v, 10)
      case float64:
         buf = strconv.AppendFloat(buf, v, 'g', -1, 64)
      case bool:
         if v {
            buf = append(buf, '1')
         } else {
            buf = append(buf, '0')
         }
      case time.Time:
         if v.IsZero() {
            buf = append(buf, "'0000-00-00'"...)
         } else {
            buf = append(buf, '\'')
            buf, err = appendDateTime(buf, v.In(mc.cfg.Loc))
            if err != nil {
               return "", err
            }
            buf = append(buf, '\'')
         }
      case json.RawMessage:
         buf = append(buf, '\'')
         if mc.status&statusNoBackslashEscapes == 0 {
            buf = escapeBytesBackslash(buf, v)
         } else {
            buf = escapeBytesQuotes(buf, v)
         }
         buf = append(buf, '\'')
      case []byte:
         if v == nil {
            buf = append(buf, "NULL"...)
         } else {
            buf = append(buf, "_binary'"...)
            if mc.status&statusNoBackslashEscapes == 0 {
               buf = escapeBytesBackslash(buf, v)
            } else {
               buf = escapeBytesQuotes(buf, v)
            }
            buf = append(buf, '\'')
         }
      case string:
         buf = append(buf, '\'')
         if mc.status&statusNoBackslashEscapes == 0 {
            buf = escapeStringBackslash(buf, v)
         } else {
            buf = escapeStringQuotes(buf, v)
         }
         buf = append(buf, '\'')
      default:
         return "", driver.ErrSkip
      }

      if len(buf)+4 > mc.maxAllowedPacket {
         return "", driver.ErrSkip
      }
   }
   if argPos != len(args) {
      return "", driver.ErrSkip
   }
   return string(buf), nil
}

其中，对于 time.Time 类型的参数：

case time.Time:
   if v.IsZero() {
      buf = append(buf, "'0000-00-00'"...)
   } else {
      buf = append(buf, '\'')
      buf, err = appendDateTime(buf, v.In(mc.cfg.Loc))
      if err != nil {
         return "", err
      }
      buf = append(buf, '\'')
   }

首先来看 appendDateTime 函数：

func appendDateTime(buf []byte, t time.Time) ([]byte, error) {
   year, month, day := t.Date()
   hour, min, sec := t.Clock()
   nsec := t.Nanosecond()

   if year < 1 || year > 9999 {
      return buf, errors.New("year is not in the range [1, 9999]: " + strconv.Itoa(year)) // use errors.New instead of fmt.Errorf to avoid year escape to heap
   }
   year100 := year / 100
   year1 := year % 100

   var localBuf [len("2006-01-02T15:04:05.999999999")]byte // does not escape
   localBuf[0], localBuf[1], localBuf[2], localBuf[3] = digits10[year100], digits01[year100], digits10[year1], digits01[year1]
   localBuf[4] = '-'
   localBuf[5], localBuf[6] = digits10[month], digits01[month]
   localBuf[7] = '-'
   localBuf[8], localBuf[9] = digits10[day], digits01[day]

   if hour == 0 && min == 0 && sec == 0 && nsec == 0 {
      return append(buf, localBuf[:10]...), nil
   }

   localBuf[10] = ' '
   localBuf[11], localBuf[12] = digits10[hour], digits01[hour]
   localBuf[13] = ':'
   localBuf[14], localBuf[15] = digits10[min], digits01[min]
   localBuf[16] = ':'
   localBuf[17], localBuf[18] = digits10[sec], digits01[sec]

   if nsec == 0 {
      return append(buf, localBuf[:19]...), nil
   }
   nsec100000000 := nsec / 100000000
   nsec1000000 := (nsec / 1000000) % 100
   nsec10000 := (nsec / 10000) % 100
   nsec100 := (nsec / 100) % 100
   nsec1 := nsec % 100
   localBuf[19] = '.'

   // milli second
   localBuf[20], localBuf[21], localBuf[22] =
      digits01[nsec100000000], digits10[nsec1000000], digits01[nsec1000000]
   // micro second
   localBuf[23], localBuf[24], localBuf[25] =
      digits10[nsec10000], digits01[nsec10000], digits10[nsec100]
   // nano second
   localBuf[26], localBuf[27], localBuf[28] =
      digits01[nsec100], digits10[nsec1], digits01[nsec1]

   // trim trailing zeros
   n := len(localBuf)
   for n > 0 && localBuf[n-1] == '0' {
      n--
   }

   return append(buf, localBuf[:n]...), nil
}

appendDateTime 函数将时间转为字符数组类型，并存到 buf 中返回。
appendDateTime 对年月日、时分秒、纳秒信息进行处理，其中不包含对于时区信息的任何处理，因此最终 Golang 传给 MySQL 的时间参数是一个不包含时区信息的纯字符串。

接下来关注调用 appendDateTime 的另一个参数：v.In(mc.cfg.Loc)，其中 In 方法修改时间的时区信息：

func (t Time) In(loc *Location) Time {
    if loc == nil {
        panic("time: missing Location in call to Time.In")
    }
    t.setLoc(loc)
    return t
}

而 mc.cfg.Loc 则是配置数据库连接时设置的时区信息。因此 In 方法没有修改 time.Time 类型参数中存储的时间戳数据，而是仅修改了时区。修改后，通过 appendDateTime 函数时间操作（t.Date()、t.Clock()、t.Nanosecond()）获得的皆是所设置时区下的时间。

因此，之所以将 time.Time 类型作为参数可以使时区信息有效，是因为 Golang 在将参数传递给 MySQL 时已经将 time.Time 类型转为数据库时区下的时间字符串。

此外，需要注意，日志中打印出的并非最终的 SQL 查询语句。

日志中的时间参数生成于 time.Time 类型的 String 方法，该方法返回采用如下格式字符串的格式化时间：“2006-01-02 15:04:05.999999999 -0700 MST”，这与日志中的格式是一致的。（一开始正是由于误以为这是最终 SQL，才采取了相同格式的字符串参数进行尝试）

MySQL 如何解析传入参数？

查看官方文档中的相关说明：9.1.3 Date and Time Literals

简单概括下重点信息：

MySQL通过以下格式识别 TIMESTAMP 和 DATETIME 值：

‘YYYY-MM-DD hh:mm:ss’ 或 ‘YY-MM-DD hh:mm:ss’ 格式的字符串
‘YYYYMMDDhhmmss’ 或 ‘YYMMDDhhmmss’ 格式的字符串（前提是该字符串作为日期有意义）
YYYYMMDDhhmmss 或 YYMMDDhhmmss 格式的数字（前提是该数字作为日期有意义）

从 MySQL 8.0.19 开始，支持识别包含时区信息的 TIMESTAMP 和 DATETIME 值，携带时区信息须严格按照形如 ‘2020-01-01 10:10:10+05:30’ 的格式，即附加在时间信息末尾、不加空格。且须注意如下三点：

对于小于 10 小时的值，需要前导零
不支持 ‘-00:00’
不能使用 ‘EET’ 和 ‘Asia/Shanghai’ 等时区名称

因此，并非使用 string 类型不能传递有效的时区信息，而是使用的格式错误。

使用正确的格式进行 SQL 查询，参数分别为：

“2022-08-31 00:00:00+00:00”
“2022-08-31 00:00:00+08:00”

在日志中看到如下结果：

可以看到，时区信息得到了有效解析。

奇妙海盐

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
【Go-MySQL】time.Time 和 timestamp 类型的时区问题

日志中的时间参数生成于 time.Time 类型的 String 方法，该方法返回采用如下格式字符串的格式化时间：“2006-01-02 15:04:05.999999999 -0700 MST”，这与日志中的格式是一致的。TIMESTAMP 类型存储的是自 1970-01-01 00:00:01 +0000 UTC 到指定时间经过的秒数，在展示或检索数据时，再转为数据库指定时区的时间，即数据的存储和展示是分离的。因此，并非使用 string 类型不能传递有效的时区信息，而是使用的格式错误。
复制链接

扫一扫