mysql如何获取时间整点

目录

• mysql获取时间整点
• 1.获取当天整点时间
• 2.当前时间往前推的时间点
• 总结

mysql获取时间整点

1.获取当天整点时间

SELECT *FROM(						SELECT DATE_FORMAT(CURDATE(),"%Y-%m-%d %H") AS hh UNION						SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 2 HOUR),"%Y-%m-%d %H") AS hh UNION    			SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 4 HOUR),"%Y-%m-%d %H") AS hh UNION						SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 6 HOUR),"%Y-%m-%d %H") AS hh UNION						SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 8 HOUR),"%Y-%m-%d %H") AS hh UNION						SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 10 HOUR),"%Y-%m-%d %H") AS hh UNION						SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 12 HOUR),"%Y-%m-%d %H") AS hh UNION						SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 14 HOUR),"%Y-%m-%d %H") AS hh UNION						SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 16 HOUR),"%Y-%m-%d %H") AS hh UNION						SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 18 HOUR),"%Y-%m-%d %H") AS hh UNION						SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 20 HOUR),"%Y-%m-%d %H") AS hh UNION						SELECT 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 UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 2 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 4 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 6 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 8 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 10 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 12 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 14 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 16 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 18 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT 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 UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 2 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 4 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 6 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 8 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 10 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 12 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 14 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 16 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 18 HOUR),"%Y-%m-%d-%H") AS hh UNION    SELECT DATE_FORMAT((NOW() - INTERVAL 20 HOUR),"%Y-%m-%d-%H") AS hh) hourtable    LEFT JOIN    ( SELECT    DATE_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 pid    FROM    p_park_record o    WHERE    DATE_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 ON    hourtable.hh = manytable.xh<if test="parkId != null and parkId != """>    AND manytable.pid = #{parkId}</if>    ORDER BY hourtable.hh

效果：

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。