How the DAYOFWEEK() function works in Mariadb?

The MariaDB DAYOFWEEK() function is used to determine the day of the week for a given date, returning an integer value from 1 (Sunday) to 7 (Saturday). This function is handy for scheduling tasks, generating weekly reports, or any application that needs to sort or filter data based on the day of the week.

Syntax

The syntax for the MariaDB DAYOFWEEK() function is as follows:

DAYOFWEEK(date)

date is the date value from which you want to extract the day of the week.

Examples

Example 1: Day of the Week for a Specific Date

To get the day of the week for a specific date:

SELECT DAYOFWEEK('2024-03-17') AS weekday;

The output will be:

+---------+
| weekday |
+---------+
|       1 |
+---------+

This indicates that the date falls on a Sunday.

Example 2: Day of the Week for Today

To find out the day of the week for today:

SELECT DAYOFWEEK(CURDATE()) AS today_weekday;

The output will show the day of the week for today:

+---------------+
| today_weekday |
+---------------+
|             1 |
+---------------+

Example 3: Filtering Data by Day of the Week

To filter data based on the day of the week:

DROP TABLE IF EXISTS events;
CREATE TABLE events (
    id INT,
    event_date DATE,
    event_name VARCHAR(255)
);
INSERT INTO events VALUES (1, '2024-03-17', 'Sunday Brunch'), (2, '2024-03-18', 'Monday Meeting');

SELECT * FROM events WHERE DAYOFWEEK(event_date) = 1;

The output will display events that occur on Sunday:

+------+------------+---------------+
| id   | event_date | event_name    |
+------+------------+---------------+
|    1 | 2024-03-17 | Sunday Brunch |
+------+------------+---------------+

Example 4: Grouping Data by Day of the Week

To group data by the day of the week:

SELECT DAYOFWEEK(event_date) AS weekday, COUNT(*) AS total_events
FROM events
GROUP BY weekday;

The output will show the count of events grouped by the day of the week:

+---------+--------------+
| weekday | total_events |
+---------+--------------+
|       1 |            1 |
|       2 |            1 |
+---------+--------------+

Example 5: Ordering Data by Day of the Week

To order data by the day of the week:

SELECT * FROM events ORDER BY DAYOFWEEK(event_date);

The output will order the events by the day of the week:

+------+------------+----------------+
| id   | event_date | event_name     |
+------+------------+----------------+
|    1 | 2024-03-17 | Sunday Brunch  |
|    2 | 2024-03-18 | Monday Meeting |
+------+------------+----------------+

Here are a few functions related to the MariaDB DAYOFWEEK() function:

MariaDB WEEKDAY() function returns the weekday index for a date (0 = Monday, …, 6 = Sunday).
MariaDB WEEK() function is used to return the week number for a given date.
MariaDB DAYNAME() function returns the name of the weekday for a given date.

Conclusion

The DAYOFWEEK() function in MariaDB is a practical tool for working with dates and their corresponding weekdays. It allows developers to easily query and manipulate data based on the day of the week, which can be crucial for applications that rely on weekly cycles. Whether it’s for organizing events, managing schedules, or generating time-based reports, understanding how to use the DAYOFWEEK() function can significantly enhance the capabilities of your database queries.