mysql计算时间差函数
mysql数据中计算时间差函数
MySql计算两个⽇期的时间差函数TIMESTAMPDIFF⽤法:
语法：
TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
说明：返回结果 datetime_expr2-datetime_expr1
  返回⽇期或⽇期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其结果的单位由interval 参数给出。interval 的法定值同TIMESTAMPADD()函数说明中所列出的相同
interval可是：
SECOND 秒 SECONDS
MINUTE 分钟 MINUTES
HOUR 时间 HOURS
DAY 天 DAYS
MONTH ⽉ MONTHS
YEAR 年 YEARS
(1)计算⽉份差
SELECT TIMESTAMPDIFF(MONTH,'2016-01-03','2016-04-08');
结果是：
(2)查看当前时间是now()，如：
select now();
结果是：
2016-04-08 17:44:01
(3)计算年份差
SELECT TIMESTAMPDIFF(YEAR,'2016-12-04','2017-12-04');
结果
(4)今天是2017-12-06，计算距离2016-12-06年差距
SELECT TIMESTAMPDIFF(YEAR,'2016-12-04',NOW());
结果:
(5)结合case..when 统计时间差:(今天是2017-12-06)
SELECT
(CASE WHEN TIMESTAMPDIFF(YEAR,TIME,NOW())=1THEN'1'ELSE'不够⼀年'END)    XX
FROM blacklist
结果:
blackList表的数据
-
--------第⼆种更全的---------------------------
select timediff('23:40:00', ' 18:30:00'); -- 两时间相减SELECT  substring( timediff('23:40:00', ' 18:30:00'),1,5) ----“05：10”相减返回⼩时：分钟
select datediff('2008-08-08', '2008-08-01'); -- 7      -----两⽇期相减
select TO_DAYS('2008-09-08')-TO_DAYS('2008-08-08')    -----两⽇期相减
SELECT  substring( '2009-06-17 10:00:00',  1,  10  )  ----从datetime中提取“⽇期”
（*********************
时间戳是从1970年1⽉1⽇开始到⽬标时间所经过的秒数.
可以进⾏两个datetime时间间隔的运算******************************）
⼀、MySQL 获得当前⽇期时间函数
1.1 获得当前⽇期+时间（date + time）函数：now()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2008-08-08 22:20:46 |
+---------------------+
除了 now() 函数能获得当前的⽇期时间外，MySQL 中还有下⾯的函数：
current_timestamp()
,current_timestamp
,localtime()
,localtime
,localtimestamp -- (v4.0.6)
,
localtimestamp() -- (v4.0.6)
这些⽇期时间函数，都等同于 now()。鉴于 now() 函数简短易记，建议总是使⽤ now() 来替代上⾯列出的函数。
1.2 获得当前⽇期+时间（date + time）函数：sysdate()
sysdate() ⽇期时间函数跟 now() 类似，不同之处在于：now() 在执⾏开始时值就得到了， sysdate() 在函数执⾏时动态得到值。看下⾯的例⼦就明⽩了：
mysql> select now(), sleep(3), now();
+---------------------+----------+---------------------+
| now() | sleep(3) | now() |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:21 | 0 | 2008-08-08 22:28:21 |
+---------------------+----------+---------------------+
mysql> select sysdate(), sleep(3), sysdate();
+---------------------+----------+---------------------+
| sysdate() | sleep(3) | sysdate() |
+---------------------+----------+---------------------+
| 2008-08-08 22:28:41 | 0 | 2008-08-08 22:28:44 |
+---------------------+----------+---------------------+
可以看到，虽然中途 sleep 3 秒，但 now() 函数两次的时间值是相同的； sysdate() 函数两次得到的时间值相差 3 秒。MySQL Manual 中是这样描述 sysdate() 的：Return the time at which the function executes。
sysdate() ⽇期时间函数，⼀般情况下很少⽤到。
2. 获得当前⽇期（date）函数：curdate()
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2008-08-08 |
+------------+
其中，下⾯的两个⽇期函数等同于 curdate()：
current_date()
,current_date
3. 获得当前时间（time）函数：curtime()
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 22:41:30 |
+-----------+
其中，下⾯的两个时间函数等同于 curtime()：
current_time()
,current_time
4. 获得当前 UTC ⽇期时间函数：utc_date(), utc_time(), utc_timestamp()
mysql> select utc_timestamp(), utc_date(), utc_time(), now()
+---------------------+------------+------------+---------------------+
| utc_timestamp() | utc_date() | utc_time() | now() |
+---------------------+------------+------------+---------------------+
| 2008-08-08 14:47:11 | 2008-08-08 | 14:47:11 | 2008-08-08 22:47:11 |
+---------------------+------------+------------+---------------------+
因为我国位于东⼋时区，所以本地时间 = UTC 时间 + 8 ⼩时。UTC 时间在业务涉及多个国家和地区的时候，⾮常有⽤。
⼆、MySQL ⽇期时间 Extract（选取）函数。
1. 选取⽇期时间的各个部分：⽇期、时间、年、季度、⽉、⽇、⼩时、分钟、秒、微秒
set @dt = '2008-09-10 07:15:30.123456';
select date(@dt); -- 2008-09-10
select time(@dt); -- 07:15:30.123456
select year(@dt); -- 2008
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 36
select day(@dt); -- 10
select hour(@dt); -- 7
select minute(@dt); -- 15
select second(@dt); -- 30mysql中select
select microsecond(@dt); -- 123456
2. MySQL Extract() 函数，可以上⾯实现类似的功能：
set @dt = '2008-09-10 07:15:30.123456';
select extract(year from @dt); -- 2008
select extract(quarter from @dt); -- 3
select extract(month from @dt); -- 9
select extract(week from @dt); -- 36
select extract(day from @dt); -- 10
select extract(hour from @dt); -- 7
select extract(minute from @dt); -- 15
select extract(second from @dt); -- 30
select extract(microsecond from @dt); -- 123456
select extract(year_month from @dt); -- 200809
select extract(day_hour from @dt); -- 1007
select extract(day_minute from @dt); -- 100715
select extract(day_second from @dt); -- 10071530
select extract(day_microsecond from @dt); -- 10071530123456
select extract(hour_minute from @dt); -- 715
select extract(hour_second from @dt); -- 71530
select extract(hour_microsecond from @dt); -- 71530123456
select extract(minute_second from @dt); -- 1530
select extract(minute_microsecond from @dt); -- 1530123456
select extract(second_microsecond from @dt); -- 30123456
MySQL Extract() 函数除了没有date(),time() 的功能外，其他功能⼀应具全。并且还具有选取‘day_microsecond’ 等功能。注意这⾥不是只选取 day 和 microsecond，⽽是从⽇期的 day 部分⼀直选取到 microsecond 部分。够强悍的吧！
MySQL Extract() 函数唯⼀不好的地⽅在于：你需要多敲⼏次键盘。
3. 函数：dayofweek(), dayofmonth(), dayofyear()
分别返回⽇期参数，在⼀周、⼀⽉、⼀年中的位置。
set @dt = '2008-08-08';
select dayofweek(@dt); -- 6
select dayofmonth(@dt); -- 8
select dayofyear(@dt); -- 221
⽇期 '2008-08-08' 是⼀周中的第 6 天（1 = Sunday, 2 = Monday, ..., 7 = Saturday）；⼀⽉中的第 8 天；⼀年中的第 221 天。
4. 函数：week(), weekofyear(), dayofweek(), weekday(), yearweek()
set @dt = '2008-08-08';
select week(@dt); -- 31
select week(@dt,3); -- 32
select weekofyear(@dt); -- 32
select dayofweek(@dt); -- 6
select weekday(@dt); -- 4
select yearweek(@dt); -- 200831
MySQL week() 函数，可以有两个参数，具体可看⼿册。 weekofyear() 和 week() ⼀样，都是计算“某天”是位于⼀年中的第⼏周。weekofyear(@dt) 等价于 week(@dt,3)。
MySQL weekday() 函数和 dayofweek() 类似，都是返回“某天”在⼀周中的位置。不同点在于参考的标准， weekday：(0 = Monday, 1 = Tuesday, ..., 6 = Sunday)； dayofweek：（1 = Sunday, 2 = Monday, ..., 7 = Saturday）
MySQL yearweek() 函数，返回 year(2008) + week 位置(31)。
5. MySQL 返回星期和⽉份名称函数：dayname(), monthname()
set @dt = '2008-08-08';
select dayname(@dt); -- Friday
select monthname(@dt); -- August
思考，如何返回中⽂的名称呢？
6. MySQL last_day() 函数：返回⽉份中的最后⼀天。
select last_day('2008-02-01'); -- 2008-02-29
select last_day('2008-08-08'); -- 2008-08-31
MySQL last_day() 函数⾮常有⽤，⽐如我想得到当前⽉份中有多少天，可以这样来计算：
mysql> select now(), day(last_day(now())) as days;
+---------------------+------+
| now() | days |
+---------------------+------+
| 2008-08-09 11:45:45 | 31 |
+---------------------+------+
三、MySQL ⽇期时间计算函数
1. MySQL 为⽇期增加⼀个时间间隔：date_add()
set @dt = now();
select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);
select date_add(@dt, interval -1 day); -- sub 1 day
MySQL adddate(), addtime()函数，可以⽤ date_add() 来替代。下⾯是 date_add() 实现 addtime() 功能⽰例：
mysql> set @dt = '2008-08-09 12:12:33';
mysql>
mysql> select date_add(@dt, interval '01:15:30' hour_second);
+------------------------------------------------+
| date_add(@dt, interval '01:15:30' hour_second) |
+------------------------------------------------+
| 2008-08-09 13:28:03 |
+------------------------------------------------+
mysql> select date_add(@dt, interval '1 01:15:30' day_second);
+-------------------------------------------------+
| date_add(@dt, interval '1 01:15:30' day_second) |
+-------------------------------------------------+
| 2008-08-10 13:28:03 |