mysql获取时间整点

本文主要是介绍mysql获取时间整点，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1.获取当天整点时间

SELECT *
FROM
(SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d %H') AS hh UNIONSELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 2 HOUR),'%Y-%m-%d %H') AS hh UNIONSELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 4 HOUR),'%Y-%m-%d %H') AS hh UNIONSELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 6 HOUR),'%Y-%m-%d %H') AS hh UNIONSELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 8 HOUR),'%Y-%m-%d %H') AS hh UNIONSELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 10 HOUR),'%Y-%m-%d %H') AS hh UNIONSELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 12 HOUR),'%Y-%m-%d %H') AS hh UNIONSELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 14 HOUR),'%Y-%m-%d %H') AS hh UNIONSELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 16 HOUR),'%Y-%m-%d %H') AS hh UNIONSELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 18 HOUR),'%Y-%m-%d %H') AS hh UNIONSELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 20 HOUR),'%Y-%m-%d %H') AS hh UNIONSELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 22 HOUR),'%Y-%m-%d %H') AS hh 
) hourtable

结果：
在这里插入图片描述

2.当前时间往前推的时间点

SELECT *FROM(SELECT DATE_FORMAT((NOW() - INTERVAL 0 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 2 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 4 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 6 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 8 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 10 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 12 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 14 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 16 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 18 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 20 HOUR),'%Y-%m-%d-%H') AS hh) hourtable

结果：
在这里插入图片描述

示例：

SELECT SUBSTRING(hourtable.hh,12)AS TIME, COALESCE(manytable.ss,0) ss, COALESCE(manytable.ys,0) ys,COALESCE(manytable.ddbs,0) ddbsFROM(SELECT DATE_FORMAT((NOW() - INTERVAL 0 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 2 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 4 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 6 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 8 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 10 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 12 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 14 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 16 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 18 HOUR),'%Y-%m-%d-%H') AS hh UNIONSELECT DATE_FORMAT((NOW() - INTERVAL 20 HOUR),'%Y-%m-%d-%H') AS hh) hourtableLEFT JOIN( SELECTDATE_FORMAT(o.updated_at, '%Y-%m-%d-%H')  xh,COALESCE(SUM( o.amount_paid ) ,0) ss,COALESCE(SUM( o.amount_payable ),0) ys,COALESCE(COUNT( o.record_key ),0) ddbs,park_key as pidFROMp_park_record oWHEREDATE_FORMAT( o.updated_at, '%Y-%m-%d-%H' ) > DATE_FORMAT( ( NOW( ) - INTERVAL 20 HOUR ), '%Y-%m-%d-%H' )GROUP BY  DATE_FORMAT(o.updated_at, '%Y-%m-%d-%H'),pid ) manytable ONhourtable.hh = manytable.xh<if test="parkId != null and parkId != ''">AND manytable.pid = #{parkId}</if>ORDER BY hourtable.hh