在Go语言中保留来自PostgreSQL时间戳的时区信息。

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

Retain time zone information from postgresql timestamp in go

问题

我有一个带有日期和repeat_until列的postgresql数据库,类型为timestamp with time zone。示例日期具有特定的时区格式。后者是冬令时。

2017-08-28 09:00:00+022017-12-31 23:00:00+01

使用字符串和time.Time,第一个给出相对于GMT+0的时间,后者给出秒数(而不是Unix时间戳)。

import (
    _ "github.com/lib/pq"
    "fmt"
    "github.com/gorilla/mux"
    "github.com/jmoiron/sqlx"
    "log"
    "net/http"
    "time"
)

type Event struct {
    Date        string
    RepeatUntil time.Time `db:"repeat_until"`
}

event := Event{}
rows, _ := db.Queryx("select * from events order by date")
for rows.Next() {
    err := rows.StructScan(&event)
    if err != nil {
        log.Fatalln(err)
    }
    fmt.Printf("%#v", event)
}

Date:"2017-08-28T07:00:00Z"

RepeatUntil:time.Time{sec:63650354400, nsec:0, loc:(*time.Location)(nil)}

保留时区信息的推荐方法是什么?time.Time似乎是显而易见的选择,但我不确定它是如何变成秒数的,而且秒数对应的是Unix时间戳中的3986年。

我正在使用sqlx。

英文:

I have a postgresql db with the columns date and repeat_until as timestamp with time zone. The example dates have a time zone specific format. The latter is winter time.

2017-08-28 09:00:00+02, 2017-12-31 23:00:00+01

Using string and time.Time the first gives the time relative to GMT+0, the latter seconds (not unix timestamp).

import (
    _ "github.com/lib/pq"
    "fmt"
    "github.com/gorilla/mux"
    "github.com/jmoiron/sqlx"
    "log"
    "net/http"
    "time"
)

type Event struct {
    Date            string
    RepeatUntil     time.Time   `db:"repeat_until"`
}


event := Event{}
rows, _ := db.Queryx("select * from events order by date")
for rows.Next() {
    err := rows.StructScan(&event)
    if err != nil {
        log.Fatalln(err)
    }
    fmt.Printf("%#v", event)
}

Date:"2017-08-28T07:00:00Z"

RepeatUntil:time.Time{sec:63650354400, nsec:0, loc:(*time.Location)(nil)}

What is the recommended way to retain time zone information? time.Time seems obvious but I am not sure how it got to seconds which is in year 3986 in unixtime.

I'm using sqlx.

答案1

得分: 6

PostgreSQL中的时区是会话参数,即可以为每个会话(连接)指定时区。如果不指定时区,将从pg_hba.conf中的设置参数推断时区。当您选择记录时,日期/时间数据将自动从数据库时区转换为会话(连接)时区。

在您的情况下,要获取特定时区的时间,请在连接参数中明确指定,例如:

psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
	"dbname=%s sslmode=disable TimeZone=Europe/Paris",
	host, port, user, dbname)
db, err := sqlx.Open("postgres", psqlInfo)

可以通过以下方式获取TimeZone

select * from pg_timezone_names;
英文:

The timezone in PostgreSQL is session parameter, i.e. it can be specified for each session (connection). If you don't specify it, the timezone will be inferred from setting parameters in pg_hba.conf. When you select records, the date/time data then will be converted automatically from database timezone to session (connection) timezone.

In your case, to get the time in specific timezone, specify it explicitly in the connection parameters, e.g.

psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
	"dbname=%s sslmode=disable TimeZone=Europe/Paris",
	host, port, user, dbname)
db, err := sqlx.Open("postgres", psqlInfo)

The TimeZone can be obtained by

select * from pg_timezone_names;

答案2

得分: 1

时区可以在单个连接期间进行配置,也可以通过更改数据库中的时区设置来进行配置。

alter database foo set timezone to 'Europe/Oslo';

这将返回按该时区格式化的日期。

英文:

The timezone can be configured during a single connection or by altering the timezone setting in the database.

alter database foo set timezone to 'Europe/Oslo';

This will return the date formatted for that timezone.

huangapple
  • 本文由 发表于 2017年8月29日 04:03:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/45926555.html
匿名

发表评论

匿名网友

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

确定