英文:
How to setup pgx to get UTC values from DB?
问题
在Postgres中创建的列是:
used_at timestamp with time zone NOT NULL,
在Postgres中保存的值没有时区(使用UTC时间):
2022-06-30 22:49:03.970913+00
使用以下查询:
show timezone
我得到的结果是:
Etc/UTC
但是通过Ent(使用pgx
stdlib
)获取的值是:
2022-07-01T00:49:03.970913+02:00
使用pgdriver
/pq
从数据库中获取的是UTC值。
我该如何设置pgx
以获取UTC值?
我还尝试使用以下连接字符串和代码:
import (
"database/sql"
_ "github.com/jackc/pgx/v4/stdlib"
)
conn, err := sql.Open("pgx", "postgres://postgres:postgres@localhost/project?sslmode=disable&timezone=UTC")
//处理错误
问题仍然存在。
我需要一种方法从数据库中获取UTC值(这些值已经存储在数据库中)。
英文:
The column created in Postgres is:
used_at timestamp with time zone NOT NULL,
The value in Postgres is saved without timezone (in UTC):
2022-06-30 22:49:03.970913+00
Using this query:
show timezone
I get:
Etc/UTC
But from Ent (using pgx
stdlib
) I get the value:
2022-07-01T00:49:03.970913+02:00
Using pgdriver
/pq
I get the UTC value from DB.
How can I setup pgx
to get UTC value?
I tried using this connection string with this code too:
import (
"database/sql"
_ "github.com/jackc/pgx/v4/stdlib"
)
conn, err := sql.Open("pgx", "postgres://postgres:postgres@localhost/project?sslmode=disable&timezone=UTC")
//handle err
The problem is still here.
I need a way to get back from DB the UTC values (that are laready stored in the DB).
答案1
得分: 4
timezone
不是一个有效的参数关键字。
但是,你可以使用 options
关键字来指定连接开始时发送给服务器的命令行选项。只需记住需要对其中的值进行 百分比编码。
设置 TimeZone
的示例:
package main
import (
"context"
"fmt"
"github.com/jackc/pgx/v4"
)
func main() {
ctx := context.Background()
c1, err := pgx.Connect(ctx, "postgres:///?sslmode=disable")
if err != nil {
panic(err)
}
defer c1.Close(ctx)
// 在连接开始时发送 "-c TimeZone=UTC" 给服务器
c2, err := pgx.Connect(ctx, "postgres:///?sslmode=disable&options=-c%20TimeZone%3DUTC")
if err != nil {
panic(err)
}
defer c2.Close(ctx)
var tz1, tz2 string
if err := c1.QueryRow(ctx, "SHOW timezone").Scan(&tz1); err != nil {
panic(err)
}
if err := c2.QueryRow(ctx, "SHOW timezone").Scan(&tz2); err != nil {
panic(err)
}
fmt.Println(tz1)
fmt.Println(tz2)
}
Europe/Prague
UTC
然而,这只是强制连接的时区,似乎对 pgx
从数据库中读取的时间戳的解析没有影响。实际上,它似乎直接或间接地依赖于主机机器的本地时区。为了确认这一点,你可以将全局变量 time.Local
更新为 UTC 并观察差异。
// ...
var t1, t2 time.Time
if err := c1.QueryRow(ctx, "select now()::timestamptz").Scan(&t1); err != nil {
panic(err)
}
if err := c2.QueryRow(ctx, "select now()::timestamptz").Scan(&t2); err != nil {
panic(err)
}
fmt.Println(t1)
fmt.Println(t2)
// 显式设置 Local 为 UTC
time.Local = time.UTC
if err := c1.QueryRow(ctx, "select now()::timestamptz").Scan(&t1); err != nil {
panic(err)
}
if err := c2.QueryRow(ctx, "select now()::timestamptz").Scan(&t2); err != nil {
panic(err)
}
fmt.Println(t1)
fmt.Println(t2)
}
Europe/Prague
UTC
2022-06-27 17:18:13.189557 +0200 CEST
2022-06-27 17:18:13.190047 +0200 CEST
2022-06-27 15:18:13.190401 +0000 UTC
2022-06-27 15:18:13.190443 +0000 UTC
出于明显的原因,我建议避免上述操作。如果 pgx
不提供配置用于解析时间戳的默认位置的方法,那么我能想到的下一个最佳选择是使用自定义的 time.Time
类型。
// ...
var t1, t2 time.Time
if err := c1.QueryRow(ctx, "select now()::timestamptz").Scan(&t1); err != nil {
panic(err)
}
if err := c2.QueryRow(ctx, "select now()::timestamptz").Scan(&t2); err != nil {
panic(err)
}
fmt.Println(t1)
fmt.Println(t2)
var tt1, tt2 myTime
if err := c1.QueryRow(ctx, "select now()::timestamptz").Scan(&tt1); err != nil {
panic(err)
}
if err := c2.QueryRow(ctx, "select now()::timestamptz").Scan(&tt2); err != nil {
panic(err)
}
fmt.Println(tt1)
fmt.Println(tt2)
}
type myTime struct {
time.Time
}
func (tt *myTime) Scan(src interface{}) error {
if t, ok := src.(time.Time); ok {
tt.Time = t.In(time.UTC)
return nil
}
return fmt.Errorf("myTime: unsupported type %T", src)
}
Europe/Prague
UTC
2022-06-27 17:26:45.94049 +0200 CEST
2022-06-27 17:26:45.940959 +0200 CEST
2022-06-27 15:26:45.941321 +0000 UTC
2022-06-27 15:26:45.941371 +0000 UTC
英文:
timezone
is not a valid parameter key word.
You can however use the options
key word to specify command-line options to send to the server at connection start. Just keep in mind that you need to percent encode the values therein.
Example of how to set the TimeZone
:
package main
import (
"context"
"fmt"
"github.com/jackc/pgx/v4"
)
func main() {
ctx := context.Background()
c1, err := pgx.Connect(ctx, "postgres:///?sslmode=disable")
if err != nil {
panic(err)
}
defer c1.Close(ctx)
// sends "-c TimeZone=UTC" to server at connection start
c2, err := pgx.Connect(ctx, "postgres:///?sslmode=disable&options=-c%20TimeZone%3DUTC")
if err != nil {
panic(err)
}
defer c2.Close(ctx)
var tz1, tz2 string
if err := c1.QueryRow(ctx, "SHOW timezone").Scan(&tz1); err != nil {
panic(err)
}
if err := c2.QueryRow(ctx, "SHOW timezone").Scan(&tz2); err != nil {
panic(err)
}
fmt.Println(tz1)
fmt.Println(tz2)
}
Europe/Prague
UTC
However this only enforces the connection's timezone which does not seem to have an effect on how pgx
parses the timestamps themselves once read from the database. In fact it seems it relies, directly or indirectly, on the host machine's local timezone. To confirm that you can update the global time.Local
variable to UTC and observe the difference.
// ...
var t1, t2 time.Time
if err := c1.QueryRow(ctx, "select now()::timestamptz").Scan(&t1); err != nil {
panic(err)
}
if err := c2.QueryRow(ctx, "select now()::timestamptz").Scan(&t2); err != nil {
panic(err)
}
fmt.Println(t1)
fmt.Println(t2)
// explicitly set Local to UTC
time.Local = time.UTC
if err := c1.QueryRow(ctx, "select now()::timestamptz").Scan(&t1); err != nil {
panic(err)
}
if err := c2.QueryRow(ctx, "select now()::timestamptz").Scan(&t2); err != nil {
panic(err)
}
fmt.Println(t1)
fmt.Println(t2)
}
Europe/Prague
UTC
2022-06-27 17:18:13.189557 +0200 CEST
2022-06-27 17:18:13.190047 +0200 CEST
2022-06-27 15:18:13.190401 +0000 UTC
2022-06-27 15:18:13.190443 +0000 UTC
For obvious reasons I'd avoid doing the above. If pgx
doesn't provide a way to configure the default location it uses to parse the timestamps then the next best option, that I can think of, would be to use a custom time.Time
type.
// ...
var t1, t2 time.Time
if err := c1.QueryRow(ctx, "select now()::timestamptz").Scan(&t1); err != nil {
panic(err)
}
if err := c2.QueryRow(ctx, "select now()::timestamptz").Scan(&t2); err != nil {
panic(err)
}
fmt.Println(t1)
fmt.Println(t2)
var tt1, tt2 myTime
if err := c1.QueryRow(ctx, "select now()::timestamptz").Scan(&tt1); err != nil {
panic(err)
}
if err := c2.QueryRow(ctx, "select now()::timestamptz").Scan(&tt2); err != nil {
panic(err)
}
fmt.Println(tt1)
fmt.Println(tt2)
}
type myTime struct {
time.Time
}
func (tt *myTime) Scan(src interface{}) error {
if t, ok := src.(time.Time); ok {
tt.Time = t.In(time.UTC)
return nil
}
return fmt.Errorf("myTime: unsupported type %T", src)
}
Europe/Prague
UTC
2022-06-27 17:26:45.94049 +0200 CEST
2022-06-27 17:26:45.940959 +0200 CEST
2022-06-27 15:26:45.941321 +0000 UTC
2022-06-27 15:26:45.941371 +0000 UTC
答案2
得分: 0
你可以全局配置你的应用程序使用UTC时间:
package main
import "time"
func main() {
location, _ := time.LoadLocation("UTC")
time.Local = location
// 设置数据库连接
// ...
}
这段代码将会将应用程序的时区设置为UTC。
英文:
You can configure your application to use UTC globally:
package main
import "time"
func main() {
location, _ := time.LoadLocation("UTC")
time.Local = location
// setup db connection
// ...
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论