mysql中TIMESTAMPDIFF案例詳解

更新時間：2021年08月13日 16:15:30 作者：baiyaoliang7445

這篇文章主要介紹了mysql中TIMESTAMPDIFF案例詳解,本篇文章通過簡要的案例,講解了該項技術的了解與使用,以下就是詳細內容,需要的朋友可以參考下

1. Syntax

TIMESTAMPDIFF(unit,begin,end); 根據單位返回時間差,對于傳入的begin和end不需要相同的數據結構,可以存在一個為Date一個DateTime

2. Unit

支持的單位有

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

3. Example

下面這個例子是對于TIMESTAMPDIFF最基本的用法，

3.1 求 2017-01-01 - 2017-02-01 之間有幾個月

SELECT TIMESTAMPDIFF(MONTH, '2017-01-01', '2017-02-01') as  result;

+--------+
| result |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)

3.2 求 2017-01-01 - 2017-02-01 之間有幾天

SELECT TIMESTAMPDIFF(DAY, '2017-01-01', '2017-02-01') as  result;

+--------+
| result |
+--------+
| 31 |
+--------+
1 row in set (0.00 sec)

3.3 求 2017-01-01 08: 00:00 - 2017-01-01 08: 55:00 之間有幾分鐘

SELECT TIMESTAMPDIFF(MINUTE, '2017-01-01 08:00:00', '2017-01-01 08:55:00') result;

+--------+
| result |
+--------+
| 55 |
+--------+
1 row in set (0.00 sec)

3.4 求 2017-01-01 08: 00:00 - 2017-01-01 08: 55:33 之間有幾分鐘

SELECT TIMESTAMPDIFF(MINUTE, '2017-01-01 08:00:00', '2017-01-01 08:55:33') result;

+--------+
| result |
+--------+
| 55 |
+--------+
1 row in set (0.00 sec)

3.5 對于DAY， MINUTE進行計算DIFF時，會直接將相對應的DAY，MINUTE相減

3.6 對于 SECOND 會怎樣計算呢

SELECT TIMESTAMPDIFF(SECOND, '2017-01-01 08:00:00', '2017-01-01 08:55:33') result;

55 * 60 + 33 = 3333
+--------+
| result |
+--------+
|   3333 |
+--------+
1 row in set (0.00 sec)

3.7 如何求數據庫中兩個date字段的diff

3.7.1 建表

CREATE TABLE demo (id INT AUTO_INCREMENT PRIMARY KEY, start_time DATE NOT NULL, end_time DATE NOT NULL);
Query OK, 0 rows affected (0.10 sec)

3.7.2 添加數據

INSERT INTO demo(start_time, end_time)
VALUES('1983-01-01', '1990-01-01'),
('1983-01-01', '1989-06-06'),
('1983-01-01', '1985-03-02'),
('1983-01-01', '1992-05-05'),
('1983-01-01 11:12:11', '1995-12-01');

3.7.3 直接query數據

select * from demo;
+----+------------+------------+
| id | start_time | end_time |
+----+------------+------------+
| 1 | 1983-01-01 | 1990-01-01 |
| 2 | 1983-01-01 | 1989-06-06 |
| 3 | 1983-01-01 | 1985-03-02 |
| 4 | 1983-01-01 | 1992-05-05 |
| 5 | 1983-01-01 | 1995-12-01 |
+----+------------+------------+
5 rows in set (0.00 sec)

3.7.4 計算duration

select *, TIMESTAMPDIFF(YEAR, start_time, end_time) as duration from demo;

+----+------------+------------+----------+
| id | start_time | end_time | duration |
+----+------------+------------+----------+
| 1 | 1983-01-01 | 1990-01-01 | 7 |
| 2 | 1983-01-01 | 1989-06-06 | 6 |
| 3 | 1983-01-01 | 1985-03-02 | 2 |
| 4 | 1983-01-01 | 1992-05-05 | 9 |
| 5 | 1983-01-01 | 1995-12-01 | 12 |
+----+------------+------------+----------+
5 rows in set (0.00 sec)

3.7.5 其他應用

select *, if(TIMESTAMPDIFF(YEAR, end_time, CURRENT_TIMESTAMP())< 26 ,'< 26','>= 26') as result from demo;

+----+------------+------------+--------+
| id | start_time | end_time | result |
+----+------------+------------+--------+
| 1 | 1983-01-01 | 1990-01-01 | >= 26 |
| 2 | 1983-01-01 | 1989-06-06 | >= 26 |
| 3 | 1983-01-01 | 1985-03-02 | >= 26 |
| 4 | 1983-01-01 | 1992-05-05 | < 26 |
| 5 | 1983-01-01 | 1995-12-01 | < 26 |
+----+------------+------------+--------+
5 rows in set (0.00 sec)

到此這篇關于mysql中TIMESTAMPDIFF案例詳解的文章就介紹到這了,更多相關mysql中TIMESTAMPDIFF內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: