RockOnGom RockOnGom - 11 months ago 51
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

"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){
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){
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){
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
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,

Answer Source

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.