SQL操作日期语句

目录

文章目录

SQL操作日期、时间语句

SQL操作日期

查询今天昨天

查询今天
SELECT * FROM 表名 WHERE TO_DAYS( 时间字段名 ) = TO_DAYS(NOW());

SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=0
查询昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW() ) - TO_DAYS( 时间字段名 ) = 1

SELECT * FROM 表名 WHERE DATEDIFF( 字段 , NOW() )=-1

查询未来过去n天

查询未来第n天的数据
SELECT * FROM 表名 WHERE DATEDIFF( 字段 , NOW() ) = n
查询过去第n天的数据
SELECT * FROM 表名 WHERE DATEDIFF( 字段 , NOW() ) = -n
查询未来n天内（不包含第n天）所有数据（包含今天（查询当天）数据）
SELECT * FROM 表名 WHERE DATEDIFF( 字段 , NOW() ) < n AND DATEDIFF( 字段 , NOW() ) >= 0
查询未来n天内（不包含第n天）所有数据（不包含今天（查询当天）数据）
SELECT * FROM 表名 WHERE DATEDIFF( 字段 , NOW() ) < n AND DATEDIFF( 字段 , NOW() ) > 0
查询未来n天内（包含第n天）所有数据（包含今天（查询当天）数据）
SELECT * FROM 表名 WHERE DATEDIFF( 字段 , NOW() ) <= n AND DATEDIFF( 字段 , NOW() ) >= 0
查询未来n天内（包含第n天）所有数据（不包含今天（查询当天）数据）
SELECT * FROM 表名 WHERE DATEDIFF( 字段 , NOW() ) <= n AND DATEDIFF( 字段 , NOW() ) > 0
查询过去n天内（不包含第n天）所有数据（包含今天（查询当天）数据）
SELECT * FROM 表名 WHERE DATEDIFF( 字段 , NOW() ) <= 0 AND DATEDIFF( 字段 , NOW() ) > -n
查询过去n天内（不包含第n天）所有数据（不包含今天（查询当天）数据）
SELECT * FROM 表名 WHERE DATEDIFF( 字段 , NOW() ) < 0 AND DATEDIFF( 字段 , NOW() ) > -n
查询过去n天内（包含第n天）所有数据（包含今天（查询当天）数据）
SELECT * FROM 表名 WHERE DATEDIFF( 字段 , NOW() ) <= 0 AND DATEDIFF( 字段 , NOW() ) >= -n
查询未来n天内（包含第n天）所有数据（不包含今天（查询当天）数据）
SELECT * FROM 表名 WHERE DATEDIFF( 字段 , NOW() ) < 0 AND DATEDIFF( 字段 , NOW() ) >= -n

查询近n天/年数据（今天（查询当天）前n天后n天）

查询近n天内所有数据
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL n DAY) <= date( 时间字段名 )
查询近10年内所有数据量（根据年份划分）（前10年后10年）
SELECT YEAR ( 时间字段名 ) AS m, count( id ) AS number, 
-- 保留两位小数
cast( sum( 金额字段名 ) AS DECIMAL ( 10, 2 )) AS mon 
FROM 表名 WHERE YEAR ( 时间字段名 )> YEAR ( now() ) - 10 GROUP BY YEAR ( 时间字段名 )
查询近12个月内所有数据（根据月份）（前12个月后12个月）
SELECT DATE_FORMAT( 时间字段名, '%Y-%m' ) AS cm, COUNT( id ) AS cn, cast( sum ( 金额字段名 ) AS DECIMAL ( 10, 2 ) ) AS mon FROM 表名 WHERE date_sub( curdate(), INTERVAL 12 MONTH ) <= date( 时间字段名 ) GROUP BY cm
注：近n天、月、年 这种则会查询 前n 后n

查询本周、本月、本年数据

查询本周数据
SELECT * FROM 表名 WHERE YEARWEEK(date_format( 时间字段名 ,'%Y-%m-%d')) = YEARWEEK(now());
查询本月数据
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名 ,'%Y-%m') = DATE_FORMAT(now(),'%Y-%m')

select * FROM 表名 WHERE DATE_FORMAT( 时间字段名 ,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')
查询今年数据
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名 , '%Y' ) = DATE_FORMAT( SYSDATE(), '%Y' )
查询今年每个月有多少条数据
SELECT year( 时间字段名 ) as y,month( 时间字段名 ) as m, count(*) as number FROM 表名 WHERE DATE_FORMAT( 时间字段名 ,'%Y') = DATE_FORMAT(SYSDATE(),'%Y') GROUP BY month( 时间字段名 )  （或者 GROUP BY m   ）
查询今年每个季度有多少条数据
SELECT QUARTER ( 时间字段名 ),count(*) FROM 表名 WHERE DATE_FORMAT( 时间字段名 ,'%Y') = DATE_FORMAT(SYSDATE(),'%Y')  GROUP BY  QUARTER ( 时间字段名 )

查询上周、上月、上年数据（都是精确到某一周、月、年）

查询上周数据
SELECT * FROM 表名 WHERE YEARWEEK(date_format( 时间字段名 ,'%Y-%m-%d')) = YEARWEEK(now())-1;
查询上月数据
SELECT * FROM 表名 where date_format( 时间字段名 ,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
查询上年数据
SELECT * FROM 表名 WHERE YEAR ( 时间字段名 )= YEAR ( date_sub( now(), INTERVAL 1 YEAR ))
查询上年今天的数据（去年今天）
select * from 表名 where DATE_FORMAT( 时间字段名 ,'%Y-%m-%d' )  = DATE_FORMAT( NOW(),'%Y-%m-%d' )
查询上年每个月都多少条数据
SELECT year( 时间字段名 ) as y,month( 时间字段名 ) as m, count(*) as number FROM 表名 WHERE YEAR ( 时间字段名 )= YEAR ( date_sub( now(), INTERVAL 1 YEAR )) GROUP BY month( 时间字段名 )     （或者 GROUP BY m   ）
查询上年每个季度都有多少天数据
SELECT QUARTER ( 时间字段名 ),count(*) FROM 表名 WHERE YEAR ( 时间字段名 )= YEAR ( date_sub( now(), INTERVAL 1 YEAR ))  GROUP BY  QUARTER ( 时间字段名 )

查询前n年、月、周、天记录（包含从查询日期到第n年内的）

查询前n年、月、周 都是 前 n 到现在查询的时间这个时间段中的数据的数量

查询过去10年的数据数量（包含今年）
SELECT 
-- 数据库数据中某年数据的数量
COUNT(*) num, 
-- 数据库数据中某年
YEAR( 时间字段名 ) AS sname, 
-- 当前时间的年份
YEAR(now()) AS nowYear,
-- 距离当前时间年份过去10的年份
YEAR(now()) - 10 AS lastYeaer
FROM 表名 
WHERE
 YEAR( 时间字段名 ) <= YEAR(now()) AND  
 YEAR( 时间字段名 ) > YEAR(now()) - 10 GROUP BY YEAR( 时间字段名 )
查询未来10年的数据数量（包含今年）
SELECT COUNT(*) num, 
-- 数据库数据中某年
YEAR( 时间字段名 ) AS sname, 
-- 当前时间的年份
YEAR(now()) AS nowYear,
-- 距离当前时间年份未来10的年份
YEAR(now()) + 10 AS lastYeaer
FROM 表名 
WHERE
 YEAR( 时间字段名 ) >= YEAR(now()) AND  
 YEAR( 时间字段名 ) < YEAR(now()) + 10 GROUP BY YEAR( 时间字段名 )

注：
一、
在  ‘查询过去10年的数据数量（包含今年）’  和  ‘查询未来10年的数据数量（包含今年）’ 中 包含与不包含几年 和包含与不包含过去/未来的最后一年  都在于
 YEAR(create_time) (>=) (<=) (<) (>) YEAR(now()) AND  
 YEAR(create_time)  (>=) (<=) (<) (>)  YEAR(now()) + 10 GROUP BY YEAR(create_time)
中 具体根据逻辑进行判断
二、
在两个sql中 where 后面第一个判断条件（   YEAR(create_time) (>=) (<=) (<) (>) YEAR(now())  ） 是为了不进行输出未来或者过去的年份
	比如   在查询过去10年的时候  如果不加这个判断  则会输出 未来10年的年份
查询6个月前的数据（包含当前月）
SELECT * FROM 表名 WHERE 时间字段名 BETWEEN date_sub(now(),interval 6 month) AND now();

注：
一、
包含当前月意思
 比如查询6个月前  （现在是 12 月 15日）
 则会查询  12 、 11 、 10 、 9 、 8、 7  月 
二、
假如当前时间是 12月15日 数据库中如果有 6月15日之后的数据也会查询出来，不包含6月15日
查询近7天数据
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date( 时间字段名 )
查询过去7天数据
SELECT * FROM 表名 where date( 时间字段名 ) <=  date( now() ) and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date( 时间字段名 )

月份  日后补充

查询季度

第一季度：1月－3月     第二季度：4月－6月       第三季度：7月－9月     第四季度：10月－12月
查询本季度有多少条记录
SELECT 
-- 查询年份和第几季度，使用 ‘-‘ 连接
CONCAT( YEAR ( create_time ), '-', QUARTER ( create_time )) now_quarter,
-- 查询本季度共有多少条数据
CONUT(*) AS d
 FROM 表名 
 WHERE
 DATE_FORMAT( 时间字段名, '%Y' ) = DATE_FORMAT( SYSDATE(), '%Y' ) AND  QUARTER ( 时间字段名 ) = QUARTER ( now())
 GROUP BY CONCAT( YEAR ( 时间字段名 ), QUARTER ( 时间字段名 )), date_format( 时间字段名, '%Y' ), QUARTER ( 时间字段名 )
上年每个季度共有多少条记录
SELECT QUARTER( 时间字段名 ) AS lquarter,,count(id) AS number, sum( 金钱字段名 ) AS lquantity FROM 表名
 WHERE
 YEAR ( 时间字段名 )= YEAR ( date_sub( now() , INTERVAL 1 YEAR )) AND create_time IS NOT NULL
 GROUP BY QUARTER ( 时间字段名 )
今年每个季度共有多少条记录
SELECT QUARTER( 时间字段名 ) AS lquarter,count(id), sum( 金钱字段名 ) AS lquantity
 FROM 表名 WHERE YEAR ( 时间字段名 )= YEAR (  now() )
 AND create_time IS NOT NULL  GROUP BY QUARTER ( 时间字段名 )
上个季度的数据
SELECT 
QUARTER(DATE_SUB(now(),interval 1 QUARTER)) AS lquarter,
COUNT(id) AS number, 
SUM(money) AS lmoney 
FROM 表名 WHERE QUARTER( 时间字段名 ) = QUARTER(DATE_SUB(now(),interval 1 QUARTER)) ;

====================== 分割线 很华丽 =======================

SQL操作时间语句

查询当前时间

SELECT 
-- 当前时间，精确到年月日时分秒
NOW() AS nowDate

-- 当前时间，精确到年月日时分秒
select SYSDATE()

查询前一小时时间

-- 一小时前，精确到年月日时分秒
SELECT DATE_SUB(NOW(),INTERVAL 1 HOUR) AS lastDate

查询前一小时内的数据

SELECT * FROM 表名 WHERE 时间字段名 BETWEEN DATE_SUB(NOW(),INTERVAL  1 HOUR) AND NOW()

SELECT * FROM 表名 WHERE 时间字段名 BETWEEN DATE_SUB(NOW(),INTERVAL 1 HOUR)  AND  SYSDATE()

查询当前时间之后的所有数据

SELECT * FROM 表名 WHERE 时间字段名 > NOW()

查询当前时间前一小时之后的所有数据

SELECT * FROM 表名 WHERE 时间字段名 > DATE_SUB(NOW(), INTERVAL 60 MINUTE)    

SELECT * FROM 表名 WHERE 时间字段名 > DATE_SUB(NOW(), INTERVAL 1 HOUR)

查询今天每个小时内数据总量

SELECT HOUR( 时间字段名 ) AS h, COUNT( * ) AS number 
FROM 表名 
WHERE
	DATE( 时间字段名 ) = DATE(NOW())
-- 根据小时进行分组
GROUP BY h  
-- 根据小时进行排序
ORDER BY h;

查询今天每半小时数据的总量

SELECT HOUR( 时间字段名 ) AS h ,FLOOR( MINUTE ( 时间字段名 ) / 30 ) AS m, COUNT(*) AS number 
FROM 表名 
WHERE DATE( 时间字段名 ) = DATE( NOW() ) 
-- 根据时分进行分组（一个小时有两个分钟区间  0-30   31-60）
GROUP BY h,m 
-- 根据小时进行排序
ORDER BY h;
-- FLOOR()函数，向下取整 例如：FLOOR(29 / 30) = 0   (29/30 = 0.9667)
-- 所以在分钟的时候 30分钟之前的为 0  30分钟之后的为 1 
-- 日后想起好的表达方式在继续优化

查询今天（精确到小时）之后（未来时间）的所有数据

SELECT * FROM 表名 WHERE 时间字段名 > SYSDATE()

SELECT * FROM 表名 WHERE 时间字段名 > DATE_ADD(NOW(),INTERVAL 0 HOUR)
版权声明：本文为weixin_50166145原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。