doucheng9304 2017-08-28 20:03
浏览 115
已采纳

在Go中保留Postgresql时间戳中的时区信息

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.

  • 写回答

2条回答 默认 最新

  • doujiling4377 2017-08-29 12:49
    关注

    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;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?