Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
-
None
Description
The test is:
MariaDB [test]> create table cs1 (a datetime) engine=columnstore;
|
Query OK, 0 rows affected (0,379 sec)
|
|
|
MariaDB [test]> insert into cs1 values("2025-04-03 20:36:42");
|
Query OK, 1 row affected, 1 warning (0,108 sec)
|
|
|
MariaDB [test]> SET @@session.time_zone = "+01:00";
|
Query OK, 0 rows affected (0,000 sec)
|
|
|
MariaDB [test]> insert into cs1 values("2025-04-03 20:36:42");
|
Query OK, 1 row affected, 1 warning (0,137 sec)
|
|
|
MariaDB [test]> SET @@session.time_zone = "+00:00";
|
Query OK, 0 rows affected (0,000 sec)
|
|
|
MariaDB [test]> insert into cs1 values("2025-04-03 20:36:42");
|
Query OK, 1 row affected (0,146 sec)
|
|
|
MariaDB [test]> SET @@session.time_zone = "+01:00";
|
Query OK, 0 rows affected (0,000 sec)
|
|
|
MariaDB [test]> insert into cs1 values("2025-04-03 20:36:42");
|
Query OK, 1 row affected (0,139 sec)
|
|
|
MariaDB [test]> select * from cs1;
|
+---------------------+
|
| a |
|
+---------------------+
|
| 2025-04-03 20:36:42 |
|
| 2025-04-03 20:36:42 |
|
+---------------------+
|
2 rows in set (0,064 sec)
|
|
|
MariaDB [test]> SET @@session.time_zone = "+00:00";
|
Query OK, 0 rows affected (0,000 sec)
|
|
|
MariaDB [test]> create table inno1 (a datetime);
|
Query OK, 0 rows affected (0,010 sec)
|
|
|
MariaDB [test]> insert into inno1 values("2025-04-03 20:36:42");
|
Query OK, 1 row affected (0,006 sec)
|
|
|
MariaDB [test]> SET @@session.time_zone = "+01:00";
|
Query OK, 0 rows affected (0,000 sec)
|
|
|
MariaDB [test]> insert into inno1 values("2025-04-03 20:36:42");
|
Query OK, 1 row affected (0,001 sec)
|
|
|
MariaDB [test]> select * from cs1;
|
+---------------------+
|
| a |
|
+---------------------+
|
| 2025-04-03 20:36:42 |
|
| 2025-04-03 20:36:42 |
|
+---------------------+
|
2 rows in set (0,012 sec)
|
|
|
MariaDB [test]> SET @@session.time_zone = "+00:00";
|
Query OK, 0 rows affected (0,000 sec)
|
|
|
MariaDB [test]> select a, UNIX_TIMESTAMP(a) from cs1;
|
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2025-04-03 20:36:42 | 1743701802 |
|
| 2025-04-03 20:36:42 | 1743701802 |
|
+---------------------+-------------------+
|
2 rows in set (0,016 sec)
|
|
|
MariaDB [test]> select a, UNIX_TIMESTAMP(a) from inno1;
|
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2025-04-03 20:36:42 | 1743712602 |
|
| 2025-04-03 20:36:42 | 1743712602 |
|
+---------------------+-------------------+
|
2 rows in set (0,001 sec)
|
|
|
MariaDB [test]> SET @@session.time_zone = "+01:00";
|
Query OK, 0 rows affected (0,000 sec)
|
|
|
MariaDB [test]> select a, UNIX_TIMESTAMP(a) from cs1;
|
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2025-04-03 20:36:42 | 1743701802 |
|
| 2025-04-03 20:36:42 | 1743701802 |
|
+---------------------+-------------------+
|
2 rows in set (0,016 sec)
|
|
|
MariaDB [test]> select a, UNIX_TIMESTAMP(a) from inno1;
|
+---------------------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+---------------------+-------------------+
|
| 2025-04-03 20:36:42 | 1743709002 |
|
| 2025-04-03 20:36:42 | 1743709002 |
|
+---------------------+-------------------+
|
2 rows in set (0,001 sec)
|
|
|
MariaDB [test]>
|
on another machine, with different system time zone settings (outsize of MariaDB?)
MariaDB [autopilot]> create table cs1 (a datetime) engine=columnstore;
|
Query OK, 0 rows affected (0.416 sec)
|
|
|
MariaDB [autopilot]> insert into cs values("2025-04-03 20:36:42");^C
|
MariaDB [autopilot]> SET @@session.time_zone = "+00:00";
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [autopilot]> insert into cs1 values("2025-04-03 20:36:42");
|
Query OK, 1 row affected (0.315 sec)
|
|
|
MariaDB [autopilot]> SET @@session.time_zone = "+01:00";
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [autopilot]> insert into cs1 values("2025-04-03 20:36:42");
|
Query OK, 1 row affected (0.050 sec)
|
|
|
MariaDB [autopilot]> SET @@session.time_zone = "+00:00";
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [autopilot]> create table inno1 (a datetime);
|
Query OK, 0 rows affected (0.011 sec)
|
|
|
MariaDB [autopilot]> insert into inno1 values("2025-04-03 20:36:42");
|
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [autopilot]> SET @@session.time_zone = "+01:00";
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [autopilot]> insert into inno1 values("2025-04-03 20:36:42");
|
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [autopilot]> SET @@session.time_zone = "+00:00";
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [autopilot]> select a, UNIX_TIMESTAMP(a) from cs1;
|
+------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+------+-------------------+
|
| 2025-04-03 20:36:42 | 1743705402 |
|
| 2025-04-03 20:36:42 | 1743705402 |
|
+------+-------------------+
|
2 rows in set (0.160 sec)
|
|
|
MariaDB [autopilot]> select a, UNIX_TIMESTAMP(a) from inno1;
|
+------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+------+-------------------+
|
| 2025-04-03 20:36:42 | 1743712602 |
|
| 2025-04-03 20:36:42 | 1743712602 |
|
+------+-------------------+
|
2 rows in set (0.000 sec)
|
|
|
MariaDB [autopilot]> SET @@session.time_zone = "+01:00";
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [autopilot]> select a, UNIX_TIMESTAMP(a) from cs1;
|
+------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+------+-------------------+
|
| 2025-04-03 20:36:42 | 1743705402 |
|
| 2025-04-03 20:36:42 | 1743705402 |
|
+------+-------------------+
|
2 rows in set (0.005 sec)
|
|
|
MariaDB [autopilot]> select a, UNIX_TIMESTAMP(a) from inno1;
|
+------+-------------------+
|
| a | UNIX_TIMESTAMP(a) |
|
+------+-------------------+
|
| 2025-04-03 20:36:42 | 1743709002 |
|
| 2025-04-03 20:36:42 | 1743709002 |
|
+------+-------------------+
|
2 rows in set (0.000 sec)
|
|
Findings:
- results do not depend on the time zone set when INSERT is executed
- `select a` gives the same value in all cases (both engines, independently from time zone settings)
- `select UNIX_TIMESTAMP(a)` does not depend on time zone settings for Columnstore, but gives different values for every time zone for INNODB
- in values in the Columnstore table depend on system timezone settings
- `select UNIX_TIMESTAMP(a)` for Columnstore table shows the number that corresponds to time in the text representation for the machine system time zone
- `select UNIX_TIMESTAMP(a)` for INNODB table shows the number that corresponds time in the text representation for time zone defined in the @@session.time_zone
Attachments
Issue Links
- is blocked by
-
-
- In Progress
-