The query results for timestamp_tz in MySQL do not match those in DuckDB.

[BsoBird]

What happens?

mysql side:

create table TEST01(
  TZTEST TIMESTAMP(3)
);


insert into TEST01(TZTEST) VALUES(CURRENT_TIMESTAMP());

select * from TEST01
--  2025-10-29 18:36:01

duckdb side:

detach mysqldb;
ATTACH 'host=localhost user=root port=3306 database=test password=xxxxxx ' AS mysqldb (TYPE MYSQL_SCANNER);

select * from mysqldb.test01;
--2025-10-29 22:36:01.000 +0400

Why does the time queried in DuckDB not match the time in MySQL?

To Reproduce

mysql side:

create table TEST01(
  TZTEST TIMESTAMP(3)
);


insert into TEST01(TZTEST) VALUES(CURRENT_TIMESTAMP());

select * from TEST01
--  2025-10-29 18:36:01

duckdb side:

detach mysqldb;
ATTACH 'host=localhost user=root port=3306 database=test password=xxxxxx ' AS mysqldb (TYPE MYSQL_SCANNER);

select * from mysqldb.test01;
--2025-10-29 22:36:01.000 +0400

extension_name|loaded|installed|install_path                                                                                |description                                    |aliases  |extension_version|install_mode|installed_from|
--------------+------+---------+--------------------------------------------------------------------------------------------+-----------------------------------------------+---------+-----------------+------------+--------------+
mysql_scanner |true  |true     |C:\Users\BsoBird\.duckdb\extensions\v1.4.1\windows_amd64\mysql_scanner.duckdb_extension|Adds support for connecting to a MySQL database|{'mysql'}|c80647b          |REPOSITORY  |core          |

OS:

windows

MySQL Version:

8.0.42

DuckDB Version:

1.4.1

DuckDB Client:

java(DBEaver)

Full Name:

BsoBird

Affiliation:

github user

Have you tried this on the latest main branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[BsoBird]

But strangely, in another environment, the timestamps exported using DuckDB are correct. In the problematic environment, I can only get the correct time after setting the timezone to SET mysql_session_time_zone = '+00:00' (But this is obviously unreasonable.) . I don’t know how to adjust it—can anyone guide me?

[staticlibs]

@BsoBird

Hi, thanks for the report!

Just the timestamp 2025-10-29 22:36:01.000 +0400 is actually the same timestamp as 2025-10-29 18:36:01:

D SET TimeZone='UTC';
D SELECT ('2025-10-29 18:36:01'::TIMESTAMP WITH TIME ZONE) = ('2025-10-29 22:36:01.000+04:00'::TIMESTAMP WITH TIME ZONE);
┌──────────────────────────────────────────────────────────────────────────────────────────────────┐
│ (CAST('2025-10-29 18:36:01' AS TIMESTAMP WITH TIME ZONE) = CAST('2025-10-29 22:36:01.000+04:00…  │
│                                             boolean                                              │
├──────────────────────────────────────────────────────────────────────────────────────────────────┤
│ true                                                                                             │
└──────────────────────────────────────────────────────────────────────────────────────────────────┘

Though I agree that the rules when the timezone shift is applied can be confusing especially because both MySQL TIMESTAMP and DuckDB's TIMESTAMP WITH TIME ZONE have this offset pre-applied (not actually stored), so it is easy to inadvertently have it applied multiple times.

What would be the expected output of the select * from mysqldb.test01; query in this case?

[BsoBird]

@staticlibs 2025-10-29 18:36:01 +04:00 because I set the global time zone in MySQL, i write data into mysql with this timezone. What confuses me even more is that, at present, push-down queries seem hard to take effect; we usually treat the data in MySQL as the source of truth, which leaves me unsure what kind of WHERE clause to write is correct. Besides, I'm unlikely to change the TimeZone on the duckdu side, as that would cause other issues.(If I don't switch the timezone on the DuckDB side, the result of the expression ('2025-10-29 18:36:01'::TIMESTAMP WITH TIME ZONE) = ('2025-10-29 22:36:01.000+04:00'::TIMESTAMP WITH TIME ZONE) will be false)

[BsoBird]

@staticlibs Should we first check the value of globalTimeZone on the MySQL side, then use that as a reference to calculate what offset DuckDB should actually apply? Also, do you have any good ideas to avoid this problem?(Maybe we need to consider the global time zone setting of MySQL, the session time zone setting of MySQL, and the time zone setting on the DuckDB side at the same time....)