Mysql常用的时间格式转换及加减计算方法

#当前时间精确到微秒

SELECT NOW(6) AS '当前时间精确到微秒';

#时间格式转换

SELECT 

UNIX_TIMESTAMP() AS '当前时间戳'

,UNIX_TIMESTAMP('2018-1-1') AS '转换成时间戳'

,FROM_UNIXTIME(1523439700) AS '时间戳转datetime'

,date_format(NOW(),'%Y-%m-%d %H:%i:%s') AS '时间格式化';

 

#计算时间差

select timestampdiff(second,'2018-4-11 00:00:00','2018-4-15 12:00:00') AS '时间差';

 

#时间加减法

SELECT 

DATE(DATE_ADD(NOW(),INTERVAL -1 DAY)) AS '昨天'

,DATE(NOW()) AS '今天'

,DATE(DATE_ADD(NOW(),INTERVAL 1 DAY)) AS '明天'

,DATE(concat(date_format(now(),'%Y-%m-'),'01')) AS '本月第一天'

,LAST_DAY(now()) AS '本月最后一天'

,date_add(date(NOW()),INTERVAL -weekday(NOW()) day) AS '本周第一天'

,date_add(date(NOW()),INTERVAL -weekday(NOW())+6 day) AS '本周最后一天';