DAYOFWEEK in MariaDB

MariaDB is an open-source relational database management system that is a subset of MySQL which is based on SQL(Structured query language). It is an improved version of MySQL and has various features, security, and performance when compared to MySQL. In this article we will learn about the DAYOFWEEK() function with its syntax and various examples and so on.

DAYOFWEEK() in MariaDB

The DAYOFWEEK function is used to return the weekday index for a given date. The index starts from 1 for Sunday and goes up to 7 for Saturday. This function is optimized for performance within MariaDB. It efficiently calculates the weekday index even for large datasets, ensuring minimal effect on query execution time.

Syntax:

DAYOFWEEK(any_date)

Explanation: In the above syntax the any_date can be:

• A column name containing date values.
• A string representing a date in the format YYYY-MM-DD.
• A date literal directly enclosed in single quotes.

Example of DAYOFWEEK in MariaDB

To understand the DAYOFWEEK function easily, we need a table on which we will perform various operations and queries. So here we will create a orders table which consists of id and order_date.

CREATE TABLE orders(
  id INT PRIMARY KEY AUTO_INCREMENT,
  order_date DATE
);

Output:

Creation of the table orders

Let’s insert some records into the orders table.

INSERT INTO orders(order_date) 
VALUES ('2024-02-12'), ('2024-01-25'), ('2023-12-31');

Output:

Insertion of values into orders

Explanation:

Example 1: Getting the Day of the Week for a Specific Date

The below query just return the day of week of the given date.

SELECT DAYOFWEEK('2024-02-12');

Output:

Day of week of specific date

Explanation: In the above query it returns 2 which defiens that the February 12 of 2024 falls on a Monday, with Monday whose index is 2.

Example 2: Getting The Day of the Week for a Column of Dates

The below query outputs a table with order_data column and its respective day of the week in the day_of_week column.

SELECT order_date, DAYOFWEEK(order_date) AS day_of_week
FROM orders;

Output:

Day of week of column of dates

Explanation: In the above query it retrieves the order_date column from the orders table along with the corresponding day of the week using the DAYOFWEEK function, which returns an integer representing the day of the week .

Example 3: Filtering Data Based on the Day of the Week

The below query outputs a table with order date with only the respective days.

SELECT order_date FROM orders
WHERE DAYOFWEEK(order_date) IN (2, 1, 6);

Output:

Filtering based on day of week

Explanation: In the above query it selects order_date values from the orders table where the corresponding day of the week is either Monday (2), Sunday (1), or Friday (6), based on the result of the DAYOFWEEK function.

Conclusion

Overall, the DAYOFWEEK() function in MariaDB is a important function for handling date-related operations efficiently. with its simple syntax and optimized performance we can easily determining the day of the week for a given date and filtering data based on weekdays. Now you can easily perform as many can operations related to the Days of Weeks.