英文:
postgres query not working with time format "2005-06-10 23:00:00 +0000 UTC"
问题
我来帮你翻译一下：
我的PostgreSQL表的模式如下：
| 列名 | 类型 | |
|---|---|---|
| id | int | |
| name | string | |
| created | timestamp without timezone |
现在我想要获取在某个特定时间之后创建的记录。如果我在查询中使用以下条件，它可以正常工作：
created > 2009-11-10 23:00:00 +0000
但是当我使用以下时间戳字符串时，它似乎无法正常工作：
created > 2009-11-10 23:00:00 +0000 UTC
上述格式是在Go中将time.Time对象转换为字符串时获得的默认格式。PostgreSQL是否不支持或无法理解后一种格式？
英文:
Schema of my postgres table
| column | type | |
|---|---|---|
| id | int | |
| name | string | |
| created | timestamp without timezone |
Now i am trying to fetch records which were created after a certain time. In my query if I write condition as below , it works flawlessly
created > 2009-11-10 23:00:00 +0000
but when i write the below timestamp string, it does not seems to work
created > 2009-11-10 23:00:00 +0000 UTC
The above is the default format which is obtained when i convert a time.Time object to string in Go. Does postgres not support or understand the latter format?
答案1
得分: 1
不要使用time.Time.String()，因为"返回的字符串是用于调试的"。没有理由期望Go对于时间值的调试表示与Postgres（或任何其他软件）理解的内容匹配。
让数据库驱动程序负责如何编码时间值，通过在查询中使用占位符，并将time.Time值原样传递：
var t time.Time = ...
rows, err := db.Query("SELECT * FROM table WHERE created > $1", t)
// 由于您使用的是没有时区的时间戳，您可能希望使用t.UTC()、t.Local()或t.In(location)而不仅仅是t。
英文:
Do not use time.Time.String() because "the returned string is meant for debugging." There is no reason to expect Go's debugging representation for Time values to match anything Postgres (or any other software, for that matter) understands.
Let the database driver worry about how to encode Time values by using a placeholder in the query and passing the time.Time value as-is:
var t time.Time = ...
rows, err := db.Query("SELECT * FROM table WHERE created > $1", t)
// Since you're using timestamp without timezone you may want to use t.UTC(), t.Local(), or t.In(location) instead of just t.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论