-
Notifications
You must be signed in to change notification settings - Fork 1.3k
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
sqlx query's output is different when the same query is run from postgresql cli #3226
Comments
This is not strictly a bug. The
Meanwhile, SQLx sets the TimeZone for the connection to UTC by default. This is to enforce consistent behavior across varying configurations, especially when database replicas are distributed across time zones. It's also for consistency with other database drivers, namely the MySQL driver, where we're forced to set UTC as the default timezone or else it's impossible to interpret timestamps correctly in the binary format. Frankly, the default
( Note that trying to pass a numerical offset or a generic timezone name like
I have no idea why this is, as it's supposed to be supported ( The core takeaway is that timezones are weird. We recommend not handling them in SQL, and just using UTC everywhere. |
True, timezones are weird, but I suppose it's a good idea to mention this quirk somewhere in documentation? |
Sure, I'd accept a PR for that. |
Bug Description
When running the query using sqlx, its output is different vs when the same query is run using postgersql CLI. Postgresql server, database are the same.
Minimal Reproduction
Output:
If the same query is run against the same database on the same postgresql server using psql:
Interestingly enough, if
AT TIME ZONE 'UTC'
is applied to both timestamps before truncating, orUTC
argument is supplied todate_trunc
function, output will be the same between sqlx and psql.Info
0.7.4
postgres,time,uuid,runtime-tokio,rust_decimal
Postgresql 16.2
Arch linux 6.8.9-zen1-2-zen
rustc --version
:rustc 1.79.0-nightly (ab5bda1aa 2024-04-08)
The text was updated successfully, but these errors were encountered: