Time Zones and Timestamps in Go SQL Code Explained

Time zone bugs have a particular character: they are invisible on the developer's machine, appear in production when users are in other zones, and fix themselves twice a year when the clocks change. The database and Go each have a model of time, and the bugs live in the gap between them. This article shows the gap with PostgreSQL 18 and Go 1.25.6, so you can choose the types and the policy that keep both sides in agreement.

Two PostgreSQL types

timestamp (without time zone) stores a calendar date and wall clock time, like 2026-03-01 09:00:00. It carries no zone: it says nothing about which 9 AM. timestamptz (with time zone) stores one instant, internally as UTC, and converts to and from the session's TimeZone setting on input and output. Despite its name it does not store the zone; it stores the moment.

The rule used by nearly every team that has been bitten: use timestamptz for anything that happened or will happen at a moment in time: created, updated, scheduled, expired. Use plain timestamp only for deliberately zone-less wall clock values, such as "the shop opens at 09:00 local time wherever the shop is", and store the zone separately.

The experiment

package main

import (
	"context"
	"fmt"
	"log"
	"time"

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

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	_, err = pool.Exec(ctx, `drop table if exists t598; create table t598 (id int, plain timestamp, tz timestamptz)`)
	if err != nil {
		log.Fatal(err)
	}

	tokyo, _ := time.LoadLocation("Asia/Tokyo")
	when := time.Date(2026, 3, 1, 9, 0, 0, 0, tokyo) // 00:00 UTC
	fmt.Println("Go value:                ", when, "| UTC:", when.UTC().Format(time.RFC3339))

	_, err = pool.Exec(ctx, `insert into t598 values (1, $1, $2)`, when, when)
	if err != nil {
		log.Fatal(err)
	}

	// Read back with different session time zones.
	for _, zone := range []string{"UTC", "Asia/Tokyo", "America/New_York"} {
		conn, _ := pool.Acquire(ctx)
		_, _ = conn.Exec(ctx, fmt.Sprintf(`set time zone '%s'`, zone))
		var plain, tz time.Time
		var plainText, tzText string
		_ = conn.QueryRow(ctx, `select plain, tz, plain::text, tz::text from t598`).Scan(&plain, &tz, &plainText, &tzText)
		fmt.Printf("session %-17s timestamp=%s  timestamptz=%s  (Go sees tz as %s)\n", zone, plainText, tzText, tz.UTC().Format("15:04 UTC"))
		_ = plain
		conn.Release()
	}

	// What Go gets back: the instant is right, the location is not the original.
	var back time.Time
	_ = pool.QueryRow(ctx, `select tz from t598`).Scan(&back)
	fmt.Printf("scanned timestamptz equals the original instant: %v, same location name: %v\n", back.Equal(when), back.Location() == when.Location())

	// A plain timestamp has no zone: pgx stores the UTC wall clock and reads it back as UTC.
	var plain time.Time
	_ = pool.QueryRow(ctx, `select plain from t598`).Scan(&plain)
	fmt.Printf("scanned timestamp (no zone): %s in %s\n", plain.Format("2006-01-02 15:04"), plain.Location())
	_, _ = pool.Exec(ctx, `drop table t598`)
}

go run . 2>&1

Go value:                 2026-03-01 09:00:00 +0900 JST | UTC: 2026-03-01T00:00:00Z
session UTC               timestamp=2026-03-01 09:00:00  timestamptz=2026-03-01 00:00:00+00  (Go sees tz as 00:00 UTC)
session Asia/Tokyo        timestamp=2026-03-01 09:00:00  timestamptz=2026-03-01 09:00:00+09  (Go sees tz as 00:00 UTC)
session America/New_York  timestamp=2026-03-01 09:00:00  timestamptz=2026-02-28 19:00:00-05  (Go sees tz as 00:00 UTC)
scanned timestamptz equals the original instant: true, same location name: false
scanned timestamp (no zone): 2026-03-01 09:00 in UTC

The program builds a time in Tokyo, 2026-03-01 09:00 JST, which is 2026-03-01T00:00:00Z in UTC. It inserts that one value into both column types of one row, then reads the row under three session time zones.

timestamptz follows the session zone.

session UTC               timestamptz=2026-03-01 00:00:00+00
session Asia/Tokyo        timestamptz=2026-03-01 09:00:00+09
session America/New_York  timestamptz=2026-02-28 19:00:00-05

