設定 Ubuntu & php & mysql timezone

ubuntu

查詢可用 timezone 列表

1
sudo timedatectl list-timezones

設定 timezone

1
sudo timedatectl set-timezone America/New_York

接著重啟一些有影響的服務，例如 cron、apache、mysql 等

1
sudo service cron restart

php

方法一

修改 php.ini

1
2
[Date]
date.timezone = "America/New_York"

結束後重啟 apache

方法二

php 裡設定

1
date_default_timezone_set("America/New_York")

方法三

php 裡設定

1
ini_set('date.timezone','America/New_York');

附上 php timezone list

mysql

說明

mysql timezone 有兩種格式

  • 與 UTC 的時差例如 +00:00
  • 時區名稱例如 Europe/Helsinki

方法一：需重新啟動 mysql

修改設定檔/etc/mysql/my.cnf

1
2
3
default-time-zone = '+8:00'
或是
timezone='UTC'

重啟 mysql

1
sudo systemctl restart mysqld

方法二：不用重新啟動 mysql

設定 global timezone:
有三種方式

1
2
3
SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
SET @@global.time_zone='+00:00';

設定 session timezone:
有三種方式

1
2
3
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00";

integer , timestamp , datetime 比較

integer

  • 長度是4個位元組
  • 儲存空間上比 datatime 少，integer 索引儲存空間也相對較小，排序和查詢效率相對較高一點點
  • 可讀性差

timestamp

  • 長度是4個位元組
  • 值以 UTC 格式儲存
  • 時區轉化，儲存時對當前的時區進行轉換，檢索時再轉換回當前的時區
  • timestamp 值不能早於 1970 或晚於 2037

datetime

  • 長度是8個位元組
  • 與時區無關
  • 以 YYYY-MM-DD HH:MM:SS 格式檢索和顯示 datetime 值。支援的範圍為 1000-01-01 00:00:00 到 9999-12-31 23:59:59
Data Type Storage Required Before MySQL 5.6.4 Storage Required as of MySQL 5.6.4
DATETIME 8 bytes 5 bytes + fractional seconds storage
TIMESTAMP 4 bytes 4 bytes + fractional seconds storage

Mysql Date and Time Type Storage Requirements

查詢 timezone

1
SHOW VARIABLES LIKE '%time_zone%';

補充

mysql 還有一個 system_time_zone，是在 mysql server 開啟時就預設為與機器時間相同。


結論

DB 設定上考量到日光節約的原因，直接把 timezone 設為 UTC 似乎是比較好的方式，如果把 timezone 設為 UTC offset 的話，遇到日光節約時間又要調一次時區，不是很實際。若是把時區設為 UTC 在取出時再依 web server 時間（PHP timezone）轉換時間，比較好處理。

不過，timestamp 有 2038 年問題，在問題發生前就要再轉別的格式，例如整數型態或是 datetime，不過在那之前官方的解決方案應該就出來了。

另 DB table 裡有關時間欄位的型態設定，自動產生的時間應設為 timestamp，例如資料的 date_add、date_upd等。因為如果 DB 換了 timezone 時間才會跟著換，如果是 datetime，就是固定不變，可以視為一段字串來看。但是如果對時間的需求是設定的概念，例如設定商品的特價時間區間、或是文章的發佈時間，存成 datetime 是可以的，使用的時候也是取出後根據 web server timezone 去轉換，或是轉成使用者 local timezone ，視需求而定。

更多閱讀

只因「閏秒」這 1 秒的解決方案，AWS 工程師可能花上數百小時
到底是 GMT+8 還是 UTC+8 ?
Rails 時區好亂啊，通通轉成 UTC

Reference

https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc

https://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices?rq=1

https://stackoverflow.com/tags/timezone/info

https://javorszky.co.uk/2016/06/06/today-i-learned-about-mysql-and-timezones/

https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html