How to use system time_zone in MySqlConnection?

[pymongo]

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.

[mehcode]

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.

[pymongo]

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)

[mehcode]

Let's keep this open until there is a solution here.

[abonander]

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.

[comicfans]

Hello, I have similar problem, I want my result field DateTime<FixedOffset>, but query_as didn't accept it.

[CallumDowling]

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.

[gfan8w]

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!!!!

[gudaoxuri]

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.