Is there a function in MySQL to return the VERY beginning of a week for a given date?-CodePudding

So, let's say the current date is 2022-03-04 10:45:26, I want to have a query retuning every entry after 2022-02-27 00:00:00.

I have found SUBDATE(eventDate, WEEKDAY(eventDate)) but this is not precise enough.

In other words, this is the query I am looking for

SELECT * 
FROM ts_punch_events
WHERE eventDate > "2022-02-27 00:00:00";

But using a parameter, like NOW() or a user-defined one, to filter the results.

In other words, I'm looking for a way to have a "function" returning this

fn("2022-03-04 10:45:26");   ->   "2022-02-26 00:00:00"
fn("2022-01-07 09:38:43");   ->   "2022-01-02 00:00:00"
fn(NOW())                    ->   ... first day of the week at 00:00:00

The equivalent in JavaScript would be startOfWeek.

// The start of a week for 2 September 2014 11:55:00:
const result = startOfWeek(new Date(2014, 8, 2, 11, 55, 0))
//=> Sun Aug 31 2014 00:00:00

CodePudding user response：

SELECT DATE(eventDate - INTERVAL (WEEKDAY(eventDate)   1) MOD 7 DAY) AS week_start
FROM datatable

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f3ede945ed02693b47d5f9fb2018ea6e