MariaDB – Time Zone

測試環境為 CentOS 8 x86_64 (虛擬機) .

遇到一個奇怪的問題,在不同機器上使用 UNIX_TIMESTAMP(TS) 函數 (該函數功能為把時間轉換成為數字,轉換方式為: 指定的時間 -(減去) 1970-01-01 00:00:00 得到的秒數差, 回傳為無符號整數) 卻得到不同的植.

機器 A

MariaDB [(none)]> SELECT UNIX_TIMESTAMP('2021-01-01 12:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2021-01-01 12:00:00') |
+---------------------------------------+
|                            1609473600 |
+---------------------------------------+

機器 B

MariaDB [(none)]> SELECT UNIX_TIMESTAMP('2021-01-01 12:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2021-01-01 12:00:00') |
+---------------------------------------+
|                            1609520400 |
+---------------------------------------+

這是因為這兩台機器所設定的時區 (Time Zone) 不一樣所導致的, 部分跟時間相關的函數 會因為時區得到不同的回傳值.

來看一下 MariaDB 設定的 時區.

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%time_zone';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | EST    |
| time_zone        | SYSTEM |
+------------------+--------+

要變更時區可以透過以下幾種方式.

變更 Linux 系統的時區

[root@localhost ~]# timedatectl
               Local time: Thu 2022-02-24 16:45:50 CST
           Universal time: Thu 2022-02-24 08:45:50 UTC
                 RTC time: Thu 2022-02-24 08:45:50
                Time zone: Asia/Taipei (CST, +0800)
System clock synchronized: yes
              NTP service: active
          RTC in local TZ: no

需重新啟動 MariaDB 服務.

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%time_zone';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.001 sec)

奇怪 CST 是什麼時區.請參考 – https://www.worldtimebuddy.com/cst-to-pst-converter

暫時改變 MariaDB 時區

透過以下指令來變更卻遇到錯誤訊息.

MariaDB [(none)]> SET GLOBAL time_zone = 'America/New_York';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

需把 時區資料加到資料庫.

[root@localhost ~]# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p
Enter password:
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.

設定時區.

MariaDB [(none)]> SET GLOBAL time_zone = 'America/New_York';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%time_zone';
+------------------+------------------+
| Variable_name    | Value            |
+------------------+------------------+
| system_time_zone | CST              |
| time_zone        | America/New_York |
+------------------+------------------+
2 rows in set (0.001 sec)

須注意 MariaDB 服務重新啟動就會依據 Linux 設定的時區.

MariaDB [(none)]> exit
Bye
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%time_zone';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.001 sec)

MariaDB 時區設定檔

[root@localhost ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mariadb]
...
default_time_zone = 'America/New_York'

需重啟 MariaDB 服務.

[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%time_zone';
+------------------+------------------+
| Variable_name    | Value            |
+------------------+------------------+
| system_time_zone | CST              |
| time_zone        | America/New_York |
+------------------+------------------+
2 rows in set (0.001 sec)