Why does the parsing of oid.Timestamp type not use the current session time zone?

[ivothgle]

There is such a database table

CREATE TABLE "test"
(
  "id"               SERIAL8       NOT NULL,
  "created_at"           timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at"           timestamp  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("id")
)

and go structure

type Test struct {
	Id        int
	CreatedAt time.Time
	UpdatedAt time.Time
}

Then, the time zone of the database configuration is PRC , and the local time zone of the go program is PRC

https://github.com/lib/pq/blob/master/encode.go#L109

The string(s) value at this time is 2024-10-11 10:00:00. Because the parseTs parameter does not contain a time zone, the time zone of the parsed time.Time is UTC, but the time zone of this date should be the database time zone PRC.

This will cause all calculations about this time to be wrong.
How should I solve this problem?

[arp242]

If I use the psql CLI then it always returns the time as inserted:

=# create temp table tbl (col timestamp);
=# insert into tbl values ('2026-01-31 18:30:40');

=# select * from tbl;
		col
─────────────────────
2026-01-31 18:30:40

=# set timezone = 'Asia/Shanghai';
=# select * from tbl;
		col
─────────────────────
2026-01-31 18:30:40

=# alter database pqgo set timezone to 'Asia/Shanghai';
=# select * from tbl;
		col
─────────────────────
2026-01-31 18:30:40

I need to explicitly use at time zone 'Asia/Shanghai':

select col at time zone 'Asia/Shanghai' from tbl;
		timezone
────────────────────────
2026-01-31 18:30:40+08

---

As for pq, technically it doesn't return the timezone in UTC, it returns the timezone with an empty location, although in the string that does get represented as +0000 (making it seem like it's UTC). AFAIK Go's time package doesn't really have a concept of "time without timezone", so there is some mismatch between PostgreSQL and Go here.

Like psql, you can use at time zone 'Asia/Shanghai' in the SQL, or convert the date with time.date():

cn, _ := time.LoadLocation("Asia/Shanghai")
t = time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), t.Second(), t.Nanosecond(), cn)

Which I admit is awkward, but time.Time doesn't have a way to outright change the timezone.

---

In short, this seems correct to me.

Simply changing it would be a non-starter as that would break existing applications.

Adding some new facility might be possible, I suppose, but I don't really see a demand for it? Especially because this matches how PostgreSQL works as near as I can tell.