2023-06-06 03:25:47
•
1179
mysql，字段时间戳 查询今天、昨天、本周等等数据
知识点：
from_unixtime(time_stamp) -> 将时间戳转换为日期
unix_timestamp(date) -> 将指定的日期或者日期字符串转换为时间戳注：数据表中需要一个存储日期时间的字段，本文中create_time存储的数据均为时间戳，times和create_time都是字段，testsort和table_name是数据库表
mysql 字段时间戳 查询今天的数据
SELECT * FROM `testsort` WHERE DATE_FORMAT(FROM_UNIXTIME(times),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d');
查询今天的数据
SELECT * FROM `testsort` WHERE TO_DAYS(FROM_UNIXTIME(times)) = TO_DAYS(NOW())
查询昨天的数据
SELECT * FROM `testsort` WHERE TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(times)) = 1
查询近7天的数据（包含今天）
SELECT * FROM table_name WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= FROM_UNIXTIME(times)
查询本周的数据
SELECT * FROM table_name WHERE YEARWEEK(FROM_UNIXTIME(times,'%Y-%m-%d')) = YEARWEEK(NOW());
查询上周的数据
SELECT * FROM table_name WHERE YEARWEEK(FROM_UNIXTIME(create_time,'%Y-%m-%d')) = YEARWEEK(NOW())-1;
查询近30天的数据（包含今天）
SELECT * FROM table_name where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= FROM_UNIXTIME(create_time)
查询本月的数据
SELECT * FROM table_name WHERE FROM_UNIXTIME(create_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
查询上月的数据
SELECT * FROM table_name WHERE QUARTER(FROM_UNIXTIME(create_time))=QUARTER(NOW()) AND YEAR(FROM_UNIXTIME(create_time))=YEAR(NOW())
查询本季度的数据
SELECT * FROM table_name WHERE QUARTER(FROM_UNIXTIME(create_time))=QUARTER(NOW()) AND YEAR(FROM_UNIXTIME(create_time))=YEAR(NOW())
查询上季度的数据
SELECT * FROM table_name WHERE QUARTER(FROM_UNIXTIME(create_time)) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) AND YEAR(FROM_UNIXTIME(create_time))=YEAR(NOW())
查询今年的数据
SELECT * FROM table_name WHERE YEAR(FROM_UNIXTIME(create_time))=YEAR(NOW())
查询去年的数据
SELECT * FROM table_name WHERE YEAR(FROM_UNIXTIME(create_time)) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))
php版本：
if($cat_id==1){//今天
$start = strtotime(date('Y-m-d', time())); //当前时间 当天0时0分0秒
$end = $start + 86400; //,获取明天，一天是86400(时间戳)
}elseif($cat_id==2){//昨天
$start = strtotime(date('Y-m-d', time()))- 86400; //获取昨天，一天是86400(时间戳)
$end = strtotime(date('Y-m-d', time()));
}elseif($cat_id==3){//前天
$start = strtotime(date('Y-m-d', time()))- 86400*2; //获取前天，一天是86400(时间戳)
$end = $start+86400 ;
}elseif($cat_id==4){//本周
$start = strtotime(date('Y-m-d', time()).'+1 day -'.date('w', time()).' day');
$end = strtotime(date('Y-m-d', time()).'+1 week -'.date('w', time()).' day'.'+1 day') ;
}elseif($cat_id==5){//本月
$start=strtotime(date('Y-m-01 00:00:00'));
$end = strtotime(date('Y-m-d H:i:s'));
}else{//本季度
$month=date('m');
if($month==1 || $month==2 ||$month==3){
$start=strtotime(date('Y-01-01 00:00:00'));
$end=strtotime(date("Y-03-31 23:59:59"));
}elseif($month==4 || $month==5 ||$month==6){
$start=strtotime(date('Y-04-01 00:00:00'));
$end=strtotime(date("Y-06-30 23:59:59"));
}elseif($month==7 || $month==8 ||$month==9){
$start=strtotime(date('Y-07-01 00:00:00'));
$end=strtotime(date("Y-09-30 23:59:59"));
}else{
$start=strtotime(date('Y-10-01 00:00:00'));
$end=strtotime(date("Y-12-31 23:59:59"));
}
}
select * from table where ctime between $start and $end;
参考网站：https://blog.csdn.net/qq_43428376/article/details/109514186
https://blog.csdn.net/sinat_37239798/article/details/115161583
数据库字段为日期格式的msyql，请参考：https://blog.51cto.com/u_16099188/6362093