sqlx
sqlx copied to clipboard
How to use system time_zone in MySqlConnection?
I want to use local/system timezone in MySqlPool.
let datetime = chrono::Utc::now().naive_local();
// The insert time is not equal to local time
sqlx::query("INSERT INTO orders (created_at) VALUES (?);")
.bind(datetime)
.execute(&pool)
.await
.unwrap();
But each MySqlConnection execute SET time_zone = '+00:00';
after it is established.
sql-core/src/connection.rs:317
self_.execute(r#"
SET sql_mode=(SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE'));
SET time_zone = '+00:00';
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
"#).await?;
Potential Solutions
Execute SET time_zone = SYSTEM;
Before insert/update datetime value,
but SET time_zone
and Insert
maybe using different connection.
See https://github.com/launchbadge/sqlx/issues/263#issuecomment-617612420 for a potential solution to amend what SQLx is doing on start-up or for general purpose execution.
Another idea is to allow setting system variables via query parameters. The Go MySQL driver allows this.
mysql://root:password@localhost/db?time_zone=SYSTEM
mysql://root:password@localhost/db?time_zone=%27Europe%2FParis%27
Currently, there is no good answer here. #263 is probably a minimum to fix issues like this.
Here is my dirty way to write local timezone datetime.
// Assume local timezone is +08:00
// FIXME Local+naive_local()=+16 timezone!!!
let datetime = chrono::Local::now().naive_local();
sqlx::query("INSERT INTO trades (created_at) VALUES (?);")
.bind(datetime)
.execute(&pool)
.await
.unwrap();
MySqlConnection(0) + NaiveDateTime(+16) = local timezone(+8)
Let's keep this open until there is a solution here.
For properly decoding DateTime<(any TimeZone)>
we'd have to know what the system time zone is, and that the connection is set to use it instead of UTC.
Or I guess the onus is on the user if they decide to set the timezone to non-UTC to only use NaiveDateTime
. Now that I think about it more, maybe this belongs on the ConnectOptions
struct we're discussing in #174.
Hello, I have similar problem, I want my result field DateTime<FixedOffset>, but query_as didn't accept it.
How is this going? When selecting NOW() on sqlx I get a utc+0 timestamp, when using mysql terminal I get utc+11. This causes havoc for my views for example when they operate on datetimes within functions that I have made that assume a +11.
Here is my dirty way to write local timezone datetime.
// Assume local timezone is +08:00 // FIXME Local+naive_local()=+16 timezone!!! let datetime = chrono::Local::now().naive_local(); sqlx::query("INSERT INTO trades (created_at) VALUES (?);") .bind(datetime) .execute(&pool) .await .unwrap();
MySqlConnection(0) + NaiveDateTime(+16) = local timezone(+8)
This is best answer!!!!
How is this going? When selecting NOW() on sqlx I get a utc+0 timestamp, when using mysql terminal I get utc+11. This causes havoc for my views for example when they operate on datetimes within functions that I have made that assume a +11.
c
See #263 (comment) for a potential solution to amend what SQLx is doing on start-up or for general purpose execution.
Another idea is to allow setting system variables via query parameters. The Go MySQL driver allows this.
mysql://root:password@localhost/db?time_zone=SYSTEM mysql://root:password@localhost/db?time_zone=%27Europe%2FParis%27
Currently, there is no good answer here. #263 is probably a minimum to fix issues like this.
Setting a session-level timezone via uri seems generic and reasonable.