快捷導(dǎo)航

mysql如何獲取時(shí)間整點(diǎn)

更新時(shí)間：2023年02月07日 09:02:38 作者：清歡

這篇文章主要介紹了mysql如何獲取時(shí)間整點(diǎn)問(wèn)題，具有很好的的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

mysql獲取時(shí)間整點(diǎn)

1.獲取當(dāng)天整點(diǎn)時(shí)間

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

結(jié)果：

2.當(dāng)前時(shí)間往前推的時(shí)間點(diǎn)

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

結(jié)果：

示例：

SELECT SUBSTRING(hourtable.hh,12)AS TIME, COALESCE(manytable.ss,0) ss, COALESCE(manytable.ys,0) ys,COALESCE(manytable.ddbs,0) ddbs
        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
            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

效果：