服务器之家

服务器之家 > 正文

mysql中TIMESTAMPDIFF案例详解

时间:2021-09-18 18:28     来源/作者:baiyaoliang7445

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 之间有几个月
?
1
2
3
4
5
6
7
8
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 之间有几天
?
1
2
3
4
5
6
7
8
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 之间有几分钟
?
1
2
3
4
5
6
7
8
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 之间有几分钟
?
1
2
3
4
5
6
7
8
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相减

mysql中TIMESTAMPDIFF案例详解

  • 3.6 对于 SECOND 会怎样计算呢
?
1
2
3
4
5
6
7
8
9
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  建表
      ?
      1
      2
      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  添加数据
      ?
      1
      2
      3
      4
      5
      6
      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数据
      ?
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      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
      ?
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      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 其他应用
      ?
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      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内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家!

原文链接:https://blog.csdn.net/baiyaoliang7445/article/details/102218767

标签:

相关文章

热门资讯

yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总
苹果12mini价格表官网报价 iPhone12mini全版本价格汇总 2020-11-13
2021年耽改剧名单 2021要播出的59部耽改剧列表
2021年耽改剧名单 2021要播出的59部耽改剧列表 2021-03-05
返回顶部