Retain time zone information from postgresql timestamp in go

Question

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.

Answer 1

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;

Answer 2

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.