与时间相关mysql查询

SELECT DATE_FORMAT(buytime,'%Y%m%d') buytime,username FROM orderlist AS custal1

SELECT username ,MIN(DATE_FORMAT(buytime,'%Y%m%d'))FROM orderlist GROUP BY username ,DATE_FORMAT(buytime,'%Y%m%d')

 

-- 查询上月最后一天数据

SELECT * 
FROM orderlist WHERE order_date = LAST_DAY(NOW() - INTERVAL 1 MONTH);

-- 查询前一月第一天

SELECT CONCAT(DATE_FORMAT(LAST_DAY(NOW() – INTERVAL 1 MONTH),'%Y-%m-'),'01');

-- 今天

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

-- 昨天

SELECT * FROM orders WHERE TO_DAYS(NOW()) - TO_DAYS(DATE_FORMAT(order_date, '%Y%m%d')) = 1;

SELECT * FROM orders WHERE DATE_FORMAT(order_date, '%Y%m%d') = DATE_SUB(CURDATE(),INTERVAL 1 DAY);

-- 近7天

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(时间字段名);

SELECT * FROM 
orders WHERE DATE_SUB(CURDATE(), INTERVAL 10 DAY) <= DATE(order_date);

-- 近30天

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(时间字段名);

-- 本月

SELECT * FROM orders WHERE DATE_FORMAT( order_date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );

-- 查询本月过生日的学生
SELECT *
FROM student WHERE MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))=MONTH(s_birth);

-- 50、查询下月过生日的学生
SELECT st.*
FROM student st WHERE MONTH(st.s_birth)= MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1;

-- 上一月

SELECT * FROM 表名 WHERE PERIOD_DIFF( DATE_FORMAT( NOW( ) , '%Y%m' ) , DATE_FORMAT( 时间字段名, '%Y%m' ) ) =1;

-- 上月最后一天
SELECT *
FROM alibaba WHERE DATE_FORMAT(sdate,'%Y%m%d')=LAST_DAY(DATE_FORMAT(NOW(),'%Y%m%d') - INTERVAL 2 MONTH);

SELECT *
FROM alibaba WHERE DATE_FORMAT(sdate,'%Y%m%d')=LAST_DAY(NOW() - INTERVAL 4 MONTH);

-- shang月第一天
SELECT *
FROM alibaba WHERE DATE_FORMAT(sdate,'%Y%m')=CONCAT(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 2 MONTH),'%Y%m'),'01'); 

SELECT CONCAT(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH),'%Y-%m-'),'01');  

-- 查询当前今天的数据 

1. SELECT *
FROM orders WHERE TO_DAYS(order_date)=TO_DAYS(NOW());

2. SELECT NAME,submittime FROM enterprise WHERE YEARWEEK(DATE_FORMAT(submittime,'%Y-%m-%d')) =DATE_FORMAT(NOW(),'%Y-%m-%d');

3. SELECT * 
FROM orders WHERE DATE_FORMAT(order_date,'%Y%m%d')=DATE_FORMAT(NOW(),'%Y%m%d');

SELECT * 
FROM orders WHERE DAYOFYEAR(order_date)=DAYOFYEAR(NOW());

 

-- 查询当前这周的数据

SELECT NAME,submittime FROM enterprise WHERE YEARWEEK(DATE_FORMAT(submittime,'%Y-%m-%d')) = YEARWEEK(NOW());

SELECT * FROM orders WHERE YEARWEEK(DATE_FORMAT(order_date,'%Y%m%d')) = YEARWEEK(NOW());

SELECT *
FROM orders WHERE YEARWEEK(order_date)=YEARWEEK(NOW());

SELECT *
FROM orders WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(DATE_FORMAT(order_date,'%Y%m%d'));

 -- 47、查询本周过生日的学生
  SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth);
 
 -- 48、查询下周过生日的学生
  SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =WEEK(s_birth);

-- 查询上周的数据

SELECT NAME,submittime FROM enterprise WHERE YEARWEEK(DATE_FORMAT(submittime,'%Y-%m-%d')) = YEARWEEK(NOW())-1;
//国外一周是从周日到周六来算的 
SELECT * FROM ordersrecord WHERE YEARWEEK(ordertime,1) =YEARWEEK(DATE_SUB(CURDATE(),INTERVAL 7 DAY),1)

SELECT *
FROM orders WHERE WEEK(NOW())-WEEK(order_date) <=1

查询当前月份的数据

SELECT NAME,submittime FROM enterprise WHERE DATE_FORMAT(submittime,'%Y-%m')=DATE_FORMAT(NOW(),'%Y-%m')

SELECT *
FROM orders WHERE MONTH(NOW())=MONTH(order_date)

SELECT *
FROM orders WHERE DATE_FORMAT(NOW(),'%Y%m')=DATE_FORMAT(order_date,'%Y%m')

查询距离当前现在6个月的数据

SELECT NAME,submittime FROM enterprise WHERE submittime BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW();

查询上个月的数据

SELECT NAME,submittime FROM enterprise WHERE DATE_FORMAT(submittime,'%Y-%m')=DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m')

 

SELECT * FROM `user` WHERE DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');

SELECT * FROM USER WHERE WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(NOW())

SELECT * 
FROM USER 
WHERE MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(NOW())

SELECT * 
FROM [USER] 
WHERE YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(NOW())
AND MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(NOW())

SELECT * 
FROM [USER] 
WHERE pudate BETWEEN 上月最后一天
AND 下月第一天
WHERE DATE(regdate) = CURDATE();

SELECT * FROM test WHERE YEAR(regdate)=YEAR(NOW()) AND MONTH(regdate)=MONTH(NOW()) AND DAY(regdate)=DAY(NOW());

SELECT DATE( c_instime ) ,CURDATE( )
FROM `t_score`
WHERE 1
LIMIT 0 , 30

#查询本季度数据

SELECT * FROM `ht_invoice_information` WHERE QUARTER(create_date)=QUARTER(NOW());

#查询上季度数据

SELECT * FROM `ht_invoice_information` WHERE QUARTER(create_date)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));

#查询本年数据

SELECT * FROM `ht_invoice_information` WHERE YEAR(create_date)=YEAR(NOW());

#查询上年数据

SELECT * FROM `ht_invoice_information` WHERE YEAR(create_date)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));

#更改同一字段不同的值

UPDATE  ordernum
SET product =
CASE WHEN product='A' THEN 'B' ELSE 'A' END;

#查询第n高薪水