How to setup pgx to get UTC values from DB?

Question

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).

Answer 1

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

Answer 2

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