DAYOFWEEK() function to get number from 1 to 7 based on Weekday in MySQL
Filter records of all weekdays up to today
This function dayofweek returns values 1 to 7 based on the weekday starting from Sunday as 1, Monday as 2 and …so on for others. So if today is Thursday then dayofweek function will return 5.
So we need before three days record ( including today ) from today to get the records stating from Monday. So we will deduct 2 ( Monday value ) from the weekday figure. Here is the query to get the records of all weekdays of a week till today.
SELECT id,date,weekday, dayofweek(date) as dayofweek
FROM dt_weekday
WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL (dayofweek(CURDATE())-2) DAY ) AND CURDATE( )
Above query will collect records starting from Monday and ending with today. For example if today is Tuesday then you will get two records ( of Monday and Tuesday )
Below query will return the Date of Monday of present week.
SELECT DATE_SUB( CURDATE( ) ,INTERVAL (dayofweek(CURDATE())-2) DAY )
By using weekday function we will get the same result but here weekday will return different number for days.
Monday = 0, Tuesday = 1 and so on ...
Here is a simple query
SELECT WEEKDAY( '2014-08-15' )
Output of above query is 4 . (15th Aug 2014 is Friday).
Here is the query using weekday function to get records of the week starting from Monday.
SELECT id, DATE, weekday, WEEKDAY( CURDATE( ) ) AS weekday
FROM dt_weekday
WHERE `date` BETWEEN DATE_SUB( CURDATE( ) , INTERVAL( WEEKDAY( CURDATE( ) ) ) DAY ) AND CURDATE( )
Previous One week records
By adjusting the interval in our above query we can return the records of previous Week starting from Monday to Saturday.
SELECT id, DATE, weekday, DAYOFWEEK( CURDATE( ) ) AS dayofweek
FROM dt_weekday
WHERE `date`
BETWEEN DATE_SUB( CURDATE( ) , INTERVAL (dayofweek(CURDATE())+5)
DAY )
AND DATE_SUB( CURDATE( ) , INTERVAL (dayofweek(CURDATE()))
DAY )
Previous Two week records
This query can be further adjusted to return previous two weeks record. ( Check the difference with previous query )
SELECT id, DATE, weekday, DAYOFWEEK( CURDATE( ) ) AS dayofweek
FROM dt_weekday
WHERE `date`
BETWEEN DATE_SUB( CURDATE( ) , INTERVAL( DAYOFWEEK( CURDATE( ) ) +12 )
DAY )
AND DATE_SUB( CURDATE( ) , INTERVAL( DAYOFWEEK( CURDATE( ) ) +7 )
DAY )
Dynamic SQL Dump
Here is the sql dump to create table dt_weekday. This dump is dynamically created by considering previous 15 days and next 15 days starting from today. You will have one record for each day.
You should always take a fresh dump file from here for your testing, if you are not executing on same day.
One column weekday is kept to store name of the weekday ( sun, mon, tue … ) for your understanding.
CREATE TABLE IF NOT EXISTS `dt_weekday` (
`id` varchar(3) NOT NULL,
`date` date NOT NULL,
`weekday` varchar(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `dt_weekday` VALUES ('-15', '2024-12-25','Wed' ); INSERT INTO `dt_weekday` VALUES ('-14', '2024-12-24','Tue' ); INSERT INTO `dt_weekday` VALUES ('-13', '2024-12-23','Mon' ); INSERT INTO `dt_weekday` VALUES ('-12', '2024-12-22','Sun' ); INSERT INTO `dt_weekday` VALUES ('-11', '2024-12-21','Sat' ); INSERT INTO `dt_weekday` VALUES ('-10', '2024-12-20','Fri' ); INSERT INTO `dt_weekday` VALUES ('-9', '2024-12-19','Thu' ); INSERT INTO `dt_weekday` VALUES ('-8', '2024-12-18','Wed' ); INSERT INTO `dt_weekday` VALUES ('-7', '2024-12-17','Tue' ); INSERT INTO `dt_weekday` VALUES ('-6', '2024-12-16','Mon' ); INSERT INTO `dt_weekday` VALUES ('-5', '2024-12-15','Sun' ); INSERT INTO `dt_weekday` VALUES ('-4', '2024-12-14','Sat' ); INSERT INTO `dt_weekday` VALUES ('-3', '2024-12-13','Fri' ); INSERT INTO `dt_weekday` VALUES ('-2', '2024-12-12','Thu' ); INSERT INTO `dt_weekday` VALUES ('-1', '2024-12-11','Wed' ); INSERT INTO `dt_weekday` VALUES ('0', '2024-12-10','Tue' ); INSERT INTO `dt_weekday` VALUES ('1', '2024-12-09','Mon' ); INSERT INTO `dt_weekday` VALUES ('2', '2024-12-08','Sun' ); INSERT INTO `dt_weekday` VALUES ('3', '2024-12-07','Sat' ); INSERT INTO `dt_weekday` VALUES ('4', '2024-12-06','Fri' ); INSERT INTO `dt_weekday` VALUES ('5', '2024-12-05','Thu' ); INSERT INTO `dt_weekday` VALUES ('6', '2024-12-04','Wed' ); INSERT INTO `dt_weekday` VALUES ('7', '2024-12-03','Tue' ); INSERT INTO `dt_weekday` VALUES ('8', '2024-12-02','Mon' ); INSERT INTO `dt_weekday` VALUES ('9', '2024-12-01','Sun' ); INSERT INTO `dt_weekday` VALUES ('10', '2024-11-30','Sat' ); INSERT INTO `dt_weekday` VALUES ('11', '2024-11-29','Fri' ); INSERT INTO `dt_weekday` VALUES ('12', '2024-11-28','Thu' ); INSERT INTO `dt_weekday` VALUES ('13', '2024-11-27','Wed' ); INSERT INTO `dt_weekday` VALUES ('14', '2024-11-26','Tue' ); INSERT INTO `dt_weekday` VALUES ('15', '2024-11-25','Mon' ); INSERT INTO `dt_weekday` VALUES ('16', '2024-11-24','Sun' );