Home > Database > Mysql Tutorial > body text

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

Barbara Streisand
Release: 2024-11-11 21:00:04
Original
437 people have browsed it

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)
Copy after login

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);
Copy after login

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'
Copy after login

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);
Copy after login

This will produce the following result:

2011-01-02
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template