douou8954
2017-08-05 19:13
浏览 77
已采纳

mysql timestamp error with time.Now()Golang

How to save time.Now() in mysql table, column name as created_at timestamp null.

I am getting error :

Error:Error 1292: Incorrect datetime value: '2017-08-05 19:06:14.190 +0000' for column 'created_at' at row 1

More Information as asked :- ( I am using fragmenta cms, so all reference code with their line number is given below )

Table schema :-

mysql> describe users;
+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| id                   | int(11)      | NO   | PRI | NULL    | auto_increment |
| created_at           | timestamp    | YES  |     | NULL    |                |
| updated_at           | timestamp    | YES  |     | NULL    |                |
| status               | int(11)      | YES  |     | NULL    |                |
| role                 | int(11)      | YES  |     | NULL    |                |
| name                 | varchar(250) | YES  |     | NULL    |                |
| email                | varchar(250) | YES  |     | NULL    |                |
| title                | varchar(250) | YES  |     | NULL    |                |
| summary              | text         | YES  |     | NULL    |                |
| text                 | text         | YES  |     | NULL    |                |
| image_id             | int(11)      | YES  |     | NULL    |                |
| password_hash        | varchar(250) | YES  |     | NULL    |                |
| password_reset_token | text         | YES  |     | NULL    |                |
| password_reset_at    | timestamp    | YES  |     | NULL    |                |
+----------------------+--------------+------+-----+---------+----------------+

Code that is running it to save :-

At line no. 62 here ( https://github.com/fragmenta/fragmenta-cms/blob/master/src/pages/actions/setup.go#L62 )

It calls code user := users.New()

in Line no. 51 at file here ( https://github.com/fragmenta/fragmenta-cms/blob/master/src/users/query.go#L51 )

New() function is setup.

Which is like :-

func New() *User {
    user := &User{}
    user.CreatedAt = time.Now()
    user.UpdatedAt = time.Now()
    user.TableName = TableName
    user.KeyName = KeyName
    user.Status = status.Draft
    return user
}

and their connecting / mysql opening pattern is located here ( https://github.com/fragmenta/query/blob/master/adapters/database_mysql.go#L23 ) .

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douquanhui5735 2017-08-06 00:41
    已采纳

    There is a bug in https://github.com/fragmenta/query. The TimeString method in query/adapters/database.go is not valid for all DBMS adapters.

    // TimeString - given a time, return the standard string representation
    func (db *Adapter) TimeString(t time.Time) string {
        return t.Format("2006-01-02 15:04:05.000 -0700")
    }
    

    It's not valid for a MySQL timestamp: MySQL 5.7 Reference Manual, 11.3.1 The DATE, DATETIME, and TIMESTAMP Types. The MySQL TimeString method in query/adapters/database_mysql.go should be:

    // TimeString - given a time, return the MySQL standard string representation
    func (db *MysqlAdapter) TimeString(t time.Time) string {
        return t.Format("2006-01-02 15:04:05.999999")
    }
    
    点赞 打赏 评论
  • dsyq40772 2017-08-05 20:42

    You are trying to insert it using a string query.go:36:

    now := query.TimeString(time.Now().UTC())
    

    that is generated by the package that you are using database.go:59:

    return t.Format("2006-01-02 15:04:05.000 -0700")
    

    MySQL expects it to be in the pattern of yyyy-MM-dd hh:mm:ss, use the following snippet to apply the pattern to you current Time.time object:

    now := time.Now().UTC().Format("2006-01-02 03:04:05")
    

    Anyway, why not to use the SQL function NOW() when inserting the record ?

    点赞 打赏 评论

相关推荐 更多相似问题