在 MySQL 中處理時間

這篇關於 MySQL 中日期和時間的概述將幫助你在資料庫表中處理時間值。

流行資料庫系統 MySQL 的新老用戶常常會對資料庫處理時間值的方式感到困惑。有時用戶不會費心去了解時間值的數據類型。這可能是因為他們覺得本身也沒有什麼好了解的。日期就是日期，對吧？好吧，並非總是如此。花幾分鐘時間了解 MySQL 如何存儲和顯示日期和時間是有益的。學習如何最好地利用資料庫表中的時間值可以幫助你成為更好的編碼者。

MySQL 時間值類型

當你在 MySQL 中新建表時，選擇合適的數據類型（INT、FLOAT、CHAR 等）高效地保存插入到表中的數據。MySQL 為時間值提供了五種數據類型。它們是 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。

MySQL 使用 ISO 8601 格式來存儲以下格式的值（LCTT 譯註：國際標準 ISO 8601，是國際標準化組織的日期和時間的表示方法，全稱為《數據存儲和交換形式·信息交換·日期和時間的表示方法》）：

• DATE：YYYY-MM-DD
• TIME：HH:MM:SS
• TIMESTAMP：YYYY-MM-DD HH:MM:SS
• YEAR：YYYY

DATETIME 與 TIMESTAMP 的比較

你可能已經注意到 日期時間 DATETIME 和 時間戳 TIMESTAMP 數據類型存有相同的數據。你可能想知道這兩者之間是否有差異。答案是：有。

首先，可以使用的日期範圍不同。DATETIME 可以保存 1000-01-01 00:00:00 和 9999-12-31 23:59:59 之間的日期，而 TIMESTAMP 的範圍更有限，從 1970-01-01 00:00:01 到 2038-01-19 03:14:07 UTC。

其次，雖然兩種數據類型都允許你 自動初始化 auto_initialize 或 自動更新 auto_update 它們各自的值（分別用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP），但在 5.6.5 版本之前，對 DATETIME 值不能這樣操作。如果你要用 DATETIME，你可以使用 CURRENT_TIMESTAMP 的 MySQL 同義詞之一，例如 NOW() 或 LOCALTIME()。

如果你對一個 DATETIME 值使用 ON UPDATE CURENT_TIMESTAMP（或其同義詞之一），但沒有使用 DEFAULT CURRENT_TIMESTAMP 子句，那麼這個列的默認值為 NULL。除非你在表的定義中包含 NOT NULL，在這種情況下，它默認為 0。

另一件需要記住的重要事情是，儘管通常情況下，除非你聲明一個默認值，否則 DATETIME 和 TIMESTAMP 列都沒有一個默認值，但這個規則有一個例外。如果沒有指定 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 這兩個子句，並且禁用 explicit_defaults_for_timestamp 這個變數，那麼你表中的第一個 TIMESTAMP 列將被隱式創建。

要檢查這個變數的狀態，請運行：

mysql> show variables like 'explicit_default%';

如果你想打開或關閉它，運行這段代碼（用 0 表示關閉，用 1 表示打開）：

mysql> set explicit_defaults_for_timestamp = 0;

TIME

MySQL 的 時間 TIME 數據類型可能看起來很簡單，但有幾件事是一個優秀的程序員應該牢記的。

首先要注意的是，雖然 TIME 經常被認為是一天中的時間，但它實際上是經過的時間。換句話說，它可以是一個負值，或者可以大於 23:59:59。在 MySQL 中，一個 TIME 值的範圍可以是 -838:59:59 到 838:59:59。

另外，如果你縮寫一個時間值，MySQL 會因你是否使用冒號作出不同解釋。例如，10:34 這個值被 MySQL 看作是 10:34:00。也就是說，十點過後的 34 分鐘。但是，如果你不使用冒號寫作 1034，MySQL 將其視為 00:10:34，意思是 10 分鐘 34 秒。

最後，你應該知道 TIME 值（以及 DATETIME 和 TIMESTAMP 欄位的時間部分）從 5.6.4 版本開始，可以取一個小數部分。要使用它，請在數據類型定義的結尾處添加一個整數（最大值為 6）的圓括弧。

