MySQL Get same yesterday day of previous week data

Question

Let say yesterday was Wednesday; I need to get all records for Wednesday of previous week. How to achieve this in MySQL statement condition?

Answer 1

Yesterday would be today - 1 day. Then subtract additional 7 days:

select *
from t
where date_column >= current_date - interval 8 day
and   date_column <  current_date - interval 7 day

Answer 2

SELECT *
 FROM Table
 where ( (datetime between date()-7 and date()) ) order by datetime DESC

Answer 3

You can directly subtract 7 days from your day of interest if you wish to get all rows in the previous week's day of interest:

SET @Wedn_date = '2022-05-11 14:00:00';

SELECT *
FROM   tab
WHERE DATEDIFF(@Wedn_date, date) = 7

Check the SQL Fiddle here.