常用时间函数
SELECT NOW();
SELECT CURRENT_DATE();
SELECT CURRENT_TIMESTAMP()
SELECT DATEDIFF(‘2012-11-28’,’2012-11-25’)
SELECT DATE_FORMAT(NOW(),’%m/%d/%Y %H:%i:%s’)
当前时间1分钟前时间
SELECT DATE_SUB(NOW(),INTERVAL 1 MINUTE)
SELECT DATE_SUB(NOW(),INTERVAL 1 HOUR)
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)
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 |
+————————————————-+
MySQL 为日期减去一个时间间隔：date_sub()
mysql> select date_sub(‘1998-01-01 00:00:00’, interval ‘1 1:1:1’ day_second);
+—————————————————————-+
| date_sub(‘1998-01-01 00:00:00’, interval ‘1 1:1:1’ day_second) |
+—————————————————————-+
| 1997-12-30 22:58:59 |
+—————————————————————-+
MySQL date_sub() 日期时间函数 和 date_add() 用法一致，不再赘述。
MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)
MySQL datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。
select datediff(‘2008-08-08’, ‘2008-08-01’); – 7
select datediff(‘2008-08-01’, ‘2008-08-08’); – -7
MySQL timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。
select timediff(‘2008-08-08 08:08:08’, ‘2008-08-08 00:00:00’); – 08:08:08
select timediff(‘08:08:08’, ‘00:00:00’); – 08:08:08
注意：timediff(time1,time2) 函数的两个参数类型必须相同。
MySQL 时间戳（timestamp）转换、增、减函数：
timestamp(date) – date to timestamp
timestamp(dt,time) – dt + time
timestampadd(unit,interval,datetime_expr) –
timestampdiff(unit,datetime_expr1,datetime_expr2) –
请看示例部分：
复制代码
select timestamp(‘2008-08-08’); – 2008-08-08 00:00:00
select timestamp(‘2008-08-08 08:00:00’, ‘01:01:01’); – 2008-08-08 09:01:01
select timestamp(‘2008-08-08 08:00:00’, ‘10 01:01:01’); – 2008-08-18 09:01:01
select timestampadd(day, 1, ‘2008-08-08 08:00:00’); – 2008-08-09 08:00:00
select date_add(‘2008-08-08 08:00:00’, interval 1 day); – 2008-08-09 08:00:00
MySQL timestampadd() 函数类似于 date_add()。
select timestampdiff(year,’2002-05-01’,’2001-01-01’); – -1
select timestampdiff(day ,’2002-05-01’,’2001-01-01’); – -485
select timestampdiff(hour,’2008-08-08 12:00:00’,’2008-08-08 00:00:00’); – -12
select datediff(‘2008-08-08 12:00:00’, ‘2008-08-01 00:00:00’); – 7
复制代码
MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数。
MySQL 时区（timezone）转换函数
convert_tz(dt,from_tz,to_tz)
select convert_tz(‘2008-08-08 12:00:00’, ‘+08:00’, ‘+00:00’); – 2008-08-08 04:00:00
时区转换也可以通过 date_add, date_sub, timestampadd 来实现。
select date_add(‘2008-08-08 12:00:00’, interval -8 hour); – 2008-08-08 04:00:00
select date_sub(‘2008-08-08 12:00:00’, interval 8 hour); – 2008-08-08 04:00:00
select timestampadd(hour, -8, ‘2008-08-08 12:00:00’); – 2008-08-08 04:00:00
原文地址：https://www.cnblogs.com/ggjucheng/p/3352280.html
