How do you set the timezone connection variable?

[kellabyte]

I'm trying to set the session timezone variable that Postgres allows in the connection string but I'm not familiar how to do it using pgx. I tried to do the following below but it did not work.

runtimeParams := make(map[string]string)
runtimeParams["timezone"] = "UTC"

config := pgx.ConnConfig{
    Host:          host,
    Port:          port,
    User:          user,
    Password:      password,
    Database:      database,
    RuntimeParams: runtimeParams,
}

[jackc]

Works for me.

package main

import (
    "log"
    "os"

    "github.com/jackc/pgx"
)

func main() {
    config, err := pgx.ParseURI(os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }

    if s := os.Getenv("PGX_TIMEZONE"); s != "" {
        config.RuntimeParams["timezone"] = s
    }

    conn, err := pgx.Connect(config)
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close()

    var tz string
    err = conn.QueryRow("select current_setting('TIMEZONE')").Scan(&tz)
    if err != nil {
        log.Fatal(err)
    }
    log.Println("select current_setting('TIMEZONE') => ", tz)
}

Example runs:

jack@happy:~/go/src/github.com/jackc/pgx-520$ go run main.go 
2019/03/23 12:03:20 select current_setting('TIMEZONE') =>  localtime
jack@happy:~/go/src/github.com/jackc/pgx-520$ PGX_TIMEZONE=UTC go run main.go 
2019/03/23 12:03:31 select current_setting('TIMEZONE') =>  UTC
jack@happy:~/go/src/github.com/jackc/pgx-520$ PGX_TIMEZONE=Europe/Berlin go run main.go 
2019/03/23 12:03:43 select current_setting('TIMEZONE') =>  Europe/Berlin

[LeKovr]

Works for me.

How to get correct timestamp with this setting?

package main

import (
    "log"
    "os"
    "time"

    "github.com/jackc/pgx"
)

func main() {
    config, err := pgx.ParseURI(os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }

    if s := os.Getenv("PGX_TIMEZONE"); s != "" {
        config.RuntimeParams["timezone"] = s
    }

    conn, err := pgx.Connect(config)
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close()

    var tz string
    var tm time.Time
    err = conn.QueryRow("select current_setting('TIMEZONE'), now()").Scan(&tz, &tm)
    if err != nil {
        log.Fatal(err)
    }
    log.Printf("TIMEZONE: %s, NOW %v\n", tz, tm)
}

Shows:

$ go run main.go
2019/04/03 23:43:11 TIMEZONE: UTC, NOW 2019-04-03 23:43:11.741484 +0300 MSK
$ PGX_TIMEZONE=Europe/Berlin go run main.go
2019/04/03 23:43:12 TIMEZONE: Europe/Berlin, NOW 2019-04-03 23:43:12.097781 +0300 MSK

Meanwhile this gives correct timestamp (but not setting):

TZ=Europe/Berlin go run main.go
2019/04/03 22:43:12 TIMEZONE: UTC, NOW 2019-04-03 22:43:12.454271 +0200 CEST

[jackc]

That is because of different time zones in Go and PostgreSQL. The timestamp with time zone type in PostgreSQL actually doesn't have time zone information. It converts from the client time zone to UTC on the way in and from UTC to the client time zone on the way out. But all it is internally is an int64 of the number of microseconds from 2000-01-01 00:00:00 UTC. And that is what pgx gets over the wire from PostgreSQL (using the binary protocol which is the default).

If you want to display it in a different time zone in Go you will need to convert it there. (e.g. https://golang.org/pkg/time/#Time.In).

[LeKovr]

Yes, Time.In() allows to change TZ per var, but how to change all of them?

With postgresql clients like psql I can set timezone once and get all of timestamptz values in that timezone. With pgx I've found TZ=.. go run but might be there is a way better?

[jackc]

The TZ environment variable is probably what you want then.

[LeKovr]

So, there are 2 ways to show pgx result's timestamptz in some requested timezone:

1. applicatiion-wide, using TZ as described in time package sources
2. per value, using time.In()

and this problem is not related with RuntimeParams["timezone"]

Am I right?

[jackc]

Correct.

[LeKovr]

Ok, thank you. Sorry if my posts were an offtopic for this issue

[suryapandian]

That is because of different time zones in Go and PostgreSQL. The timestamp with time zone type in PostgreSQL actually doesn't have time zone information. It converts from the client time zone to UTC on the way in and from UTC to the client time zone on the way out. But all it is internally is an int64 of the number of microseconds from 2000-01-01 00:00:00 UTC. And that is what pgx gets over the wire from PostgreSQL (using the binary protocol which is the default).

If you want to display it in a different time zone in Go you will need to convert it there. (e.g. https://golang.org/pkg/time/#Time.In).

Does this mean that even if we explicitly query using AT TIME ZONE 'utc' i.e)

select valid_from AT TIME ZONE 'utc' FROM sometable;

pgx anyways scans and returns to me in my local timezone unless the appropriate environment variable is added?.

[jackc]

at time zone converts a timestamp with time zone to a timestamp without time zone in the specified zone.

e.g.

jack@[local:/private/tmp]:5432 jack=# select pg_typeof(now()),  pg_typeof(now() AT TIME ZONE 'utc');
        pg_typeof         │          pg_typeof
──────────────────────────┼─────────────────────────────
 timestamp with time zone │ timestamp without time zone

[suryapandian]

Thanks @jackc , makes sense now.

[AlexanderMatveev]

How to deal with timestamp without time zone type? I expected pgx to scan them to time.Time struct using current PostgreSQL connection client timezone, but it gives UTC:

package main

import (
    "context"
    "github.com/jackc/pgx/v4"
    "github.com/joho/godotenv"
    "log"
    "os"
    "time"
)

func main() {

    if err := godotenv.Load(".env.local"); err != nil {
        log.Fatal(err)
    }

    conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }

    var tz string
    var tm time.Time
    err = conn.QueryRow(context.Background(), "select current_setting('TIMEZONE'), now()").Scan(&tz, &tm)
    if err != nil {
        log.Fatal(err)
    }
    log.Printf("current_setting('TIMEZONE'): %s, now(): %v\n", tz, tm)

    var typeOf string
    err = conn.QueryRow(context.Background(), "select pg_typeof(created_at), created_at from review order by created_at desc limit 1").Scan(&typeOf, &tm)
    if err != nil {
        log.Fatal(err)
    }
    log.Printf("pg_typeof(created_at): %v, created_at: %v\n", tm, typeOf
}

2021/02/01 00:10:04 current_setting('TIMEZONE'): Europe/Moscow, now(): 2021-02-01 00:10:04.366656 +0300 MSK 2021/02/01 00:10:04 pg_typeof(created_at): 2021-01-18 23:31:13 +0000 UTC, created_at: timestamp without time zone

[AlexanderMatveev]

So I have to do this ugly timezone update after scanning:

tm, err = time.ParseInLocation(time.ANSIC, tm.Format(time.ANSIC), time.Local)