PostgreSQL & MySQL returning different timezones on timestamp query

[Valentin-Metz]

Versions

• Rust: cargo 1.71.0-nightly (9e586fbd8 2023-04-25)
• Diesel: 2.01
• Database: PostgreSQL & MariaDB
• Operating System Manjaro

Feature Flags

• diesel: diesel = { version = "2.0.1", features = ["chrono", "serde_json"] }

Problem Description

Postgres and MySQL queries behave differently when querying timestamps:

Postgres query:

.select(my_table::login_timestamp)
.filter(my_table::login_timestamp.gt(Local::now().naive_local() - chrono::Duration::seconds(5)))
.first::<chrono::NaiveDateTime>(database)

(The select for Postgres returns local time)

Identical query for MySQL:

.select(my_table::login_timestamp)
.filter(my_table::login_timestamp.gt(Local::now().naive_utc() - chrono::Duration::seconds(5)))
.first::<chrono::NaiveDateTime>(database)

(The select for MySQL returns UTC instead of local time)
A SELECT timestamp from my_table; in the mysql-console returns local time correctly.
Column datatype is a "Timestamp" in both cases.

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

• This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
• This issue can be reproduced without requiring a third party crate

[weiznich]

This bug report is missing important information about the relevant schema used for both database systems. Ideally you would add/link a completely reproducible example of the underlying issue. It is at least required to add the relevant schema definition (as sql), otherwise I can only close this issue due to missing informarion.

[Valentin-Metz]

Postgres:

create table public.my_table
(
    id                            serial8
        constraint connections_pk
            primary key,
    login_timestamp               timestamp not null
);

MariaDB:

create table my_table
(
    id                            bigint auto_increment primary key,
    login_timestamp               timestamp not null
);

Let me know if this is enough. Otherwise I can try to reproduce this in a Docker compose.

[mattdowdell]

Timestamp in MariaDB is stored as seconds since the Unix epoch in UTC. Timestamp in PostgreSQL can be made timezone aware, although your example is not.

Perhaps try using timestamp with time zone or timestamptz for the type in your PostgreSQL schema?