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内容请搜索本站以前的文章或继续浏览下面的相关文章希望大家以后多多支持本站！

海外服务器租用

版权声明：本站文章来源标注为YINGSOO的内容版权均为本站所有，欢迎引用、转载，请保持原文完整并注明来源及原文链接。禁止复制或仿造本网站，禁止在非www.yingsoo.com所属的服务器上建立镜像，否则将依法追究法律责任。本站部分内容来源于网友推荐、互联网收集整理而来，仅供学习参考，不代表本站立场，如有内容涉嫌侵权，请联系alex-e#qq.com处理。