New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Scanning of timestamp without time zone forces UTC #924
Comments
In general I strongly recommend using |
Sounds convincing, but then it's not clear why this method works fine: package main
import (
"context"
"github.com/jackc/pgx/v4"
"github.com/joho/godotenv"
"log"
"os"
"time"
)
func main() {
if err := godotenv.Load(".env.local"); err != nil {
log.Fatal(err)
}
conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
var tz string
var tm time.Time
err = conn.QueryRow(context.Background(), "select current_setting('TIMEZONE'), now()").Scan(&tz, &tm)
if err != nil {
log.Fatal(err)
}
log.Printf("current_setting('TIMEZONE'): %s, now(): %v\n", tz, tm)
var typeOf string
var tmz time.Time
err = conn.QueryRow(context.Background(), `select pg_typeof(created_at),
created_at,
created_at::timestamptz created_at_tz
from review
order by created_at desc
limit 1`).Scan(&typeOf, &tm, &tmz)
if err != nil {
log.Fatal(err)
}
log.Printf("pg_typeof(created_at): %v, created_at: %v, created_at_tz: %v\n", typeOf, tm, tmz)
}
As you can see, |
The binary format of both With https://github.com/jackc/pgtype/blob/6830cc09847cfe17ae59177e7f81b67312496108/timestamptz.go#L152 With https://github.com/jackc/pgtype/blob/6830cc09847cfe17ae59177e7f81b67312496108/timestamp.go#L145 and for some explanation https://github.com/jackc/pgtype/blob/6830cc09847cfe17ae59177e7f81b67312496108/timestamp.go#L14 |
Can't agree with that. When we storing values in -- start is timestamp without time zone
SELECT current_setting('TIMEZONE'), start, start::timestamptz
FROM release; As you can see, PostgreSQL by itself don't say "it's UTC", but keeps current connection-wide timezone setting. That's by design. And that what is expected while scanning fields. I hope, I explained my point of view in sufficient detail. |
I guess some of this depends on whether you view the PostgreSQL type See https://play.golang.org/p/YLIpROP3MhY for what happens when trying to parse Also, this behavior has been how pgx has handled |
@jackc If you look at how Postgres' documentation describes comparison between
To me, this seems to indicate that in absence of a timezone, it is local time as specified in the configuration that should be applied.
While I understand this is a breaking change, there could perhaps be solved with some configuration setting, instead of having to create custom data types to alter the scanning. |
How to deal with
timestamp without time zone
type?I expected pgx to scan them to
time.Time
struct using current PostgreSQL connection client timezone or local golang timezone, but it forces UTC:So I have to do this ugly timezone update after scanning:
The text was updated successfully, but these errors were encountered: