How to Find the First Day of the Week in MySQL?

Obtaining the Week's First Day in MySQL

When dealing with temporal data in databases, it's often necessary to determine the first day of a given week. This is particularly useful for grouping data and analyzing trends over time.

The Problem Statement

A user has a query that groups data by weeks but wants to display the first day of each week instead of different dates. They have identified a date (2011-01-03) and need to retrieve its corresponding first day of the week (2011-01-02).

Week Starting on Sunday

If the week starts on Sunday, the following MySQL expression can be used:

DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY)

This expression calculates the first day of the week by subtracting the current day of the week (1-based) from the given date and then adding 1 day.

Week Starting on Monday

If the week starts on Monday, a different expression is required:

DATE_ADD(mydate, INTERVAL(-WEEKDAY(mydate)) DAY);

In this case, the current day of the week (0-based) is subtracted from the given date to determine the number of days to go back to reach Monday.

Example Usage

Suppose we have the following date, which represents 2011-01-03:

'2011-01-03'

If the week is assumed to start on Sunday, we can use the first expression to find the first day of the week:

SELECT DATE_ADD('2011-01-03', INTERVAL(1-DAYOFWEEK('2011-01-03')) DAY);

This will produce the following result:

2011-01-02

As expected, the first day of the week starting on Sunday is 2011-01-02.

The above is the detailed content of How to Find the First Day of the Week in MySQL?. For more information, please follow other related articles on the PHP Chinese website!