go-sql-driver/mysql で MySQL に接続するときに適切なパラメータを渡さないと日時が意図しない値になる問題について確認する。
$ cat docker-compose.yml
services:
db:
image: mysql:8.0
container_name: testdb
platform: linux/arm64
restart: always
environment:
MYSQL_ROOT_PASSWORD: test
MYSQL_DATABASE: testdb
TZ: Asia/Tokyo
ports:
- "3306:3306"
$ docker-compose up -d
$ docker exec -it testdb mysql -h127.0.0.1 -P3306 -uroot -ptest
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.39 |
+-----------+
1 row in set (0.00 sec)
前提
もしタイムゾーンがDBにロードされていない場合はロードする。
mysql> select count(*) from mysql.time_zone;
$ mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -u root mysql
time_zone はデフォルト値の SYSTEM (=JST)。これは my.cnf の default-time-zone と対応している。 関数 NOW() も JST の時間を返している。
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | JST |
| time_zone | SYSTEM |
+------------------+--------+
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2018-10-02 20:26:29 |
+---------------------+
1 row in set (0.00 sec)
DATETIME 型は格納した日時がそのまま返されるのに対して、TIMESTAMP 型は UTC として保持されて返すときに time_zone での値に変換される違いがある。 これにより後から time_zone を変更すると DATETIME のカラムの値は変わらないが TIMESTAMP のカラムの値は変わることになる。
mysql> CREATE TABLE t (
dt DATETIME,
ts TIMESTAMP
);
mysql> INSERT INTO t VALUES (NOW(), NOW());
mysql> select * from t;
+---------------------+---------------------+
| dt | ts |
+---------------------+---------------------+
| 2018-10-02 20:27:13 | 2018-10-02 20:27:13 |
+---------------------+---------------------+
1 row in set (0.00 sec)
MySQL> SET SESSION time_zone = "UTC";
mysql> select NOW();
+---------------------+
| NOW() |
+---------------------+
| 2018-10-02 11:27:56 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from t;
+---------------------+---------------------+
| dt | ts |
+---------------------+---------------------+
| 2018-10-02 20:27:13 | 2018-10-02 11:27:13 |
+---------------------+---------------------+
1 row in set (0.00 sec)
実験
go-sql-driver/mysqlで loc と time_zone を付けてDBに接続し、 MySQL の NOW() の値と Go の time.Now() の値を DATETIME と TIMESTAMP のカラムに格納して値を確認する。なお、time.Now() のタイムゾーンである 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("%s: %s\n", "time.Now()", now.Format(format))
fmt.Println("default insert, get")
defaultSrc := "root:test@tcp(127.0.0.1:3306)/testdb?parseTime=true"
insert(now, defaultSrc)
get(now, defaultSrc)
fmt.Println("insert(loc=Local), get(loc=Local)")
locSrc := "root:test@tcp(127.0.0.1:3306)/testdb?parseTime=true&loc=Local"
insert(now, locSrc)
get(now, locSrc)
fmt.Println("insert(), get(loc=Local)")
insert(now, defaultSrc)
get(now, locSrc)
fmt.Println("insert(loc=Local&time_zone=US/Alaska), get(loc=Local&time_zone=US/Alaska)")
locTimeZoneSrc := "root:test@tcp(127.0.0.1:3306)/testdb?parseTime=true&loc=Local&time_zone=%27US%2FAlaska%27"
insert(now, locTimeZoneSrc)
get(now, locTimeZoneSrc)
fmt.Println("insert(loc=Local&time_zone=US/Alaska), get(loc=Local)")
insert(now, locTimeZoneSrc)
get(now, locSrc)
fmt.Println("insert(loc=Local), get(loc=Local&time_zone=US/Alaska)")
insert(now, locSrc)
get(now, locTimeZoneSrc)
}
func insert(now time.Time, src string) {
db, err := sql.Open("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)
}
}
func get(now time.Time, src string) {
db, err := sql.Open("mysql", src)
if err != nil {
panic(err)
}
defer db.Close()
rows, err := db.Query("SELECT dt, ts FROM t")
if err != nil {
panic(err)
}
i := 0
title := []string{"MySQL NOW() → Go", "Go time.Now() → MySQL → Go"}
for rows.Next() {
var datetime, timestamp time.Time
if err := rows.Scan(&datetime, ×tamp); err != nil {
panic(err)
}
fmt.Printf("\t%s:\n", title[i])
fmt.Printf("\t\tdatetime: %s → OK: %v\n",
datetime.Format(format),
math.Abs(float64(datetime.Unix()-now.Unix())) < 10,
)
fmt.Printf("\t\ttimestamp: %s → OK: %v\n",
timestamp.Format(format),
math.Abs(float64(timestamp.Unix()-now.Unix())) < 10,
)
i++
}
}
結果
time.Now(): 2018-10-02 03:43:13 -0800
loc は time.Time の タイムゾーンで、渡さないと UTC になる。 time_zone を渡さずに実行するとMySQL の NOW() の値が JST のものになるが、 これをそのまま loc のタイムゾーンのものとして扱ってしまうため次のように誤った値が返る。
default insert, get
MySQL NOW() → Go:
datetime: 2018-10-02 20:43:13 Z → OK: false
timestamp: 2018-10-02 20:43:13 Z → OK: false
Go time.Now() → MySQL → Go:
datetime: 2018-10-02 11:43:13 Z → OK: true
timestamp: 2018-10-02 11:43:13 Z → OK: true
loc を渡すと time.Time の タイムゾーンが変わることが確認できる。 また、取得時のみ loc を渡すと上の例と同じ時刻でタイムゾーンのみを変えた誤った値が返る。 これにより DB には insert 時の loc での時刻が格納されていることが分かる。
insert(loc=Local), get(loc=Local)
MySQL NOW() → Go:
datetime: 2018-10-02 20:43:13 -0800 → OK: false
timestamp: 2018-10-02 20:43:13 -0800 → OK: false
Go time.Now() → MySQL → Go:
datetime: 2018-10-02 03:43:13 -0800 → OK: true
timestamp: 2018-10-02 03:43:13 -0800 → OK: true
insert(), get(loc=Local)
MySQL NOW() → Go:
datetime: 2018-10-02 20:43:13 -0800 → OK: false
timestamp: 2018-10-02 20:43:13 -0800 → OK: false
Go time.Now() → MySQL → Go:
datetime: 2018-10-02 11:43:13 -0800 → OK: false
timestamp: 2018-10-02 11:43:13 -0800 → OK: false
time_zone を渡すと NOW() の値も正しくなる。
insert(loc=Local&time_zone=US/Alaska), get(loc=Local&time_zone=US/Alaska)
MySQL NOW() → Go:
datetime: 2018-10-02 03:43:13 -0800 → OK: true
timestamp: 2018-10-02 03:43:13 -0800 → OK: true
Go time.Now() → MySQL → Go:
datetime: 2018-10-02 03:43:13 -0800 → OK: true
timestamp: 2018-10-02 03:43:13 -0800 → OK: true
格納時と取得時の time_zone が異なる場合はややこしい。 まず NOW() について、DATETIME 型の場合は格納時の time_stamp で値が決まるが、TIMESTAMP 型の場合は UTC に変換して格納されるため、逆に取得時の time_stamp が loc と一致していることが重要になる。 time.Now() については DATETIME 型には影響がないが、TIMESTAMP 型の値が変換される際におかしくなる。
insert(loc=Local&time_zone=US/Alaska), get(loc=Local)
MySQL NOW() → Go:
datetime: 2018-10-02 03:43:13 -0800 → OK: true
timestamp: 2018-10-02 20:43:13 -0800 → OK: false
Go time.Now() → MySQL → Go:
datetime: 2018-10-02 03:43:13 -0800 → OK: true
timestamp: 2018-10-02 20:43:13 -0800 → OK: false
insert(loc=Local), get(loc=Local&time_zone=US/Alaska)
MySQL NOW() → Go:
datetime: 2018-10-02 20:43:13 -0800 → OK: false
timestamp: 2018-10-02 03:43:13 -0800 → OK: true
Go time.Now() → MySQL → Go:
datetime: 2018-10-02 03:43:13 -0800 → OK: true
timestamp: 2018-10-01 10:43:13 -0800 → OK: false
まとめ
アプリケーションのタイムゾーンと、DBのタイムゾーンが同じ場合、loc=Loc だけ渡せば問題は起きない。同じである保証がない場合は time_zone も指定しないと NOW() や TIMESTAMP 型のカラムの値がおかしくなることがある。