mysql时间相关处理

时间格式处理

mysql中时间格式处理

• 获取（ %Y-%m-%d %H:%i:%s） 格式当前时间

SELECT NOW()
select current_timestamp
select current_timestamp()

• 获取（ %Y-%m-%d） 格式当前时间

SELECT CURRENT_DATE()

• 获取当时时间戳（10位）

select UNIX_TIMESTAMP(now())  
SELECT UNIX_TIMESTAMP()

# 注意以下三种形式，结果相同
SELECT UNIX_TIMESTAMP('20120608');
SELECT UNIX_TIMESTAMP('2012-6-8');
SELECT UNIX_TIMESTAMP('2012-06-08');

• 时间戳转为时间格式

SELECT FROM_UNIXTIME(1528681020)  ---> 处理10位时间戳 结果：2018-06-11 09:37:00
SELECT FROM_UNIXTIME(unix_timestamp(),'%Y-%m-%d %H:%i:%s')  ---> 处理10位时间戳，指定格式 结果：2021-08-19 08:50:08
SELECT FROM_UNIXTIME(unix_timestamp(),'%Y-%m-%d') ---> 处理10位时间戳，指定格式 结果：2021-08-19
SELECT FROM_UNIXTIME(1528681020000/1000,'%Y-%m-%d %H:%i:%s')  ---> 处理13位时间戳，指定格式 结果：2018-06-11 09:37:00

• DATE_FORMAT

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')  结果：2021-08-19
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')  结果：2021-08-19 09:48:17
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %T')  结果：2021-08-19 09:48:25

• STR_TO_DATE

select STR_TO_DATE('2012-10-01 8:39:50', '%Y-%m-%d %T')  结果：2012-10-01 08:39:50     '%T'= 时间, 24-小时 (hh:mm:ss)
select STR_TO_DATE('2012-10-01 8:39:50', '%Y-%m-%d')     结果：2012-10-01
select STR_TO_DATE('2012-10-01 8:39:50', '%Y-%m-%d %H')  结果：2012-10-01 08:00:00
select STR_TO_DATE('2012-10-01 8:39:50', '%Y-%m-%d %H:%i')  结果：2012-10-01 08:39:00
select STR_TO_DATE('2012-10-01 8:39:50', '%Y-%m-%d %H:%i:%s')  结果：2012-10-01 08:39:50
select STR_TO_DATE('2012-10-01', '%Y-%m-%d %H:%i:%s')  结果： 2012-10-01 00:00:00

mybatis中使用注意

或者转义语句：

<![CDATA[ sql语句 ]]>   

与当前时间比较

• 符号直接比

SELECT '2015-08-05 00:00:00' > '2012-10-01 8:39:50' 结果：1

# 实际使用
select * from table where add_time >'2015-08-05 00:00:00'

• 内置函数转换，再比(影响索引)

 WHERE
 DATE_FORMAT(start_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d') 
 AND 
 DATE_FORMAT(end_time, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-%m-%d')
 
 WHERE
 DATE_FORMAT(add_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d') 
 AND 
 DATE_FORMAT(add_time, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-%m-%d')
 
WHERE
UNIX_TIMESTAMP(add_time) >= UNIX_TIMESTAMP ('2018-02-18')
AND 
UNIX_TIMESTAMP(add_time) <= UNIX_TIMESTAMP ('2018-02-20')

• between and语句

where add_time between '2018-07-01' and '2018-07-04' 

结果是1号到3号的数据，这是因为时间范围显示的实际上只是‘2018-07-01 00:00:00’到’2018-07-04 00:00:00’之间的数据，而’2018-07-04’的数据就无法显示出来，between and对边界还需要进行额外的处理。

日期计算相关

计算日期差值也可以间接做时间比较

计算差值

• TIMESTAMPDIFF函数

语法：TIMESTAMPDIFF(unit,begin,end)，计算的是end-begin

unit：比较的类型。可选参数：MICROSECOND 微秒、SECOND 秒、MINUTE 分钟、HOUR 小时、DAY 天、WEEK 周、MONTH 月份、QUARTER 季度 、YEAR 年份

begin：DATE或DATETIME表达式

end：DATE或DATETIME表达式

PS：begin和end类型可以不相同，例如begin为DATE、end为DATETIME。

SELECT TIMESTAMPDIFF(DAY,'2012-10-01','2013-01-13')		# 结果为104
SELECT TIMESTAMPDIFF(DAY,'2012-10-01','2013-01-13')>=0  # 结果为1

SELECT TIMESTAMPDIFF(MONTH,'2012-10-01','2013-01-13')   # 结果为3
SELECT TIMESTAMPDIFF(MONTH,'2012-10-01','2013-01-13') >=0  # 结果为1
SELECT TIMESTAMPDIFF(MONTH,'2013-10-01','2013-01-13') >=0  # 结果为0

WHERE TIMESTAMPDIFF(MONTH,'数据库中当条信息的日期',STR_TO_DATE('前台传入放入日期', '%Y-%m-%d %H:%i:%s'))

• DATEDIFF

语法：DATEDIFF(date1,date2) 计算的是date1-date2

SELECT DATEDIFF('2018-07-01','2018-07-04')  # 结果为 -3
SELECT DATEDIFF('2018-07-31','2018-07-04')  # 结果为 27
SELECT DATEDIFF('2012-10-02 23:39:50','2012-10-01 8:39:50');  # 结果为1

加减日期

• DATE_SUB、DATE_ADD

DATE_SUB(date,INTERVAL expr type)、DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type可以为：MICROSECOND 、SECOND 、MINUTE 、HOUR 、DAY 、WEEK 、MONTH 、QUARTER 、YEAR 、SECOND_MICROSECOND 、MINUTE_MICROSECOND、、MINUTE_SECOND 、HOUR_MICROSECOND 、HOUR_SECOND 、HOUR_MINUTE 、DAY_MICROSECOND 、DAY_SECOND 、DAY_MINUTE 、DAY_HOUR 、YEAR_MONTH

SELECT DATE_FORMAT(DATE_SUB('2012-10-01 8:39:50',INTERVAL 1 DAY),'%Y-%m-%d');  结果：2012-09-30
SELECT DATE_FORMAT(DATE_ADD('2012-10-01 8:39:50',INTERVAL 1 DAY),'%Y-%m-%d');  结果：2012-10-02
SELECT DATE_FORMAT(DATE_ADD('2012-10-01 8:39:50',INTERVAL 8 SECOND),'%Y-%m-%d');  结果：2012-10-01 08:39:58
SELECT DATE_FORMAT(DATE_ADD('2012-10-01 8:39:50',INTERVAL 20 MONTH ),'%Y-%m-%d');  结果：2014-06-01