英文:
Go MySql driver doesn't set time correctly
问题
我已经在golang中开发了一个与mysql交互的微服务一段时间了，我喜欢这个有才华的语言。不过我遇到了一个问题，不知道问题出在哪里，是在我的代码中，还是在mysql驱动程序中，还是在mysql中。我的机器时区是UTC+3，我分享一些结果，也许可以帮助解决问题。
在mysql中执行：
mysql> select now();
"2016-11-07 22:43:02" //这是正确的。
在go中执行：
fmt.Println(time.Now().Local())
"2016-11-07 22:51:02" //这也是正确的。
但是当我将实体添加到数据库中时，mysql workbench显示了错误的日期时间。
"2016-11-07 19:51:02" //错误的时间
Go代码：
func (this *AppLogHandler) addLog(_log *AppLog) (int64, error){
fmt.Println("addLog")
db := this.Context.DB
stmt, err := db.Prepare("INSERT tbl_logs SET user_id=?,ip_proxy=?, ip_original=?, end_point=?, http_method=?, message=?, status=?, created_date=?")
if err != nil {
log.Println(err)
return -1, err
}
defer stmt.Close()
res, err := stmt.Exec(&_log.UserID, &_log.IPProxy, &_log.IPOriginal, &_log.Endpoint, &_log.HttpMethod, &_log.Message, &_log.Status, &_log.CreatedDate)
if err != nil {
log.Println(err)
return -1, err
}
return res.LastInsertId()
}
//一些代码在这里
app_log := AppLog{}
app_log.IPProxy = r.RemoteAddr
app_log.IPOriginal = r.Header.Get("X-Forwarded-For")
app_log.CreatedDate = time.Now().Local()
app_log.UserID = user_id
app_log.Endpoint = r.URL.Path
app_log.HttpMethod = r.Method
fmt.Println(app_log.CreatedDate)
return this.addLog(&app_log)
所以，我需要你们的帮助。我已经花了几个小时都解决不了这个问题。
mysql版本：Ver 14.14 Distrib 5.7.15, for osx10.11 (x86_64) using EditLine wrapper
go版本：1.7
mysql驱动程序：1.2，https://github.com/go-sql-driver/mysql/
英文:
I have been developing a micro service that interact mysql for a while in golang, and i love this talented language. Anyway have a problem and do not know where is the problem, in my code, in mysql driver else in mysql. So my machine timezone utc+3, i am sharing some result may be it helps
//created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
mysql> select now();
"2016-11-07 22:43:02", //that is correct.
in go
fmt.PrintLn(time.Now().Local())
"2016-11-07 22:51:02" //that is correct too
But when i added the entity into db, mysql workbench shows me wrong datetime.
"2016-11-07 19:51:02" //
Go code:
func (this *AppLogHandler) addLog(_log *AppLog) (int64, error){
fmt.Println("addLog")
db:= this.Context.DB
stmt, err := db.Prepare("INSERT tbl_logs SET user_id=?,ip_proxy=?, ip_original=?, end_point=?, http_method=?, message=?, status=?, created_date=?")
if(err != nil){
log.Println(err)
return -1, err
}
defer stmt.Close()
res, err := stmt.Exec(&_log.UserID, &_log.IPProxy, &_log.IPOriginal, &_log.Endpoint, &_log.HttpMethod, &_log.Message, &_log.Status, &_log.CreatedDate)
if(err != nil){
log.Println(err)
return -1, err
}
return res.LastInsertId()
}
/// some code here
app_log := AppLog{}
app_log.IPProxy = r.RemoteAddr
app_log.IPOriginal = r.Header.Get("X-Forwarded-For")
app_log.CreatedDate = time.Now().Local()
app_log.UserID = user_id
app_log.Endpoint = r.URL.Path
app_log.HttpMethod = r.Method
fmt.Println(app_log.CreatedDate)
return this.addLog(&app_log)
So guys i need your helps. I couldn't solve the problem for hours.
mysql=> Ver 14.14 Distrib 5.7.15, for osx10.11 (x86_64) using EditLine wrapper
go => 1.7
mysql driver => 1.2, https://github.com/go-sql-driver/mysql/
答案1
得分: 10
mysql驱动程序有一个默认时区的配置参数，你可以将其设置为time.Local（默认为time.UTC）。当你保存值时，它首先将时间戳转换为UTC时区，然后将其发送到数据库。
正如在评论中已经提到的，一个更可靠的方法是接受默认的Loc，并在数据库中使用UTC进行标准化。这样可以大大简化后续的日期计算，并且在显示值时，不会对查看数据的人的时区做出假设，只需将其从UTC转换为本地时区即可。
英文:
The mysql driver has a configuration parameter for the default time zone, which you can set to time.Local (the default is time.UTC). When you are saving the value, it first converts the time stamp to the UTC time zone and then sends it off to the database.
As has been already stated in the comments, a much more robust approach would be to accept the default Loc, and standardize on UTC in the database. This greatly simplifies anything having to do with date math further down the line, and doesn't make assumptions about the time zone of the person viewing the data if you just convert it from UTC to local when displaying the value.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论