mysql查询时间段

最新推荐文章于 2024-08-03 17:24:50 发布

江枫鱼眠

最新推荐文章于 2024-08-03 17:24:50 发布

阅读量977

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/YouthStrive/article/details/86611273

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

mysql查询时间段数据

查询今天数据

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE TO_DAYS(estimateInLoanTime)=TO_DAYS(now())

查询昨天数据

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
TO_DAYS(NOW( )) - TO_DAYS(estimateInLoanTime) = 1

查询几天后的数据

注:DATE_ADD(CURDATE(), INTERVAL 2 DAY) 当前时间的两天后

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
DATE_ADD(CURDATE(), INTERVAL 2 DAY) = date(estimateInLoanTime)

查询几天前的数据

注:DATE_SUB(CURDATE(), INTERVAL 1 DAY) 当前时间前一天数据

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
DATE_SUB('2018-05-09', INTERVAL 1 DAY) = date(estimateInLoanTime)

查询几天内的数据

注:estimateInLoanTime BETWEEN now() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) 查询7天内的数据

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
estimateInLoanTime BETWEEN now() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)

查询本月数据

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
DATE_FORMAT(estimateInLoanTime,'%Y%m') = DATE_FORMAT(CURDATE( ),'%Y%m')

查询上月数据

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
PERIOD_DIFF(date_format(now(),'%Y%m') ,date_format(estimateInLoanTime,'%Y%m')) =1

查询本季度数据

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
QUARTER(estimateInLoanTime)=QUARTER(now());

查询上季度

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
QUARTER(estimateInLoanTime)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

查询本年的

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
YEAR(estimateInLoanTime)=YEAR(NOW());

查询上年的

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
year(estimateInLoanTime)=year(date_sub(now(),interval 1 year));

查询当周的数据

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
YEARWEEK(date_format(estimateInLoanTime,'%Y-%m-%d')) = YEARWEEK(now());

查询上周的

SELECT 	
	*
FROM
	tbl_finance_paymentreport
WHERE 
YEARWEEK(date_format(estimateInLoanTime,'%Y-%m-%d')) = YEARWEEK(now())-1;

按24小时分组查询

 SELECT
            DATE_FORMAT(createTime,'%Y/%m/%d%H') hours,
            count(id) AS chatCount,
            SUM(messageCount) AS messageCount
        FROM
            tbl_basis_middle_customer_visitor v
        WHERE companyId = #{companyId}
        <![CDATA[
          AND DATE_FORMAT(createTime,'%Y-%m-%d') >= DATE_FORMAT(#{startTime},'%Y-%m-%d')
          AND DATE_FORMAT(createTime,'%Y-%m-%d') < DATE_FORMAT(#{endTime},'%Y-%m-%d')
        ]]>
        GROUP BY
            hours;