MySQLのtime_zoneとgo-sql-driver/mysqlのlocの関係

2018-10-02 database mysql golang

go-sql-driver/mysql で MySQL に接続するときに適切なパラメータを渡さないと日時が意図しない値になる問題について。

> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.7.21    |
    +-----------+

前提

タイムゾーンがDBにロードされていない場合はロードする。

> select count(*) from mysql.time_zone \\G;
*************************** 1. row ***************************
count(*): 0

$ mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -u root mysql

time_zone はデフォルト値の SYSTEM。つまり JST で、これは my.cnf の default-time-zone で変更できる。関数 NOW() も JST の時間を返している。

> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | JST    |
| time_zone        | SYSTEM |
+------------------+--------+

> SELECT NOW();
mysql> SELECT NOW() \G;
*************************** 1. row ***************************
NOW(): 2018-10-02 20:26:29

DATETIME 型は格納したデータがそのまま返されるのに対して、TIMESTAMP 型は UTC として保持され返すときに time_zone の値に変換される違いがある。したがって後から time_zone を変更すると DATETIME のカラムの値は変わらないが TIMESTAMP は変わる。

> CREATE TABLE t (
    dt DATETIME,
    ts TIMESTAMP
);
> INSERT INTO t VALUES (NOW(), NOW());
> select * from t \G;
*************************** 1. row ***************************
dt: 2018-10-02 20:27:13
ts: 2018-10-02 20:27:13

> SET SESSION time_zone = "UTC";
> select NOW() \G;
*************************** 1. row ***************************
NOW(): 2018-10-02 11:27:56

> select * from t \G;
*************************** 1. row ***************************
dt: 2018-10-02 20:27:13
ts: 2018-10-02 11:27:13

各パラメータの関係

go-sql-driver/mysqlで loc と time_zone を付けてDBに接続し、 MySQLの NOW() の値と Go の time.Now() の値を DATETIME と TIMESTAMP のカラムに格納して出力してみる。 time.Local は UTC でも MySQLの time_zone の JST でもない US/Alaska (-0800) にしている。

package main

import (
    "database/sql"
    "fmt"
    "math"
    "time"

    _ "github.com/go-sql-driver/mysql"
)
    
const format = "2006-01-02 15:04:05 Z0700"
    
func main() {
    var err error
    if time.Local, err = time.LoadLocation("US/Alaska"); err != nil {
        panic(err)
    }
    now := time.Now()
    fmt.Printf("%15s: %s\n", "time.Now()", now.Format(format))
    fmt.Println("* none")
    run(now, "root:@/hoge?parseTime=true")
    fmt.Println("* loc")
    run(now, "root:@/hoge?parseTime=true&loc=Local")
    fmt.Println("* loc & time_zone")
    run(now, "root:@/hoge?parseTime=true&loc=Local&time_zone=%27US%2FAlaska%27")
}

func run(now time.Time, src string) {
    db, err := sql.Open("database", "mysql", src)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    if _, err := db.Exec("DELETE FROM t"); err != nil {
        panic(err)
    }

    if _, err := db.Exec("INSERT INTO t VALUES (NOW(), NOW())"); err != nil {
        panic(err)
    }

    if _, err := db.Exec("INSERT INTO t VALUES (?, ?)", now, now); err != nil {
        panic(err)
    }

    rows, err := db.Query("SELECT dt, ts FROM t")
    if err != nil {
        panic(err)
    }
    i := 0
    title := []string{"NOW()", "time.Now()"}
    for rows.Next() {
        var datetime, timestamp time.Time
        if err := rows.Scan(&datetime, &timestamp); err != nil {
            panic(err)
        }
        fmt.Printf("%15s: %s, %s -> %v\n",
            title[i],
            datetime.Format(format),
            timestamp.Format(format),
            math.Abs(float64(datetime.Unix()-now.Unix())) < 10,
        )
        i++
    }
}

実行結果

time.Now() は 2018-10-02 03:43:13 -0800。

何も指定しない場合

NOW(): 2018-10-02 20:43:13 Z, 2018-10-02 20:43:13 Z -> NG
time.Now(): 2018-10-02 20:43:13 Z, 2018-10-02 20:43:13 Z -> NG

loc は time.Time のタイムゾーンで、付けないと UTC として扱われる。これは MySQL サーバーの time_zone (JST) には影響しないため MySQL の NOW() の値は JST での時間 20:43 となるが、これを取得時にも UTC の値として扱ってしまう。

loc=Local (=US/Alaska)

NOW(): 2018-10-02 20:43:13 -0800, 2018-10-02 20:43:13 -0800 -> NG
time.Now(): 2018-10-02 03:43:14 -0800, 2018-10-02 03:43:14 -> OK

loc を指定すると time.Now() の方は正しく格納され取得できるようになる。

loc=Local & time_zone=US/Alaska

NOW(): 2018-10-02 03:43:13 -0800, 2018-10-02 03:43:13 -0800 -> OK
time.Now(): 2018-10-02 03:43:14 -0800, 2018-10-02 03:43:14 -0800 -> OK

time_zone を渡すと SET time_zone され MySQLの NOW() がそのタイムゾーンの値になる。

DATETIME型と TIMESTAMP型については全ての例において値が一致している。これは格納時と取得時で time_zone を変えていないためで、既に格納されている行の TIMESTAMP カラムを time_stamp を渡さないで取得すると意図した値を返さないことがある。

まとめ

loc=Local は渡さないと格納時にも取得時にも問題が発生するので必須。 NOW() や TIMESTAMP を扱う際は time_zone も指定しないと意図した値が得られないことがある。 time.Local と MySQL の time_zone が一致している場合はこの問題は起こらないが、それに処理が依存しているなら明示的に渡すべきだと考える。