はじめに

TIG真野です。育休明けです。

timestampz の仕様

PostgreSQLのドキュメント 8.5.1.3. タイムスタンプ には以下のような仕様が書かれています。

1,2 は timestamp with time zone という名称から、書き込み時のタイムゾーンも保持していると勘違いしちゃいがちですが、実際はそうじゃないよと認識すればOKです。理解できました。

個人的には、3は少しややこしいかなと思います。現行のタイムゾーンとは、すなわちDBセッションで有効なタイムゾーンを用いられると考えられ、確かに to_char() で文字列化したときや、 psql など一部のクライアントツール（いわゆるテキストフォーマットでやり取りする場合）に対しては正しいです。一方で、 jackc/pgx のドライバー経由でDBを利用するクライアントアプリの世界から見ると、これは適用されません（理由を先に書くと、バイナリフォーマットではタイムゾーン情報を送信しないからです）。

timestampz を扱う際の留意事項

ということでGoで jackc/pgx を用いた時に timestampz 型のデータを扱う上で留意すべきことを言い換えてみます。

環境構築

ここから、PostgreSQLに timestampz を含むテーブルを作成し、タイムゾーンを変えたいくつかのパターンで、Goでデータを書き込み・読み込みして挙動を確認していきます。

version: '3.9'

services:
  db:
    image: postgres:16.0-bullseye
    container_name: pg
    restart: always
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: pass
      POSTGRES_DB: postgres
      TZ: "Asia/Tokyo"
      POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=C"
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    command: postgres -c log_destination=stderr -c log_statement=all -c log_connections=on -c log_disconnections=on
volumes:
  pgdata:

docker-compose up -d

$ PGPASSWORD=pass psql -h localhost -p 5432 -U postgres -c "select current_setting('timezone');"
 current_setting 
-----------------
 Asia/Tokyo
(1 row)

検証用のテーブル作成

検証用にtimestamp with time zone（timestampz）型を含む event テーブルを用意します。

CREATE TABLE event
(
    event_id varchar(4) PRIMARY KEY,
    event_at timestamptz NOT NULL
);

$ PGPASSWORD=pass psql -h localhost -p 5432 -U postgres -f schema.sql
CREATE TABLE

Go経由でDB操作

Goのアプリ経由で、データの書き込み/読み込みを行ってみます。

データ書き込み

Go経由で event テーブルに2レコード書き込みます。内容は以下です。

package main

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

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

func main() {
	ctx := context.Background()

	connURL := "postgres://postgres:pass@localhost:5432/postgres?sslmode=disable"
	conn, err := pgx.Connect(ctx, connURL)
	if err != nil {
		log.Fatalf("pgx connect: %v", err)
	}
	defer conn.Close(ctx)

	jstZone := time.FixedZone("Asia/Tokyo", 9*60*60)

	args := [][]any{
		{"0001", time.Now()},
		{"0002", time.Now().In(jstZone)},
	}

	copyCount, err := conn.CopyFrom(ctx, pgx.Identifier{"event"},
		[]string{"event_id", "event_at"}, pgx.CopyFromRows(args))

	if err != nil {
		log.Fatalf("copy exec: %v", err)
	}

	fmt.Printf("copy: %d\n", copyCount)

}

実行して以下のような実行結果が出れば登録できました。

>go run .
copy: 2

データ読み込み

続いて読み込みです。Structを用意します。

package main

import "time"

type Event struct {
	ID string
	At time.Time
}

package main

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

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

func main() {
	ctx := context.Background()

	connURL := "postgres://postgres:pass@localhost:5432/postgres?sslmode=disable"
	config, err := pgx.ParseConfig(connURL)
	if err != nil {
		log.Fatal(err)
	}

	conn, err := pgx.ConnectConfig(ctx, config)
	if err != nil {
		log.Fatalf("pgx connect: %v", err)
	}
	defer conn.Close(ctx)

	rows, err := conn.Query(ctx, "select * from event")
	if err != nil {
		log.Fatalf("select query: %v", err)
	}
	defer rows.Close()

	for rows.Next() {
		var e Event
		if err := rows.Scan(&e.ID, &e.At); err != nil {
			log.Fatalf("scan: %v", err)
		}
		fmt.Printf("%s %s\n", e.ID, e.At.Format(time.RFC3339))
	}

}

On Unix systems, Local consults the TZ environment variable to find the time zone to use.
https://pkg.go.dev/time#Location

動かすと、 0001 はUTC、 0002 はJSTにしたtime.Timeの値をDBに登録したのですが、結果は どちらもUTC になっていることがわかります。

$ TZ=UTC go run .
0001 2023-10-21T12:04:20Z




    

0002 2023-10-21T12:04:20Z

