mysql timestamp error with time.Now() golang

huangapple go评论93阅读模式
英文:

mysql timestamp error with time.Now() golang

问题

如何将time.Now()保存到MySQL表中,列名为created_at timestamp null

我得到了错误:

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

##更多信息如下:(我正在使用fragmenta cms,所以所有引用代码及其行号如下)

表结构:

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    |                |
+----------------------+--------------+------+-----+---------+----------------+

保存代码运行的位置:

在这里的第62行(https://github.com/fragmenta/fragmenta-cms/blob/master/src/pages/actions/setup.go#L62)

它调用了以下代码

user := users.New()

在这个文件的第51行(https://github.com/fragmenta/fragmenta-cms/blob/master/src/users/query.go#L51)

New()函数是设置函数。

它的代码如下:

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
}

它们的连接/MySQL打开模式位于此处(https://github.com/fragmenta/query/blob/master/adapters/database_mysql.go#L23)。

英文:

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 ) .

答案1

得分: 3

https://github.com/fragmenta/query中存在一个错误。query/adapters/database.go中的TimeString方法对于所有的DBMS适配器都无效。

// TimeString - 给定一个时间,返回标准的字符串表示
func (db *Adapter) TimeString(t time.Time) string {
    return t.Format("2006-01-02 15:04:05.000 -0700")
}

对于MySQL的时间戳来说是无效的:MySQL 5.7参考手册,11.3.1 DATE、DATETIME和TIMESTAMP类型query/adapters/database_mysql.go中的MySQL TimeString方法应该是:

// TimeString - 给定一个时间,返回MySQL标准的字符串表示
func (db *MysqlAdapter) TimeString(t time.Time) string {
    return t.Format("2006-01-02 15:04:05.999999")
}
英文:

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")
}

答案2

得分: 2

你正在尝试使用一个字符串来插入它 query.go:36:

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

该字符串是由你正在使用的包生成的 database.go:59:

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

MySQL希望它的格式为yyyy-MM-dd hh:mm:ss,使用以下代码片段将该格式应用于你当前的Time.time对象:

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

不过,插入记录时为什么不使用SQL函数NOW()呢?

英文:

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 ?

huangapple
  • 本文由 发表于 2017年8月6日 03:13:48
  • 转载请务必保留本文链接:https://go.coder-hub.com/45525539.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定