Feature request: Fetch connection time_zone automatically. #1379
Description
Issue description
Open dsn, if dsn's loc not set, the mysql timestamp field will parse error. just see the sample below.
Example code
mysql
```sql
CREATE TABLE `Test1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
insert into Test1 values (1, '2022-01-01 00:00:00')show VARIABLES like '%time_zone%';the output:
| Variable_name | Value |
|---|---|
| system_time_zone | CST |
| time_zone | +08:00 |
SELECT unix_timestamp(create_time) from Test1 where id = 1;the output
| unix_timestamp(create_time) |
|---|
| 1640966400 |
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"time"
)
func main() {
var user = "user"
var pwd = "password"
var dbName = "dbname"
dsn := fmt.Sprintf("%s:%s@tcp(localhost:3306)/%s?timeout=100s&parseTime=true&interpolateParams=true", user, pwd, dbName)
db, err := sql.Open("mysql", dsn)
if err != nil {
panic(err)
}
defer db.Close()
rows, err := db.Query("select create_time from Test1 limit 1")
if err != nil {
panic(err)
}
for rows.Next() {
t := time.Time{}
rows.Scan(&t)
fmt.Println(t)
fmt.Println(t.Unix())
}
}Error log
2022-01-01 00:00:00 +0000 UTC
1640995200
this is not equal the mysql create_time ->1640966400
reason of the not equal
the error is because, if the dsn's loc is not set, go-sql-driver will use the default loc1, loc2
how does go-sql-driver parse the timestamp field:
- get the timestamp filed
- convert it to
[]byte - use the timestamp pattern -> 0000-00-00 00:00:00.000000 to parse the
[]byte - so get the year, moth, day, hour, min,sec
- finally, use call the time.Date(year, month, day, hour, min,sec, mc.Cfg.Loc) to get time.Time. but the mc.cfg.Loc get from the dsn.
- if dsn's loc not set , will use the default loc -> UTC.
thus, client get the wrong timestamp.
how to fix this
I'm not sure wether it's a bug or design it like this.
but I find java jdbc when connect to msyql server, will query SHOW VARIABLES to get the system_time_zone and time_zone. if user's dsn not set the loc, just replace user jdbc's loc with the mysql server's timezone
go-sql-dirver's source code doesn't has this step, If I can add this feature ?
Looking forward to your reply!
Configuration
Driver version (or git SHA):
github.com/go-sql-driver/mysql v1.7.0
Go version: run go version in your console
go version go1.18.2 darwin/amd64
Server version: E.g. MySQL 5.6, MariaDB 10.0.20
MySQL 5.6
Server OS: E.g. Debian 8.1 (Jessie), Windows 10
macos