關於時區(一)
由於碰到了些設定時區上的問題,所以花了點時間來研究時區在Linux、SQL及Node JS之間的關係。首先我們先從UTC開始做介紹:
UTC 是什麼?能表示時區嗎?
UTC的英文全名為Coordinated Universal Time,是世界標準時間 。它是透過原子鐘來進行計時,也成為國際通訊的通用標準。
同時,它也能用來表示時區。例如,台灣就是UTC+8的時區。
Timestamp 是什麼?能表示時區嗎?
時間戳是由格林威治時間1970年1月1日(00:00:00)至今的毫秒數。也稱之為「Unix Timestamp」,為一種時間表示方式。同時也表示UTC+0這個地方的時區。
DB 能不能設定時區? (以 mysql 為例)
可以,我們可透過下述指令就能查詢目前時區設定為何:
mysql> SHOW VARIABLES LIKE '%time_zone%';
並可透過下述指令來取得現在時間:
mysql> SELECT now();
若要改變時區,以台灣為例,只要使用下述指令就可以:
mysql> SET time_zone = "+8:00"; // UTC+8為台灣區的時間
像澳洲的時區的話就會是:
mysql> SET time_zone = "+10:00"; // UTC+10為澳洲區的時間
補充說明:
DB能透過「DATETIME」、「DATE」和「TIMESTAMP」甚至是「VARCHAR」的字串型態來呈現時間。其格式分別為:
- DATETIME
> YYYY-MM-DD HH:MM:SS
> 可為NULL。
> 佔8Bytes
> 若只有輸入YYYY-MM-DD剩下的HH:MM:SS會自動幫我們輸入為0。 - DATE
> YYYY-MM-DD
> 可為NULL。
> 佔3Bytes - TIMESTAMP
> YYYY-MM-DD HH:MM:SS
> 不能為NULL。
> 佔4Bytes
> 雖能有多個type為TIMESTAMP的field,但沒有意義,因為value只能由DB自動帶入或修改。 - VARCHAR
> 自行設定呈現格式
> 可為NULL
> 可自行調配Bytes
由上述例子可以觀察到,除了DATE只能顯示到年、月、日,DATETIME及TIMESTAMP都可以呈現到毫秒的時間。
除了TIMESTAMP之外,DATETIME及DATE都需至少輸入value為YYYY-MM-DD才可以,否則DB會回覆ERROR 1292 (22007): Incorrect datetime value的錯誤。
程式能不能設定時區?(以 nodejs 為例)
原則上是不行,因為它是直接取得系統當地的UTC時間。
我們可以透過下述程式來呈現目前的時間:
但若要透過該UTC時間來換算出其他時區的時間可透過下述程式達成:
這部份參考自Convert the local time to another time zone with this JavaScript這篇的做法做出。
只要得知GMT的時區就能推算出來,如澳洲為GMT+10。
系統能不能設定時區?(以 ubuntu 為例)
可以,我們可以在ubuntu的Terminal環境下,透過指令sudo dpkg-reconfigure tzdata來調整時區。之後在選擇想要設定的時區後,重啟電腦就設定完成了。
$ sudo dpkg-reconfigure tzdata
額外補充:
- 顯示現在系統時間
$ date
四 4月 6 17:26:06 CST 2017 //CST為大中華地區的時間
- 只顯示年月日時間
$ date +%Y/%m/%dT%T //需注意的是指令中間並不能空白,所以+個T來做區分
2017/04/06T17:31:14
- 透過systemd來檢視系統時間
$ timedatectl
Local time: 四 2017-04-06 17:33:17 CST
Universal time: 四 2017-04-06 09:33:17 UTC
RTC time: 四 2017-04-06 17:33:18
Time zone: Asia/Taipei (CST, +0800)
Network time on: yes
NTP synchronized: no
RTC in local TZ: yes
延伸議題:
若我們在一個MySQL的table中放一個field type為timestamp的欄位
然後 INSERT一個value它會呈現什麼樣子?
它還是會呈現原本輸入的值。
mysql> INSERT INTO test (name ,time) VALUES (‘Penguin‘ ,‘2017–04–05 18:19:03’);
mysql> select * from test;
+----+----------+---------------------+
| id | name | time |
+----+----------+---------------------+
| 1 | Penguin | 2017-04-05 18:19:03 |
+----+----------+---------------------+
請問分別在伺服器上用 UTC 跟用LT(本地時區)的好處跟壞處在哪?
- UTC的運作方式是從1970年1月1日以來的毫秒數,所以並不會因為DST(Daylight Savings Time,夏季節約時間)的變化而影響。
- LT是基於UTC的時間在加上各個地區的不同而改變,會受DST的影響。
若今天伺服器的時區設置為UTC,而接收伺服器資料的接收端其軟體是使用LT,則可能會出現問題。這問題就是會因為地區的時區不同以及DST的影響會導致時間錯誤,且若是軟體有原本需要處理的流程,則可能會有處理兩次或不處理的問題。
若要解決這個問題我們可以使用UTC的時間作為基礎,透過程式來計算及調整不同區域所需要的LT。但還是建議將server端的時間使用UTC來儲存時間資料比較好,這是為了保有資料的一致性,在後續若要修改或維護也比較方便。
系統(伺服器)的時區設定是否會影響 DB 中的資料?
不一定。
如果SQL的time_zone沒有特別設定,則所有日期和時間值都是來自執行SQL Server其電腦的作業系統。
但若是有個別設定SQL的time_zone,則會以設定的時區為主。
宣告一個field type為boolean欄位,但他實際上的field type並不是boolean。
這是發生什麼問題?它實際上開的型別跟 boolean 是什麼關係?
可以透過下述方式來進行測試:
在MySQL的情況下,先開一個field type為boolean的欄位,並使用DESCRIBE指令來觀察欄位。
mysql> ALTER TABLE test ADD test_boolean boolean;
mysql> DESCRIBE test;
+--------------+-------------+------+-----+-------------------+
| Field | Type | Null | Key | Default |
+--------------+-------------+------+-----+-------------------+
| test_boolean | tinyint(1) | YES | | NULL |
+--------------+-------------+------+-----+-------------------+
可以發現到它將field type轉變為是tinyint(1),這是因為MySQL(及MariaDB)本身並沒有boolean的field type。但它還是能提供像boolean的true或false的辨識功能,比如說我們輸入值為false的資料,它就會直接幫我們轉成0。
mysql> INSERT INTO test (test_boolean) values (false);
mysql> SELECT * FROM test;
+--------------+
| test_boolean |
+--------------+
| 0 |
+--------------+
這是因為TRUE or true會是1,而FALSE or false會是0,以此做為辨識。
參考資料: