mysql中将时间的分钟位除6取证再×6

星梦客

于 2024-07-01 19:27:36 发布

阅读量235

点赞数 1

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/weixin_44928129/article/details/140108488

版权

Mysql 专栏收录该内容

5 篇文章

订阅专栏

SELECT t1.case_id, REPLACE(REPLACE(REPLACE(t1.data_time,'-',''), ':', ''), ' ', '') AS radra_data_time,REPLACE(REPLACE(REPLACE(CONCAT_WS(':', DATE_FORMAT(t1.data_time, '%Y-%m-%d %H'),LPAD(FLOOR(MINUTE(t1.data_time) / 6) * 6, 2, '00'),"00"),'-',''), ':', ''), ' ', '') as light_data_time
FROM `tsw_case_timelist` t1
LEFT JOIN `light_case_timelist` t2 
    ON t1.case_id = t2.case_id AND CONCAT_WS(':', DATE_FORMAT(t1.data_time, '%Y-%m-%d %H'),LPAD(FLOOR(MINUTE(t1.data_time) / 6) * 6, 2, '00'),"00") = t2.data_time
WHERE t2.case_id IS NULL;