RockOnGom RockOnGom - 1 month ago 10
MySQL Question

Go MySql driver doesn't set time correctly

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/

Answer

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.