Scanning a timestamp with timezone converts to local timezone

[jrasko]

Describe the bug When scanning a timestamp with a timezone into a time.Time object, the timezone is set to the local time rather than the one used internally in the database (UTC).

To Reproduce

package main

import (
    "context"
    "log"
        "fmt"
    "os"

    "github.com/jackc/pgx/v5"
)

func main() {
    conn, _ := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
    defer conn.Close(context.Background())

    // wirtes correctly 2020-01-01 00:00:00.000000 +00:00 to database
    _, _ = conn.Exec(context.Background(), "INSERT INTO test VALUES ('2020-01-01 00:00:00+00')")

    var t time.Time
    _ = conn.QueryRow(context.Background(), "SELECT date FROM test LIMIT 1").Scan(&t)

    // expected: 2020-01-01 00:00:00.000000 +00:00
    // got local time: 2020-01-01 01:00:00 +0100 CET
    fmt.Println(t)
}

Expected behavior The time is returned in the timezone stored in the database. Since postgres always stores it's timestamps as UTC, this should also return an UTC Value.

Actual behavior The time is returned in the local timezone, as determined by the TZ environment variable used by the time package.

Version

• Go: go version go1.23.0 linux/amd64
• PostgreSQL: PostgreSQL 16.4 (Debian 16.4-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
• pgx: github.com/jackc/pgx/v5 v5.6.0

Additional Information The test table can be created like this:

CREATE TABLE test(
    date TIMESTAMP WITH TIME ZONE
)

The INSERT statement creates the data in UTC.

[jackc]

TIMESTAMP WITH TIME ZONE is misleadingly named. PG does not store time zones in the database. PG converts the time to UTC to save the value and converts it to the client's time zone when returning the value in the text format (in the binary format, which pgx uses, it is always returned in UTC).

If you have a specific time zone you always want your times to be scanned in than you can customize your connections with a new TimestamptzCodec that specifies a different ScanLocation.

If you actually need to store different time zones for different rows then you will need to add a time zone column to your table and convert manually after you retrieve the row.

[jrasko]

@jackc This is correct, but returning a local timestamp when the time stored by the database is in UTC is very misleading. The methods should return the values stored in the database and not do some magic by converting it into local time.

By example (of course completely fictional and did not happen to me yesterday ;D): You have a database and use GORM to access it, which relies on this specific library. Your tests pass in the CI pipeline but fail locally and vice versa due to the time being returned in the local time zone. After debugging for hours through GORM and its drivers, you discover that the issue is triggered by some magic setting the time to your local time zone based on some settings and environment.

[jackc]

There is no setting for this that will satisfy everyone. When using the text protocol, pgx receives the time in a database session's time zone as a string. But when using the binary format we get the the number of microseconds since the PostgreSQL epoch. In both cases (and for slightly different reasons), it's impossible to insert a value and select it back and be guaranteed to get a time.Time back that has the same time zone. There is no default (always use local or always use UTC) that will please everyone. Fortunately, as I mentioned above, there is a setting so you can make it do whatever you want.

Your tests pass in the CI pipeline but fail locally and vice versa due to the time being returned in the local time zone. After debugging for hours through GORM and its drivers, you discover that the issue is triggered by some magic setting the time to your local time zone based on some settings and environment.

That may have been what triggered the issue, but the root cause is incorrect usage of == with time.Time. See the official Go documentation:

From https://pkg.go.dev/time#Time:

Note that the Go == operator compares not just the time instant but also the Location and the monotonic clock reading.

Also from https://pkg.go.dev/time#Time:

In general, prefer t.Equal(u) to t == u, since t.Equal uses the most accurate comparison available and correctly handles the case when only one of its arguments has a monotonic clock reading.

From https://pkg.go.dev/time#Time.Equal:

Equal reports whether t and u represent the same time instant. Two times can be equal even if they are in different locations. For example, 6:00 +0200 and 4:00 UTC are Equal. See the documentation on the Time type for the pitfalls of using == with Time values; most code should use Equal instead.