如何设置pgx以从数据库获取UTC值?

huangapple go评论101阅读模式
英文:

How to setup pgx to get UTC values from DB?

问题

我正在使用EntPgx

在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值(这些值已经存储在数据库中)。

英文:

I'm using Ent with Pgx.

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
    // ...
}

huangapple
  • 本文由 发表于 2022年6月27日 19:23:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/72771272.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定