英文:
How to convert date string to Time in to insert on mysql driver in Golang?
问题
所以，我的问题是将一个字符串日期（"1941-09-09"）转换为时间类型。
resultInsertUser, err := tx.Exec(`INSERT INTO user
(name, birthdate, password, email, document, phone, is_admin)
VALUES (?, ?, ?, ?, ?, ?, ?)`,
user.Name,
timestamp,
user.Password,
user.Email,
user.Document,
user.Phone,
user.IsAdmin)
我正在使用官方文档中描述的Parse函数，它可以正常工作，但是当我在使用MySQL驱动的插入查询时，它不起作用，下面是错误消息：
"Incorrect datetime value: '1941-09-09 00:00:02' for column 'birthdate' at row 1"
我尝试使用time.Now()代替变量进行测试，结果却可以正常工作，为什么会这样呢？
输出这两个变量：
now := time.Now()
timestamp, _ := time.Parse("2006-01-02", user.Birthdate)
fmt.Printf("now: %v\ntimestamp: %v",
now,
timestamp)
now: 2022-11-05 08:28:59.671061 -0300 -03 m=+3.423587668
timestamp: 1941-09-09 00:00:00 +0000 UTC
有人可以帮我解决这个问题吗？
英文:
So, my problem is to convert a string date ("1941-09-09") to Time type.
resultInsertUser, err := tx.Exec(`INSERT INTO user
(name, birthdate, password, email, document, phone, is_admin)
VALUES (?, ?, ?, ?, ?, ?, ?)`,
user.Name,
timestamp,
user.Password,
user.Email,
user.Document,
user.Phone,
user.IsAdmin)
I'm using Parse function like describe on official documentation and works but, when a using on insert query using MySQL driver doesn't work, error message below 
"Incorrect datetime value: '1941-09-09 00:00:02' for column 'birthdate' at row 1"
I'm trying to use time.Now() instead variable for test and...works, how so?
Output the two variables:
now := time.Now()
timestamp, _ := time.Parse("2006-01-02", user.Birthdate)
fmt.Printf("now: %v\ntimestamp: %v",
now,
timestamp)
now: 2022-11-05 08:28:59.671061 -0300 -03 m=+3.423587668
timestamp: 1941-09-09 00:00:00 +0000 UTC
Does someone help me with this?
答案1
得分: 2
你的代码是正确的。细节问题在于（在mysql文档中）。根据mysql文档，以下是各种格式：
| 数据类型 | 格式 | 范围 |
|---|---|---|
| DATE | YYYY-MM-DD | 1000-01-01 到 9999-12-31 |
| DATETIME | YYYY-MM-DD hh:mm:ss | 1000-01-01 00:00:00 到 9999-12-31 23:59:59 |
| TIMESTAMP | YYYY-MM-DD hh:mm:ss | 1970-01-01 00:00:01 到 2038-01-19 03:14:07（使用UTC，即Unix时间） |
你可能会注意到DATETIME和TIMESTAMP类型，格式相同，但范围不同。TIMESTAMP的范围从1970年开始。
根据你的用例，birthdate列的类型是不正确的。将其从TIMESTAMP更改为DATETIME，因为另一种方式（将1941年转换为相对于1970年的-0029年）在Go中实现不是一个好主意。可能会导致很多繁重的工作。
不确定为什么mysql记录错误，说格式是datetime，而实际上它有一个不同的类型timestamp。用户可能会感到困惑。
英文:
Your code is correct. The devil's in the detail (in mysql docs). Following are the formats as per mysql doc:
| Data Type | Format | Range |
|---|---|---|
| DATE | YYYY-MM-DD | 1000-01-01 to 9999-12-31 |
| DATETIME | YYYY-MM-DD hh:mm:ss | 1000-01-01 00:00:00 to 9999-12-31 23:59:59 |
| TIMESTAMP | YYYY-MM-DD hh:mm:ss | 1970-01-01 00:00:01 to 2038-01-19 03:14:07 (In UTC, i.e., Unix time) |
As you might see DATETIME vs TIMESTAMP types, the formats are same but the ranges differ. The range of TIMESTAMP starts from year 1970.
Looking at your use case, the column birthdate's type is incorrect. Change it from TIMESTAMP -> DATETIME since the other way (converting 1941 as -0029 year relative to 1970) is not a good idea to implement in Go. Could lead to a lot of heavy-lifting.
Not sure why mysql logs the error saying the format is datetime if it altogether has a different type timestamp. Users bound to get confused.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论