OSがWindowsの場合は TZ 環境変数を読み込んでくれなかったので、tzutilコマンドで切り替えます。こちらも当然結果は同じく、2レコードとも、AtフィールドがUTCのタイムゾーンとなっていることが確認できます。

# UTCにタイムゾーン切り替え
$ tzutil /s "UTC"

$ go run .
0001 2023-10-21T12:04:20Z
0002 2023-10-21T12:04:20Z

# もとに戻す（JSTの場合）
$ tzutil /s "Tokyo Standard Time" 

今度はタイムゾーンをJSTに登録すると2レコードともJSTのタイムゾーンになることが確認できます（Windows側の実行例は割愛します）。

$ $ TZ=Asia/Tokyo go run .
0001 2023-10-21T21:04:20+09:00
0002 2023-10-21T21:04:20+09:00

つまり、最初に書いた挙動をすることがわかります。

接続URLにタイムゾーンを設定すると？

ドライバーによっては、DBを接続時にタイムゾーンを渡せるものもあります（例えばPostgreSQLのJDBCドライバーも -Duser.timezone=Asian/Tokyo で渡せるようです）。 pgx の場合は次のような RuntimeParams を利用することで、セッションのタイムゾーンを変更できます。

type Event struct {
	ID string
	At time.Time
+	TZ string
}