One stored instant, three renderings. The New York one is the previous day. None is wrong. psql shows you the text in your session's zone, which is why two engineers see different times for the same row and argue about it.

timestamp does not. Under all three sessions, the plain column reads 2026-03-01 09:00:00. There is no zone to convert, so it prints as stored. Which wall clock was stored depends on what the driver sent, and that is where the bug sits.

What reaches Go

Scanning the timestamptz column into a time.Time, the program checks two properties:

scanned timestamptz equals the original instant: true, same location name: false

back.Equal(when) is true: the instant survived exactly. But the Location is not Tokyo. pgx returns the time in the local zone of the Go process (or UTC, depending on version and configuration), because the database never stored Asia/Tokyo. If your code formats the time for display it must apply the user's zone itself: t.In(userLocation). Never assume the zone that came back is the zone it went in.

For the plain timestamp, the result is more dangerous:

scanned timestamp (no zone): 2026-03-01 09:00 in UTC

Look at what happened. The Go value was 09:00 in Tokyo. The driver wrote the wall clock reading 2026-03-01 09:00:00 into the timestamp column (it ignores the zone for this type). On the way back, the driver has no zone to attach, so it labels the value UTC. The result is 09:00 UTC, which is nine hours later than the original instant (00:00 UTC). A time went in as Tokyo morning and came out as a UTC morning. No error was raised; the data is just wrong. If the program ran on a machine in London, the same code would produce a different shift.

This is the standard failure of timestamp without time zone: the type has no memory of the zone, and the program has to supply a convention. The convention that works is "always UTC": convert to UTC before writing (t.UTC()) and treat what you read as UTC. Then plain timestamp is safe, if redundant; timestamptz makes the convention the database's problem and is the better default.

A policy that works

1. Store instants as timestamptz. In Go, hold them as time.Time.
2. Do all arithmetic and storage in UTC. Call .UTC() on a time at the boundary (when it enters the system) and convert to a zone only when rendering.
3. Set the session zone to UTC in the connection config (options=-c timezone=UTC in the DSN, or alter role app set timezone = 'UTC'). Then text output from SQL, now()::text and casts behave identically everywhere, and reports do not shift with the DBA's laptop.
4. Store the user's zone as data, an IANA name such as Asia/Tokyo in a text column, and apply it when displaying with time.LoadLocation. Do not store offsets (+09:00); they do not follow daylight saving changes.
5. For future local events, such as "9 AM in Berlin next March", store the wall clock plus the zone name, not a UTC instant. Governments change the rules for daylight saving, and an instant computed years ahead can be wrong; the wall clock and zone are the intent.
6. Truncate precision on purpose. PostgreSQL keeps microseconds; Go keeps nanoseconds. A round trip loses the last three digits, so t.Equal on a value read back can fail; compare with t.Truncate(time.Microsecond) in tests.
7. Test across a daylight saving boundary, with a fixed clock, as in the determinism article.

MySQL and SQLite

MySQL's DATETIME is like timestamp: no zone. Its TIMESTAMP converts to and from the session zone like timestamptz, but has a range that ends in 2038. The Go driver needs parseTime=true and a loc setting, as in connecting to MySQL. SQLite has no time type: it stores text, real or integer, and the driver chooses a format. Store UTC strings in ISO 8601 form or Unix integers and be consistent.

Common mistakes

• timestamp for events. The shift in the lab is the consequence.
• time.Now() in a local zone stored in a plain timestamp. Works on the developer's machine, breaks in a container with a different TZ.
• Comparing timestamp with timestamptz in SQL. PostgreSQL converts using the session zone, so the comparison changes with the session.
• Formatting a scanned time without In(). Users see the server's zone.
• Date-only values stored as midnight UTC and displayed in a zone west of UTC, showing the previous day. Use the date type for dates.
• Storing offsets instead of zone names.

What to check in review

Does the column type match what the value means? Is the Go value converted to UTC before writing? Does anything format a time without a user zone? Is the session zone pinned? Is there a test that fails if the process runs under a different TZ? The last one can be automated by running the test binary under TZ=Pacific/Kiritimati and TZ=Pacific/Pago_Pago, as in hermetic tests. The PostgreSQL manual explains date and time types and time zones, and Go's time package describes In, UTC and LoadLocation in the package reference.