英文:
How do I query sql.NullTime value from sql in golang stored in the format 2023-06-08 19:54:41 +0000
问题
currentTime := time.Now().UTC().Format(layout)
prevTime := time.Now().UTC().Add(-time.Minute * 15).Format(layout)
currTime, _ := time.Parse("2006-01-02 15:04:05", currentTime)
previousTime, _ := time.Parse("2006-01-02 15:04:05", prevTime)
query := `
SELECT *
from %s
where
updated_on_utc > %v
and
updated_on_utc <= %v`
query = fmt.Sprintf(query, TableName, previousTime, currTime)
data := db.DbClient.ExecuteQuery(query)
这段代码出现了错误信息：panic: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '19:54:41 +0000 UTC
and
updated_on_utc <= 2023-06-08 20:09:41 +0000 UTC' at line 4
我尝试了不同的查询和不同的占位符，但都出现了相同的错误。
英文:
currentTime := time.Now().UTC().Format(layout)
prevTime := time.Now().UTC().Add(-time.Minute * 15).Format(layout)
currTime, _ := time.Parse("2006-01-02 15:04:05", currentTime)
previousTime, _ := time.Parse("2006-01-02 15:04:05", prevTime)
query := `
SELECT *
from %s
where
updated_on_utc > %v
and
updated_on_utc <= %v`
query = fmt.Sprintf(query, TableName, previousTime, currTime)
data := db.DbClient.ExecuteQuery(query)
this gives out the error message : panic: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '19:54:41 +0000 UTC
and
updated_on_utc <= 2023-06-08 20:09:41 +0000 UTC' at line 4
I have tried different queries with different placeholders but all of them give the same error
答案1
得分: 1
根据你提供的代码，我理解到updated_on_utc列的类型是文本（使用time.Parse函数来提取文本），你需要将查询更改为以下形式：
SELECT *
FROM %s
WHERE
updated_on_utc > "%v"
AND
updated_on_utc <= "%v"
请不要使用字符串格式化（为什么我们应该使用查询参数？），你需要像下面这样使用查询参数：
SELECT *
FROM %s
WHERE
updated_on_utc > ?
AND
updated_on_utc <= ?
然后将你的代码更改如下：
query = fmt.Sprintf(query, "your_table_name")
_, err = db.Query(query, previousTime, currTime)
由于时间在数据库中以字符串形式存储，查询将无法返回所需的结果。因此，你需要将类型更改为datetime或timestamp。
英文:
From the code you mentioned here, I'm understanding that the column updated_on_utc is of type text(time.Parse function is used to retrieve the text), you have to change the query to the following:
SELECT *
from %s
where
updated_on_utc > "%v"
and
updated_on_utc <= "%v"
Please don't use string formatters(Why should we use query parameters?), you need to use query parameters like below:
SELECT *
from %s
where
updated_on_utc > ?
and
updated_on_utc <= ?
Then change your code as below:
query = fmt.Sprintf(query, "your_table_name")
_, err = db.Query(query, previousTime, currTime)
As the time is stored in DB as string, the query won't return the desired result. So you need to change the type to datetime or timestamp.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论