mysql中TIMESTAMPDIFF案例詳解
更新時間:2021年08月13日 16:15:30 作者:baiyaoliang7445
這篇文章主要介紹了mysql中TIMESTAMPDIFF案例詳解,本篇文章通過簡要的案例,講解了該項技術的了解與使用,以下就是詳細內容,需要的朋友可以參考下
1. Syntax
TIMESTAMPDIFF(unit,begin,end); 根據(jù)單位返回時間差,對于傳入的begin和end不需要相同的數(shù)據(jù)結構,可以存在一個為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 如何求數(shù)據(jù)庫中兩個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 添加數(shù)據(jù)
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數(shù)據(jù)
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)
- 3.7.1 建表
到此這篇關于mysql中TIMESTAMPDIFF案例詳解的文章就介紹到這了,更多相關mysql中TIMESTAMPDIFF內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
MySQL?數(shù)據(jù)庫的對庫的操作及其數(shù)據(jù)類型
這篇文章主要介紹了MySQL?數(shù)據(jù)庫的對庫的操作及其數(shù)據(jù)類型,下面文字圍繞數(shù)據(jù)庫的對庫的操作及其數(shù)據(jù)類型的相關資料展開詳細介紹,需要的小伙伴可以參考一下,希望對你有所幫助2021-12-12淺析刪除表的幾種方法(delete、drop、truncate)
這篇文章主要介紹了刪除表的幾種方法,需要的朋友可以參考下2014-05-05