在 MySQL 中處理時間

作者：Hunter Coleman 2023-05-04 07:43:47

這篇關于 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）

責任編輯：龐桂玉來源： Linux中國

MySQL 數據庫