func main() {
	ctx := context.Background()

	connURL := "postgres://postgres:pass@localhost:5432/postgres?sslmode=disable"
	config, err := pgx.ParseConfig(connURL)
	if err != nil {
		log.Fatal(err)
	}
+	config.RuntimeParams["timezone"] = "Asia/Singapore"

	conn, err := pgx.ConnectConfig(ctx, config)
	if err != nil {
		log.Fatalf("pgx connect: %v", err)
	}
	defer conn.Close(ctx)

-	rows, err := conn.Query(ctx, "select * from event")
+	rows, err := conn.Query(ctx, "select *, current_setting('timezone') as tz from event")
	if err != nil {
		log.Fatalf("select query: %v", err)
	}
	defer rows.Close()

	for rows.Next() {
		var e Event
-		if err := rows.Scan(&e.ID, &e.At); err != nil {
+		if err := rows.Scan(&e.ID, &e.At, &e.TZ); err != nil {
			log.Fatalf("scan: %v", err)
		}
-		fmt.Printf("%s %s %s\n", e.ID, e.At.Format(time.RFC3339), e.TZ)
+		fmt.Printf("%s %s %s\n", e.ID, e.At.Format(time.RFC3339))
	}

}

$ TZ=UTC go run .
0001 2023-10-21T12:04:20Z Asia/Singapore
0002 2023-10-21T12:04:20Z Asia/Singapore

Goの設定として

timestampz 型を扱う上で、time.TimeのタイムゾーンはDB側とは別に設定する必要があるという話をしました。
アプリ内で色々なタイムゾーンが混在すると不具合や、ログで表示される時刻などが乱れることで運用コストも増えてしまうので、通常はアプリ内である設定値に統一した方が良いでしょう。

func init() {
    time.Local = time.FixedZone("Asia/Tokyo", 9*60*60)
}

ただ、上記の手法だとtutuzさんの GoでJSTのタイムゾーンを扱う方法 の記事にあるように、DATA RACEする可能性があるようです。回避するためにブランクimportなどの措置も面倒なので万能の解では無いですが、お手軽ではあります（私もよく利用してしまいます）。

個別対応になるため設定し忘れが怖いですが、プロジェクトルールとして time.In() を設定するという決めにするというのもあります。

var jst = time.FixedZone("Asia/Tokyo", 9*60*60)

func main() {
	// ...(中略)...
	for _, e := range events {
		fmt.Printf("%s %s\n", e.ID, e.At.In(jst)) // Inでタイムゾーンを指定する
	}

書き込み時はともかく、読込み時もタイムゾーンを指定しないとならないのは、少し釈然としないところもありますが、忘れないようにしましょう。

FAQ

よく思いつきそうな疑問について、まとめます。

DBセッションで用いるタイムゾーンってどう決まるの？

bisqueさんの PostgreSQLのTimeZoneを理解する が詳しいのでそちらも参照ください。

この記事で簡単にざっくり説明しますと、次の優先度で決まります。

psql などのSQLクライアントとして用いると、DB側のタイムゾーンを利用しているようですが？

確かに、 psql は select current_setting('timezone'); で表示されるタイムゾーンによって、 timestampz の表示が変わります。他にも、 psqledit 、 DataGrip でも同様の挙動でした。

postgres=# select current_setting('timezone');
 current_setting 
-----------------
 Asia/Tokyo
(1 row)

postgres=# select * from event;
 event_id |           event_at            
----------+-------------------------------
 0001     | 2023-10-21 21:04:20.445974+09
 0002     | 2023-10-21 21:04:20.445974+09
(2 rows)

postgres=# set timezone to 'UTC';
SET
postgres=# select * from event;
 event_id |           event_at            
----------+-------------------------------
 0001     | 2023-10-21 12:04:20.445974+00
 0002     | 2023-10-21 12:04:20.445974+00
(2 rows)

ちなみに、 libpq は PGTZ という環境変数で、タイムゾーンを変更できます（ SET timezone TO …と等価である とありますが、DB側のログにはSET timezone To … が出てこなかったので、接続時に指定していると思われます）。

環境変数 PGTZ でのタイムゾーンを UTC にした、 psql コマンドを利用する例です。

$ PGTZ=UTC PGPASSWORD=pass psql -h localhost -p 5432 -U postgres -c 'select * from event;'
 event_id |           event_at            
----------+-------------------------------
 0001     | 2023-10-21 12:04:20.445974+00
 0002     | 2023-10-21 12:04:20.445974+00
(2 rows)

ちなみに、DBeaver 23.2.2 では、 set timezone to 'UTC' などをしても timestampz カラムを表示する際に利用するタイムゾーンに変わりはありませんでした（ +0900 のまま）。DBeaverはローカルのタイムゾーンを利用するため、もしローカル端末のタイムゾーンと、DBのタイムゾーンが異なる場合は、 dbeaver.ini に -Duser.timezone=xxx を追加して、タイムゾーンを一致させる必要があるようです。

timestampz だけPostgreSQLのDBサーバからテキストフォーマットで受け取れば、セッションのタイムゾーン付きで受信できるため、それを元に time.Time にタイムゾーンを指定すればよいでは？

同じことを思ったのですが、 Scanning of timestamp without time zone forces UTC #924 を読んだところ、いくつか課題があるようです。

これらの理由のため、対応は難しいようです。PRコントリビュートチャンスかと思いましたが、やはり簡単ではないですね…。

DB接続時のセッションで有効なタイムゾーンってGoアプリの場合、どこに影響するの？

セッションのタイムゾーンを、DBから取得した timestampz の列データを time.Time に適用してくれないなら、どこに影響するのかという疑問ですよね。

$ PGPASSWORD=pass psql -h localhost -p 5432 -U postgres \
  -c "select event_id, to_char(event_at, 'YYYY-MM-DD HH24:MI:SS') from event;"
 event_id |       to_char       
----------+---------------------
 0001     | 2023-10-21 21:04:20
 0002     | 2023-10-21 21:04:20
(2 rows)

DB接続セッションのタイムゾーン値を利用してクエリ結果を time.Time 型のフィールドにマッピングするときに自動で設定する実装をしたい

pgxにも同じような旨のIssueである、 How do you set the timezone connection variable? #520 が上がっていますが、少なくてもpgxにはそのような機能は無いようです。あまり探していませんがそういったライブラリが無いような気がします（みんな、 TZ で指定するか、 time.Local に設定している？）。pgxは拡張性が高いパッケージなので、下回りを操作すれば実現できるかもしれませんが、私は試していません。こうやれば良いよと言うのがあればぜひ教えてください。

select current_setting('TIMEZONE') で取得したタイムゾーンを、 time.In() に設定して欲しい気持ちはよくわかります。

go-sql-driver/MySQL みたいな loc=Asia%2FTokyo” オプションは無いの？

自信がなくなってきましたが、少なくても jackc/pgx については、私が探した範囲では見つけられませんでした。

1. はじめに
2. timestampz の仕様
3. timestampz を扱う際の留意事項
4. 環境構築
5. 検証用のテーブル作成
6. Go経由でDB操作
   1. データ書き込み
   2. データ読み込み
7. 接続URLにタイムゾーンを設定すると？
8. Goの設定として
9. FAQ
   1. DBセッションで用いるタイムゾーンってどう決まるの？
   2. psql などのSQLクライアントとして用いると、DB側のタイムゾーンを利用しているようですが？
   3. timestampz だけPostgreSQLのDBサーバからテキストフォーマットで受け取れば、セッションのタイムゾーン付きで受信できるため、それを元に time.Time にタイムゾーンを指定すればよいでは？
   4. DB接続時のセッションで有効なタイムゾーンってGoアプリの場合、どこに影響するの？
   5. DB接続セッションのタイムゾーン値を利用してクエリ結果を time.Time 型のフィールドにマッピングするときに自動で設定する実装をしたい
   6. go-sql-driver/MySQL みたいな loc=Asia%2FTokyo” オプションは無いの？
10. まとめ
11. 参考

カテゴリー