How the WEEK() function works in Mariadb?

The MariaDB WEEK() function is one such feature that allows users to extract the week number from a date.

Posted on

MariaDB, as a robust database management system, offers a variety of functions to handle date and time values effectively. The WEEK() function is one such feature that allows users to extract the week number from a date.

Syntax

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

WEEK(date[, mode])

The WEEK() function takes two arguments:

  • date: The date or datetime value from which to extract the week.
  • mode: An optional integer that determines how the week will be calculated. Different modes can return different week numbers for the same date.

Examples

Example 1: Basic Usage of WEEK()

This example shows how to get the week number from a specific date.

SELECT WEEK('2024-03-10');

+--------------------+
| WEEK('2024-03-10') |
+--------------------+
|                 10 |
+--------------------+

The output indicates that the date ‘2024-03-10’ falls in the 10th week of the year.

Example 2: WEEK() with Different Modes

Different modes can affect the result of the WEEK() function. Here’s how you can use it with a mode value.

SELECT WEEK('2024-03-10', 1);

+-----------------------+
| WEEK('2024-03-10', 1) |
+-----------------------+
|                    10 |
+-----------------------+

With mode 1, the week starts on Monday and the week number for ‘2024-03-10’ is 11.

Example 3: Creating a Calendar Table

To demonstrate the WEEK() function in a real-world scenario, let’s create a calendar table and insert some dates.

DROP TABLE IF EXISTS calendar;
CREATE TABLE calendar (event_date DATE);

INSERT INTO calendar VALUES ('2024-01-01'), ('2024-12-31');

SELECT event_date, WEEK(event_date) AS week_number FROM calendar;

+------------+-------------+
| event_date | week_number |
+------------+-------------+
| 2024-01-01 |           0 |
| 2024-12-31 |          52 |
+------------+-------------+

This shows the week numbers for the start and end of the year 2024.

Example 4: Filtering Records by Week Number

You can filter records by week number using the WEEK() function in a WHERE clause.

SELECT * FROM calendar
WHERE WEEK(event_date) = 52;

Assuming there are dates that fall in the 52nd week, this query will return those records.

Example 5: Comparing Current Week with Event Weeks

This example compares the current week number with event weeks in the calendar.

SELECT event_date, WEEK(event_date) = WEEK(CURDATE()) AS is_current_week FROM calendar;

+------------+-----------------+
| event_date | is_current_week |
+------------+-----------------+
| 2024-01-01 |               0 |
| 2024-12-31 |               0 |
+------------+-----------------+

The output indicates whether the event dates fall in the current week.

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

  • MariaDB YEARWEEK() function returns the year and week number for a given date.
  • MariaDB DAYOFWEEK() function returns the weekday index for a given date (1 = Sunday, 2 = Monday, …, 7 = Saturday).
  • MariaDB WEEKDAY() function returns the weekday index for a given date (0 = Monday, 1 = Tuesday, …, 6 = Sunday).

Conclusion

The WEEK() function in MariaDB is a versatile tool for working with date-related data. It provides a convenient way to extract the week number from a date, which can be particularly useful for reporting and data analysis tasks. Understanding how to use this function, along with its related functions, can greatly enhance the handling of temporal data in MariaDB.