mysql 时间戳转换今天、昨天、7天、本月、上月

最新推荐文章于 2023-05-08 20:03:27 发布

平头哥糊口养家

最新推荐文章于 2023-05-08 20:03:27 发布

阅读量699

点赞数

分类专栏： mysql学习

本文链接：https://blog.csdn.net/oYuLinZuo/article/details/110442359

版权

mysql学习专栏收录该内容

13 篇文章 0 订阅

订阅专栏

下面的时间范围查询都是前闭后开，如 $time_start <= time < $time_end

if $day_index = 1 then #今天数据

set $time_start = UNIX_TIMESTAMP(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 0 DAY),INTERVAL 0 HOUR));
set $time_end = UNIX_TIMESTAMP(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 0 DAY),INTERVAL 24 HOUR));

elseif $day_index = 2 then #昨天数据

set $time_start = UNIX_TIMESTAMP(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 1 DAY),INTERVAL 0 HOUR));
set $time_end = UNIX_TIMESTAMP(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 1 DAY),INTERVAL 24 HOUR));

elseif $day_index = 3 then #7天数据

set $time_start = UNIX_TIMESTAMP(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 6 DAY),INTERVAL 0 HOUR)); #6天前
set $time_end = UNIX_TIMESTAMP(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 0 DAY),INTERVAL 24 HOUR)); #今天结束

elseif $day_index = 4 then #本月数据

set $time_start = UNIX_TIMESTAMP(DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')); #本月开始
set $time_end = UNIX_TIMESTAMP(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL 0 DAY),INTERVAL 24 HOUR)); #今天结束

elseif $day_index = 5 then #上月数据

set $time_start = UNIX_TIMESTAMP(DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')); #上月开始
set $time_end = UNIX_TIMESTAMP(DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')); #本月开始

end if;