time_column TIME(2)

時區

時區變化不僅在現實世界中產生混亂和疲勞，而且也會在資料庫系統中製造麻煩。地球被劃分為 24 個獨立的時區，通常每隔 15 度經度就會發生變化。我說通常是因為一些國家行事方式不同。例如中國只在一個時區運作，而不是預期的五個時區。

你如何處理處於不同時區的資料庫系統的用戶就成了一個問題。幸運的是，MySQL 並沒有使這個問題變得太困難。

要檢查你的會話時區，請運行：

mysql> select @@session.time_zone;

如果結果顯示 System，這意味著它正在使用你的 my.cnf 配置文件中設置的時區。如果你在本地計算機上運行你的 MySQL 伺服器，這可能就是你會得到的，你不需要做任何改變。

如果你想改變你的會話的時區，請運行如下命令：

mysql> set time_zone = '-05:00';

這將你的時區設置為 美國/東部 US/Eastern ，比 協調世界時 UTC 晚五個小時。

獲得一周的日期

為了跟上本教程後面部分的代碼，你應該在你的系統中創建一個帶有日期值類型的表。比如：

mysql> create table test
( row_id smallint not null auto_increment primary key,
the_date date not null);

然後使用 ISO 8601 格式在表中插入一些隨機日期，如

mysql> insert into test (the_date) VALUES ('2022-01-05');

我在我的 test 表中插入了四行日期值，你插入多少行都可以。

有時你可能想知道某一天是星期幾。MySQL 給了你幾種實現方法。

第一種，也是最顯而易見的方法，是使用 DAYNAME() 函數。如下示例表所展示，DAYNAME() 函數可以告訴你每個日期是星期幾：

mysql> SELECT the_date, DAYNAME(the_date) FROM test;
+------------+-------------------------------+
| the_date   | DAYNAME(the_date)             |
+------------+-------------------------------+
| 2021-11-02 | Tuesday                       |
| 2022-01-05 | Wednesday                     |
| 2022-05-03 | Tuesday                       |
| 2023-01-13 | Friday                        |
+------------+-------------------------------+
4 rows in set (0.00 sec)

另外兩種獲取星期幾的方法是返回整數值，而不是星期幾的名稱，分別是 WEEKDAY() 和 DAYOFWEEK()。他們都返回數字，卻又各不相同。WEEKDAY() 函數返回從 0 到 6 的數字，其中 0 代表星期一，6 代表星期日。而 DAYOFWEEK() 則返回從 1 到 7 的數字，其中 1 代表星期日，7 代表星期六。

mysql> SELECT the_date, DAYNAME(the_date),
WEEKDAY(the_date), DAYOFWEEK(the_date) FROM test;
+------------+------------------+------------------+--------------------+
| the_date   | DAYNAME(the_date)| WEEKDAY(the_date)| DAYOFWEEK(the_date)|
| 2021-11-02 | Tuesday          | 1                | 3                  |
| 2022-01-05 | Wednesday        | 2                | 4                  |
| 2022-05-03 | Tuesday          | 1                | 3                  |
| 2023-01-13 | Friday           | 4                | 6                  |
+------------+------------------+------------------+--------------------+
4 rows in set (0.00 sec)

當你只想獲取日期的一部分時

有時你可能在 MySQL 表中存儲了一個日期，但是你只想獲取日期的一部分。這並不是問題。

MySQL 中有幾個顧名思義的函數，可以輕鬆獲取日期對象的特定部分。以下是一些示例：

mysql> SELECT the_date, YEAR(the_date), MONTHNAME(the_date), 
DAYOFMONTH(the_date) FROM test ;
+-----------+---------------+-------------------+---------------------+
| the_date  | YEAR(the_date)|MONTHNAME(the_date)| DAYOFMONTH(the_date)|
+-----------+---------------+-------------------+---------------------+
| 2021-11-02| 2021          | November          | 2                   |
| 2022-01-05| 2022          | January           | 5                   |
| 2022-05-03| 2022          | May               | 3                   |
| 2023-01-13| 2023          | January           | 13                  |
+-----------+---------------+-------------------+---------------------+
4 rows in set (0.00 sec)

