Not supported ISO formats of timezones

[ghost]

Hi,

within our company we have many SPs that return time with timezones. This is because users located in different time zones and require to see date/time in their local time zone.

We faced issue that pgx ignores timezones returned by the Postgres in format "2006-01-02 15:04:05+02". As the result we see that timezones are defaulted to the server's timezone.

Currently PGX supports the following formats (taken from sources):
const pgTimestamptzHourFormat = "2006-01-02 15:04:05.999999999Z07"
const pgTimestamptzMinuteFormat = "2006-01-02 15:04:05.999999999Z07:00"
const pgTimestamptzSecondFormat = "2006-01-02 15:04:05.999999999Z07:00:00"

Is it possible to customize the default format?

[jackc]

I think there might be a bit of a misunderstanding of how timestamp with time zone works. In spite of its name it does not actually store a time zone. It uses the session time zone to automatically convert to and from UTC. The time zone you see in the text format is not the time zone that the time was stored in. It is your current session time zone.

In addition, pgx uses the binary format when possible. The PostgreSQL binary format for a timestamp with time zone is a 64 bit integer offset of microseconds from 2000-01-01 00:00:00 UTC. That means in the binary format the value doesn't even include the session time zone.

[ghost]

Dear Jacks,

when you are saying "session time zone" are you mentioning the PG connection? Prior the execution of a stored procedure we issue the "SET TIMEZONE TO ... ".
The SP returns "timestamp with timezone", timezone of a particular geo location.
Upon receiving we see PGX converts "timestamp with time zone" into the time of server. Timezone in this converted time is also equals to the timezone of the server.

See:
0) Server located in timezone "UTC+1" (Europe/Berlin)

1. "SET TIMEZONE TO Asia/Jerusalem".
2. SP returns "2019-11-25 11:23:40.999+03"
3. PGX parses the result

result: "2019-11-25 11:23:40.999+01"
expected result: "2019-11-25 11:23:40.999+03"

[jackc]

when you are saying "session time zone" are you mentioning the PG connection? Prior the execution of a stored procedure we issue the "SET TIMEZONE TO ... ".

Yes. That is changing the PG session time zone.

Upon receiving we see PGX converts "timestamp with time zone" into the time of server.

Well, it's not so much it converts the time zone as the time zone really doesn't exist in the binary format -- it only is a number of microseconds from 2000-01-01 00:00:00 UTC.

If this is a critical requirement then the best option I can see is for you to use a custom Go type to handle timestamptz. You could start with a copy of the existing Timestamptz struct. But then you would remove the binary support to force pgx to use text mode. Then change the text parsing code to do what you want.

You could use this custom type handler explicitly where needed or you could use ConnInfo.RegisterDataType to make it the default.

[ghost]

I'm not sure my understanding is correct... but are you recommending to overwrite the default call ConnInfo.RegisterDataType with the custom self developed type?

pgtype/pgtype.go

Line 241 in 038f263

ci.RegisterDataType(DataType{Value: &Timestamptz{}, Name: "timestamptz", OID: TimestamptzOID})

[jackc]

Yes. Though by override I don't mean you would change it in pgtype itself. You would call RegisterDataType in an AfterConnect hook.