dayofweek() : Records of the weekdays

To get the number of weekday ( from 1 to 7 ) we will use dayofweek function of MySQL.

DAYOFWEEK(date)

Example

SELECT DAYOFWEEK('2016-05-06')

Output is 6.
For a invalid date output will give NULL
1 = Sunday , 2 = Monday, 3 = Tuesday, 4 = Wednesday , 5 = Thursday, 6 = Friday, 7 = Saturday

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 ( excluding today ) from today to get the records stating from Monday. So we will deduct 2 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(CURDATE()) 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 )

Please read the tutorial on last x day's record in part 1 of this tutorial. Here we will develop a query to get records of weekdays of the present week.

For your understanding we have displayed dayofweek value against each record.

Using weekday function

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', '2021-04-09','Fri' ); 
INSERT INTO `dt_weekday` VALUES ('-14', '2021-04-08','Thu' ); 
INSERT INTO `dt_weekday` VALUES ('-13', '2021-04-07','Wed' ); 
INSERT INTO `dt_weekday` VALUES ('-12', '2021-04-06','Tue' ); 
INSERT INTO `dt_weekday` VALUES ('-11', '2021-04-05','Mon' ); 
INSERT INTO `dt_weekday` VALUES ('-10', '2021-04-04','Sun' ); 
INSERT INTO `dt_weekday` VALUES ('-9', '2021-04-03','Sat' ); 
INSERT INTO `dt_weekday` VALUES ('-8', '2021-04-02','Fri' ); 
INSERT INTO `dt_weekday` VALUES ('-7', '2021-04-01','Thu' ); 
INSERT INTO `dt_weekday` VALUES ('-6', '2021-03-31','Wed' ); 
INSERT INTO `dt_weekday` VALUES ('-5', '2021-03-30','Tue' ); 
INSERT INTO `dt_weekday` VALUES ('-4', '2021-03-29','Mon' ); 
INSERT INTO `dt_weekday` VALUES ('-3', '2021-03-28','Sun' ); 
INSERT INTO `dt_weekday` VALUES ('-2', '2021-03-27','Sat' ); 
INSERT INTO `dt_weekday` VALUES ('-1', '2021-03-26','Fri' ); 
INSERT INTO `dt_weekday` VALUES ('0', '2021-03-25','Thu' ); 
INSERT INTO `dt_weekday` VALUES ('1', '2021-03-24','Wed' ); 
INSERT INTO `dt_weekday` VALUES ('2', '2021-03-23','Tue' ); 
INSERT INTO `dt_weekday` VALUES ('3', '2021-03-22','Mon' ); 
INSERT INTO `dt_weekday` VALUES ('4', '2021-03-21','Sun' ); 
INSERT INTO `dt_weekday` VALUES ('5', '2021-03-20','Sat' ); 
INSERT INTO `dt_weekday` VALUES ('6', '2021-03-19','Fri' ); 
INSERT INTO `dt_weekday` VALUES ('7', '2021-03-18','Thu' ); 
INSERT INTO `dt_weekday` VALUES ('8', '2021-03-17','Wed' ); 
INSERT INTO `dt_weekday` VALUES ('9', '2021-03-16','Tue' ); 
INSERT INTO `dt_weekday` VALUES ('10', '2021-03-15','Mon' ); 
INSERT INTO `dt_weekday` VALUES ('11', '2021-03-14','Sun' ); 
INSERT INTO `dt_weekday` VALUES ('12', '2021-03-13','Sat' ); 
INSERT INTO `dt_weekday` VALUES ('13', '2021-03-12','Fri' ); 
INSERT INTO `dt_weekday` VALUES ('14', '2021-03-11','Thu' ); 
INSERT INTO `dt_weekday` VALUES ('15', '2021-03-10','Wed' ); 
INSERT INTO `dt_weekday` VALUES ('16', '2021-03-09','Tue' );

Web Development

Thursday, March 25, 2021