开发者

mysql如何获取时间整点

开发者 https://www.devze.com 2023-02-08 08:58 出处:网络 作者: 清欢
目录mysql获取时间整点1.获取当天整点时间2.当前时间往前推的时间点总结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
    

    结果:

    mysql如何获取时间整点

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

    SELECT *
            FROM
                (
                SELECT DATE_FORMAT((NOW() - INTERVAL javascript0 HOUR),'%Y-%m-%d-%H') AS hh UNION
                SELECT DATE_FORMAT((NOW() php- 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
    

    结果:

    mysql如何获取时间整点

    示例:

    SELECT SUBSTRING(hourtable.hh,12)AS TIME, COALESCE(manytable.ss,0) ss, COALESCE(manytable.ys,0) ys,COALESCE(manytable.ddbs,0) ddbs
            FROM
                (
                SphpELECT 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_FO编程RMAT((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') 开发者_NoSQLAS 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
    

    效果:

    mysql如何获取时间整点

    总结

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

    0

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    关注公众号