MySQL datetime型別

2019-10-16 22:58:38

在本教學中,您將了解MySQL DATETIME資料型別以及如何使用一些方便的函式來有效地操作DATETIME型別資料。

MySQL DATETIME資料型別簡介

MySQL DATETIME儲存包含日期時間的值。 當您從DATETIME查詢資料時,MySQL會以以下格式顯示DATETIME值:

YYYY-MM-DD HH:MM:SS

預設情況下,DATETIME的值範圍為1000-01-01 00:00:009999-12-31 23:59:59

DATETIME值使用5個位元組進行儲存。另外,DATETIME值可以包括格式為YYYY-MM-DD HH:MM:SS [.fraction]例如:2017-12-20 10:01:00.999999的尾數有小數秒。 當包含小數秒精度時,DATETIME值需要更多儲存,如下表所示:

分數秒精度 儲存(位元組)
0 0
1,2 1
3,4 2
5,6 3

例如,2017-12-20 10:01:00.999999需要8個位元組,2015-12-20 10:01:00需要5個位元組,3個位元組為.999999,而2017-12-20 10:01:00.9只需要6個位元組,小數秒精度為1位元組。

請注意,在MySQL 5.6.4之前,DATETIME值需要8位元組儲存而不是5個位元組。

MySQL DATETIME與TIMESTAMP型別

MySQL提供了另一種類似於DATETIME,叫作TIMESTAMP的時間資料型別。

TIMESTAMP需要4個位元組,而DATETIME需要5個位元組。 TIMESTAMPDATETIME都需要額外的位元組,用於分數秒精度。

TIMESTAMP值範圍從1970-01-01 00:00:01 UTC2038-01-19 03:14:07 UTC。 如果要儲存超過2038的時間值,則應使用DATETIME而不是TIMESTAMP

MySQL將TIMESTAMP儲存在UTC(有時區)值中。 但是,MySQL儲存DATETIME值是沒有時區的。下面來看看看下面的例子。

首先,將當前連線的時區設定為+00:00

接下來,建立一個名為timestamp_n_datetime的表,它由兩列組成:tsdt,這兩列分別使用TIMESTAMPDATETIME型別,如以下語句 -

USE testdb;

CREATE TABLE timestamp_n_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);

然後,將當前日期和時間插入timestamp_n_datetime表的tsdt列中,如下語句所示 -

INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());

之後,從timestamp_n_datetime查詢資料,如下語句所示 -

SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;

執行上面查詢語句,得到以下結果 -

+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2017-07-26 00:38:14 | 2017-07-26 00:38:14 |
+---------------------+---------------------+
1 row in set

DATETIMETIMESTAMP列中的兩個值相同。

最後,將連線的時區設定為+03:00,再次從timestamp_n_datetime表查詢資料。

SET time_zone = '+03:00';

SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;

執行上面查詢語句,得到以下結果 -

+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2017-07-25 19:38:14 | 2017-07-26 00:38:14 |
+---------------------+---------------------+
1 row in set

可以看到,ts列為TIMESTAMP資料型別的值變了。這是因為在更改時區時,TIMESTAMP列以UTC為單位儲存日期和時間值,根據新時區調整TIMESTAMP列的值。

這意味著如果使用TIMESTAMP資料來儲存日期和時間值,則在將資料庫移動到位於不同時區的伺服器時時間的值可能不一樣,所以應該認真考慮這個問題。

MySQL DATETIME函式

以下語句使用NOW()函式將變數@dt設定為當前日期和時間。

SET @dt =  NOW();

要查詢@dt變數的值,請使用以下SELECT語句:

SELECT @dt;

執行上面查詢語句,得到以下結果 -

mysql> SELECT @dt;
+---------------------+
| @dt                 |
+---------------------+
| 2017-07-25 19:41:14 |
+---------------------+
1 row in set

MySQL DATE函式

要從DATETIME值提取日期部分,請使用DATE函式,如下所示:

mysql> SELECT DATE(@dt);
+------------+
| DATE(@dt)  |
+------------+
| 2017-07-25 |
+------------+
1 row in set

如果希望根據日期查詢資料,但是列中儲存的資料是基於日期和時間,則此功能非常有用。

下面來看看看下面的例子。

USE testdb;

CREATE TABLE test_dt (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME
);

INSERT INTO test_dt(created_at)
VALUES('2017-11-05 20:29:36');

假設您想知道在2017-11-05當天建立的行,請使用以下查詢:

SELECT 
    *
FROM
    test_dt
WHERE
    created_at = '2017-11-05';

執行上面查詢語句,得到以下結果 -

mysql> SELECT 
    *
FROM
    test_dt
WHERE
    created_at = '2017-11-05';
Empty set

它不返回任何行記錄。

這是因為created_at列不僅包含日期,還包含時間。要糾正它,請使用DATE函式,如下所示:

SELECT 
    *
FROM
    test_dt
WHERE
    DATE(created_at) = '2017-11-05';

執行上面查詢語句,得到以下結果 -

+----+---------------------+
| id | created_at          |
+----+---------------------+
|  1 | 2017-11-05 20:29:36 |
+----+---------------------+
1 row in set

它按預期返回一行。 如果表有多行,MySQL必須執行全表掃描以查詢與條件匹配的行。

MySQL TIME函式

要從DATETIME值中提取時間部分,可以使用TIME函式,如以下語句所示:

SELECT TIME(@dt);

執行上面查詢語句,得到以下結果 -

mysql> SELECT TIME(@dt);
+-----------+
| TIME(@dt) |
+-----------+
| 19:41:14  |
+-----------+
1 row in set

