mysql如何獲取時間整點
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) 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
效果:
總結
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。
推薦閱讀:
- mysql獲取指定時間段中所有日期或月份的語句(不設存儲過程,不加表)
- MYSQL SQL查詢近7天一個月的數據的操作方法
- MySql日期查詢數據的實現
- MySQL日期及時間字段的查詢
- sql語句中日期相減的操作實例代碼