mysql中TIMESTAMPDIFF案例详解
作者:baiyaoliang7445 时间:2024-01-18 05:56:49
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)
来源:https://blog.csdn.net/baiyaoliang7445/article/details/102218767
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Python语音合成之第三方库gTTs/pyttsx3/speech横评(内附使用方法)
python3实现网页版raspberry pi(树莓派)小车控制
![](https://img.aspxhome.com/file/2023/3/101283_0s.jpg)
pandas 转换成行列表进行读取与Nan处理的方法
Utf-8和Gb2312乱码问题的终结
js实现文本框宽度自适应文本宽度的方法
![](https://img.aspxhome.com/file/2023/7/136067_0s.jpg)
vue-cli脚手架引入弹出层layer插件的几种方法
在Python的web框架中中编写日志列表的教程
![](https://img.aspxhome.com/file/2023/0/102820_0s.png)
JS中的算法与数据结构之集合(Set)实例详解
Python的子线程和子进程是如何手动结束的?
Google谷歌农历鼠年春节的变化
![](https://img.aspxhome.com/file/UploadPic/20082/11/200821117922974s.png)
python3.5的包存放的具体路径
Go语言sync包与锁实现限制线程对变量的访问
Python利用Pillow(PIL)库实现验证码图片的全过程
![](https://img.aspxhome.com/file/2023/7/81457_0s.png)
Python制作进度条的四种方法总结
![](https://img.aspxhome.com/file/2023/1/78561_0s.gif)
浅谈Oracle数据库的建模与设计
Vue2.0 axios前后端登陆拦截器(实例讲解)
![](https://img.aspxhome.com/file/2023/9/139869_0s.jpg)
Python中常用的内置方法
![](https://img.aspxhome.com/file/2023/2/128502_0s.png)
CSS控制字体效果的思考
![](https://img.aspxhome.com/file/UploadPic/20072/20072311315168s.gif)
对python中array.sum(axis=?)的用法介绍
![](https://img.aspxhome.com/file/2023/4/90614_0s.jpg)