Rust MySQL connection (sqlx) using wrong timezone

help
1

Hello

I have set the global variable for my timezone both in the terminal and in my mysql-config file to UTC+2.

mysql> SET GLOBAL time_zone = '+02:00';
Query OK, 0 rows affected (0,00 sec)

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2023-10-18 06:33:32 |
+---------------------+
1 row in set (0,00 sec)

In my terminal I get the correct time when selecting CURRENT_TIMESTAMP. But when I do the following on Rust in the same database:

    let result = sqlx::query(
        "SELECT CAST(CURRENT_TIMESTAMP AS CHAR) AS xxx",
    )
    .fetch_one(&mysql.conn)
    .await;

    match result {
        Err(e) => {
            println!("Error {}", e);
        },
        Ok(r) => { 
            let time : String = r.try_get("xxx").unwrap();
            println!("Time: {}", time); 
        }
    };

The output is Time: 2023-10-18 04:34:35. (Wrong).

How can I let Rust use the correct Timezone?

2

(Not actually familiar with sqlx, just searched around.)

Looks like this issue, which has a workaround in the comments. Or perhaps you can use after_connect to set time_zone.

See also.

1 Like
3

Thanks mate! This works.

    let conn_str: String = format!(
        "mysql://{db_user}:{db_pass}@localhost:3306/{db_name}",
        db_user = *config::DB_USER,
        db_pass = *config::DB_PASS,
        db_name = *config::DB_NAME
    );

    let mysql = web::Data::new(MySQL {
        conn: MySqlPoolOptions::new().after_connect(|conn, _| {
                Box::pin(async move {
                    let _ = conn
                        .execute("SET time_zone='+02:00';")
                        .await;
                    Ok(())
                })
            }).connect(&conn_str).await.unwrap(),
    });
     let result = sqlx::query(
        "SELECT CAST(CURRENT_TIMESTAMP AS CHAR) AS xxx",
    )
    .fetch_one(&mysql.conn)
    .await;

    match result {
        Err(e) => {
            println!("Error {}", e);
        },
        Ok(r) => {
            let time : String = r.try_get("xxx").unwrap();
            println!("Time: {}", time);
        }
    };

Is there a way to auto-adjust the correct time for the daylight savings?

4

I believe you'd have to load time zone tables and use a named timezone.

Alternatively, you could presumably code for UTC and handle time zones in your own code (with chrono-tz or such).

1 Like