英文:
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 ?
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论