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

mysql中TIMESTAMPDIFF案例详解

1. syntax

2. unit

3. example

相关推荐