MySQL 也允許你使用 EXTRACT() 函數來獲取日期的一部分。你提供給函數的參數是一個單位說明符（確保是單數形式）、FROM 和列名。因此，為了從我們的 test 表中僅獲取年份，你可以寫：

mysql> SELECT EXTRACT(YEAR FROM the_date) FROM test;
+----------------------------------------------+
| EXTRACT(YEAR FROM the_date)                  |
+----------------------------------------------+
| 2021                                         |
| 2022                                         |
| 2022                                         |
| 2023                                         |
+----------------------------------------------+
4 rows in set (0.01 sec)

插入和讀取不同格式的日期

正如之前提到的，MySQL 使用 ISO 8601 格式存儲日期和時間值。但是如果你想以另一種方式存儲日期和時間值，例如 MM-DD-YYYY 格式，怎麼辦？首先，不要嘗試這樣做。MySQL 以 8601 格式存儲日期和時間，就是這樣。不要嘗試更改它。但是，這並不意味著你必須在將數據輸入到資料庫之前將數據轉換為特定的格式，或者你不能以任何你想要的格式展示數據。

如果你想要將非 ISO 的格式的日期輸入到表中，你可以使用 STR_TO_DATE() 函數。第一個參數是你想要存儲在資料庫中的日期的字元串值。第二個參數是格式化字元串，它讓 MySQL 知道日期的組織方式。讓我們看一個簡單的例子，然後我將更深入地研究這個看起來很奇怪的格式化字元串是什麼。

mysql> insert into test (the_date) values (str_to_date('January 13, 2023','%M %d, %Y'));

Query OK, 1 row affected (0.00 sec)

你將格式化字元串放在引號中，並在每個特殊字元前加上百分號。上面代碼中的格式序列告訴 MySQL 我的日期由一個完整的月份名稱 %M，後跟一個兩位數的日期%d，然後是一個逗號，最後由一個四位數的年份 %Y 組成。請注意，大寫很重要。

一些其他常用的格式化字元串字元是：

• %b 縮寫月份的名稱（例如: Jan）
• %c 數字月份（例如: 1）
• %W 星期名稱（例如: `Saturday）
• %a 星期名稱的縮寫（例如: Sat）
• %T 24 小時制的時間（例如: 22:01:22）
• %r 帶 AM/PM 的 12 小時制的時間（例如: 10:01:22 PM）
• %y 兩位數的年份（例如: 23）

請注意，對於兩位數年份 %y，年份範圍是 1970 到 2069。因此，從 70 到 99 的數字被假定為 20 世紀，而從 00 到 69 的數字被假定為 21 世紀。

如果你有一個日期存儲在你的資料庫中，你想用不同的格式顯示它，你可以使用這個 DATE_FORMAT() 函數：

mysql> SELECT DATE_FORMAT(the_date, '%W, %b. %d, %y') FROM test;
+-----------------------------------------+
| DATE_FORMAT(the_date, '%W, %b. %d, %y') |
+-----------------------------------------+
| Tuesday, Nov. 02, 21                    |
| Wednesday, Jan. 05, 22                  |
| Tuesday, May. 03, 22                    |
| Friday, Jan. 13, 23                     |
+-----------------------------------------+
4 rows in set (0.00 sec)

總結

本教程應該為你提供了一個關於 MySQL 中的日期和時間值的有用的概述。我希望本文教會了您一些新知識，使您能夠更好地控制和理解 MySQL 資料庫如何處理時間值。

（題圖：MJ/76b6481a-a271-4e81-bc17-dd7fbe08a240）

via: https://opensource.com/article/23/2/temporal-values-mysql

作者：Hunter Coleman 選題：lkxed 譯者：hanszhao80 校對：wxy

本文由 LCTT 原創編譯，Linux中國 榮譽推出

本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive