mysql查询时间区间的数据

最新推荐文章于 2024-07-22 03:11:16 发布

苦痛自渡~冷暖自知

最新推荐文章于 2024-07-22 03:11:16 发布

阅读量477

点赞数

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

本文链接：https://blog.csdn.net/qq_38384994/article/details/116305360

版权

sql 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

数据库，sql

6 篇文章 0 订阅

订阅专栏

日期区间的截取

--上周
SELECT
	CURDATE() NOW,
	ADDDATE(
		ADDDATE(
			DATE_SUB(
				CURDATE(),
				INTERVAL WEEKDAY(CURDATE()) + 1 DAY
			) ,- 6
		),
		0
	) startdate,
	ADDDATE(
		DATE_SUB(
			CURDATE(),
			INTERVAL WEEKDAY(CURDATE()) + 1 DAY
		),
		0
	) endDate

-- 上月
SELECT
	CURDATE() NOW,
	DATE_ADD(
		DATE_ADD(
			CURDATE(),
			INTERVAL - DAY (CURDATE()) + 1 DAY
		),
		INTERVAL - 1 MONTH
	) startdate,
	DATE_ADD(
		DATE_ADD(
			CURDATE(),
			INTERVAL - DAY (CURDATE()) DAY
		),
		INTERVAL 0 MONTH
	) endDate

-- 前四个月
SELECT
	CURDATE() NOW,
	ADDDATE(
		ADDDATE(
			CURDATE(),
			INTERVAL - DAY (CURDATE()) + 1 DAY
		),
		INTERVAL - 4 MONTH
	) startdate,
	DATE_ADD(
		DATE_ADD(
			CURDATE(),
			INTERVAL - DAY (CURDATE()) DAY
		),
		INTERVAL 0 MONTH
	) endDate

--上周四到本周三
SELECT
	ADDDATE(
		DATE_SUB(
			CURDATE(),
			INTERVAL (

				IF (
					WEEKDAY(CURDATE()) IN (3, 4, 5, 6),
					WEEKDAY(CURDATE()),
					WEEKDAY(CURDATE()) + 7
				)
			) + 1 DAY
		) ,- 3
	) startdate,
	ADDDATE(
		DATE_SUB(
			CURDATE(),
			INTERVAL (

				IF (
					WEEKDAY(CURDATE()) IN (3, 4, 5, 6),
					WEEKDAY(CURDATE()),
					WEEKDAY(CURDATE()) + 7
				)
			) + 1 DAY
		),
		3
	) endDate

--后一个小时
SELECT SYSDATE(), ADDDATE(SYSDATE(),INTERVAL 1 HOUR) endDate 
--前一个小时
SELECT SYSDATE(), SUBDATE(SYSDATE(),INTERVAL 1 HOUR) endDate

--本周数据
SELECT
	PLAN_ID,
	BUILDTIME
FROM
	TB_INSPECT_PLAN
WHERE
	YEARWEEK(
		date_format(BUILDTIME, '%Y-%m-%d')
	) = YEARWEEK(now())

--上周数据
SELECT
	PLAN_ID,
	BUILDTIME
FROM
	TB_INSPECT_PLAN
WHERE
	YEARWEEK(
		date_format(BUILDTIME, '%Y-%m-%d')
	) = YEARWEEK(now()) - 1

--当前月份的数据
SELECT
	PLAN_ID,
	BUILDTIME
FROM
	TB_INSPECT_PLAN
WHERE
	date_format(BUILDTIME, '%Y-%m') = date_format(now(), '%Y-%m')

--查询距当前6个月的数据
SELECT
	PLAN_ID,
	BUILDTIME
FROM
	TB_INSPECT_PLAN
WHERE
	BUILDTIME BETWEEN date_sub(now(), INTERVAL 6 MONTH)
AND now()

--查询上个月数据
SELECT
	PLAN_ID,
	BUILDTIME
FROM
	TB_INSPECT_PLAN
WHERE
	date_format(BUILDTIME, '%Y-%m') = date_format(
		DATE_SUB(CURDATE(), INTERVAL 1 MONTH),
		'%Y-%m'
	)