Comments (5)
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.
from pgtype.
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)
- "SET TIMEZONE TO Asia/Jerusalem".
- SP returns "2019-11-25 11:23:40.999+03"
- PGX parses the result
result: "2019-11-25 11:23:40.999+01"
expected result: "2019-11-25 11:23:40.999+03"
from pgtype.
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.
from pgtype.
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?
Line 241 in 038f263
from pgtype.
Yes. Though by override I don't mean you would change it in pgtype itself. You would call RegisterDataType in an AfterConnect hook.
from pgtype.
Related Issues (20)
- cannot use net.ParseIP(c.IP()) (type net.IP) as type pgtype.Inet in field value HOT 5
- Automatically Marshall JSON/JSONB/JSONBArray etc types as strings in Query Values HOT 3
- Known vulnerability in outdated pgx dependency, pgproto3-1.1.0 HOT 2
- Question: JSONBArray EncodeText HOT 2
- lseq type is not transitional decoding HOT 1
- Is it possible to omit undefined values? HOT 1
- invalid field found for struct github.com/jackc/pgtype.Inet's field IPNet: define a valid foreign key for relations or implement the Valuer/Scanner interface HOT 1
- BC Dates cause errors for the pgdate scanner HOT 3
- Json marshaling and unmarshaling of pgtype.inet HOT 2
- panic when scanning null enum to *sql.Scanner HOT 3
- DecodeBinary can't decode NaN HOT 7
- Not Support for nil macaddr HOT 1
- typo in error message
- scanning Daterange fails in v1.14.0
- Register a custom type with sqlx HOT 15
- Numeric type is broken for small negative values HOT 4
- Bug scanning Numeric in v1.14.1 HOT 3
- Release 1.14.3 HOT 1
- Support serialisation of pgtype.Type HOT 1
- comparing uncomparable type pgtype.VarcharArray HOT 3
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pgtype.