MySQL YEAR, QUARTER, MONTH, WEEK, DAY, HOUR,MINUTE和SECOND函式

要從DATETIME值獲取年,季,月,周,日,小時,分和秒,可以使用以下語句中所示的函式:

SET @dt =  NOW();
SELECT 
    HOUR(@dt),
    MINUTE(@dt),
    SECOND(@dt),
    DAY(@dt),
    WEEK(@dt),
    MONTH(@dt),
    QUARTER(@dt),
    YEAR(@dt);

執行上面查詢語句,得到以下結果 -

+-----------+-------------+-------------+----------+-----------+------------+--------------+-----------+
| HOUR(@dt) | MINUTE(@dt) | SECOND(@dt) | DAY(@dt) | WEEK(@dt) | MONTH(@dt) | QUARTER(@dt) | YEAR(@dt) |
+-----------+-------------+-------------+----------+-----------+------------+--------------+-----------+
|        19 |          42 |          56 |       25 |        30 |          7 |            3 |      2017 |
+-----------+-------------+-------------+----------+-----------+------------+--------------+-----------+
1 row in set

MySQL DATE_FORMAT函式

要格式化DATETIME值,可以使用DATE_FORMAT函式。 例如,以下語句基於%H:%i:%s - %W%M%Y格式來格式化DATETIME值:

SET @dt =  NOW();
SELECT DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y');

執行上面查詢語句,得到以下結果 -

+-----------------------------------------+
| DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y') |
+-----------------------------------------+
| 19:43:10 - Tuesday July 2017            |
+-----------------------------------------+
1 row in set

MySQL DATE_ADD函式

要將間隔新增到DATETIME值,請使用DATE_ADD函式,如下所示:

SET @dt =  NOW();
SELECT @dt start, 
       DATE_ADD(@dt, INTERVAL 1 SECOND) '1 second later',
       DATE_ADD(@dt, INTERVAL 1 MINUTE) '1 minute later',
       DATE_ADD(@dt, INTERVAL 1 HOUR) '1 hour later',
       DATE_ADD(@dt, INTERVAL 1 DAY) '1 day later',
       DATE_ADD(@dt, INTERVAL 1 WEEK) '1 week later',
       DATE_ADD(@dt, INTERVAL 1 MONTH) '1 month later',
       DATE_ADD(@dt, INTERVAL 1 YEAR) '1 year later';

執行上面查詢語句,得到以下結果 -

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| start               | 1 second later      | 1 minute later      | 1 hour later        | 1 day later         | 1 week later        | 1 month later       | 1 year later        |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2017-07-25 19:43:22 | 2017-07-25 19:43:23 | 2017-07-25 19:44:22 | 2017-07-25 20:43:22 | 2017-07-26 19:43:22 | 2017-08-01 19:43:22 | 2017-08-25 19:43:22 | 2018-07-25 19:43:22 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row in set

MySQL DATE_SUB函式

要從DATETIME值中減去一個間隔值,可以使用DATE_SUB函式,如下所示:

SET @dt =  NOW();
SELECT @dt start, 
       DATE_SUB(@dt, INTERVAL 1 SECOND) '1 second before',
       DATE_SUB(@dt, INTERVAL 1 MINUTE) '1 minute before',
       DATE_SUB(@dt, INTERVAL 1 HOUR) '1 hour before',
       DATE_SUB(@dt, INTERVAL 1 DAY) '1 day before',
       DATE_SUB(@dt, INTERVAL 1 WEEK) '1 week before',
       DATE_SUB(@dt, INTERVAL 1 MONTH) '1 month before',
       DATE_SUB(@dt, INTERVAL 1 YEAR) '1 year before';

執行上面查詢語句,得到以下結果 -

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| start               | 1 second before     | 1 minute before     | 1 hour before       | 1 day before        | 1 week before       | 1 month before      | 1 year before       |
+

MySQL DATE_DIFF函式

要計算兩個DATETIME值之間的差值,可以使用DATEDIFF函式。 請注意,DATEDIFF函式僅在計算中考慮DATETIME值的日期部分。

請參見以下範例。

首先,建立一個名為datediff_test的表,其中只有一個dt列,其資料型別為DATETIME

USE testdb;
CREATE TABLE datediff_test (
    dt DATETIME
);

其次,將一些行插入到datediff_test表中。

INSERT INTO datediff_test(dt)
VALUES('2017-04-30 07:27:39'),
 ('2017-05-17 22:52:21'),
 ('2017-05-18 01:19:10'),
 ('2017-05-22 14:17:16'),
 ('2017-05-26 03:26:56'),
 ('2017-06-10 04:44:38'),
 ('2017-06-13 13:55:53');

第三,使用DATEDIFF函式將當前日期時間與datediff_test表的每一行中的值進行比較。

SELECT 
    dt, 
    DATEDIFF(NOW(), dt)
FROM
    datediff_test;

執行上面查詢語句,得到以下結果 -

+---------------------+---------------------+
| dt                  | DATEDIFF(NOW(), dt) |
+---------------------+---------------------+
| 2017-04-30 07:27:39 |                  86 |
| 2017-05-17 22:52:21 |                  69 |
| 2017-05-18 01:19:10 |                  68 |
| 2017-05-22 14:17:16 |                  64 |
| 2017-05-26 03:26:56 |                  60 |
| 2017-06-10 04:44:38 |                  45 |
| 2017-06-13 13:55:53 |                  42 |
+---------------------+---------------------+
7 rows in set

在本教學中,您已經了解了MySQL DATETIME資料型別和一些有用的DATETIME函式。