1. Preface
This article introduces the commonly used date query methods for mysql. Hope it helps you.
2. Common date query methods
1. Query today's data
select * from Table name where to_days(Time field name) = to_days(now());
2. Query yesterday's data
select * from Table name where to_days(now( )) - to_days( Time field name) <= 1
3. Query data for the past 7 days
select * from Table name where date_sub(curdate(), interval 7 day) <= date(Time field name)
4. Query data for the past 30 days
SELECT * FROM Table name where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(Time field name)
5. Query the current monthly data
select * from Table name where date_format( Time field name, '%y%m' ) = date_format( curdate() , '%y%m')
6. Query the data of the previous month
select * from Table name where period_diff( date_format( now( ) , '%y%m' ) , date_format( Time field name, '%y%m' ) ) =1
7. Query this quarter's data
select * from Table name where quarter(create_date)=quarter(now());
8. Query the data from the previous quarter
select * from Table name where quarter(create_date)=quarter(date_sub(now(),interval 1 quarter));
9. Query this year's data
select * from Table name where year(create_date)=year(now());
10. Query the data of the previous year
select * from Table name where year(create_date)=year(date_sub(now(),interval 1 year));
11. Query the data for the current week
select * from Table name where yearweek(date_format(create_date,'%y-%m-%d')) = yearweek(now());
12. Query last week's data
select * from Table name where yearweek(date_format(create_date,'%y-%m-%d')) = yearweek(now())-1;
13. Query the data from last month
select * from Table name where date_format(create_date,'%y-%m')=date_format(date_sub(curdate(), interval 1 month),'%y-%m')
14. Query the data of the current month
select * from Table name where date_format(create_date,'%Y-%m')=date_format(now(),'%Y-%m')
15. Query data 6 months away from the current
select * from Table name where create_date between date_sub(now(),interval 6 month) and now();
16. Check the first day of the week
select
case when dayname(date('2022-3-10'))='sunday'
then date_sub(date('2022-3-10'),interval 6 day)
else date_add('2022-3-10',interval -dayofweek(date('2022-3-10'))+2 day) end
17. Check the last day of the week
select case when dayname(date('2022-3-11'))='sunday' then date('2022-3-11') else date_add('2022-3-11',interval 7-dayofweek('2022-3-11')+1 day) end
18. Check the first day of the month on a certain day
select date_add( date_add(last_day('2022-06-03'),interval 1 day ),interval -1 month );
19. Check the last day of the month on which a certain day is located
select last_day('2022-03-03');
select date_format(now(),'%y-%m-%d %h:%i:%s');
20. Check the number of days of a certain day
select timestampdiff(day,'2023-03-03',(date_add('2017-03-03',interval 1 month)));Attachment: Date function analysis
date_format()
Format date format, "date_format (time stamp, time format)"
date_sub()
Subtract the specified time interval from the date; the function is DATE_SUB(date,INTERVAL expr type), and the date parameter is a legal date expression. The expr parameter is the time interval you want to add. The time interval parameters are very comprehensive, and the commonly used ones are year, month, day, hour, minute and second;
//Decrease the number of daysdate_sub(time，INTERVAL 1 DAY) //Reduced monthdate_sub(time，INTERVAL 1 MONTH)
yearweek()
Returns which week of the specified date is YEARWEEK(date[,mode]) function form, where the format of date is generally 'year-month-day', mode is 1, which means that a week starts from Monday;
weekday()
Accepts 1 parameter, namely DATE or DATETIME value, the function form is WEEKDAY(date); returns an integer, ranging from 0 to 6, representing Monday to Sunday, that is, Monday is 0, Tuesday is 1, and Sunday is 6;
period_diff()
Return the number of months with two times difference, note that it is the previous time minus the next time.
Note: The formats of period1 and period2 should be the same.
SELECT PERIOD_DIFF(201710, 201703);
Summarize
This is the end of this article about common date query methods for mysql database. For more related contents of mysql date query methods, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!