How to search for saved records in the current week?

Question

I’m doing a database query, and I plan to list the records for the current week. It would be something based on the question about retrieve data from last 7 days from current date, but instead of being from the current date, it would be the beginning of the week.

See below the table columns tbl_vacina:

+------------+--------------+-------------------+
|     ID     |    vacina    |    data_criacao   |
+------------+--------------+-------------------+
|     1      |    Cinomose  |2017-06-10 10:11:15|
+------------+--------------+-------------------+
|     2      | Coronavirose |2017-06-09 10:11:15|
+------------+--------------+-------------------+

• id - int
• vacina - varchar(512)
• data_criacao - timestamp

How would a query to return all records saved in the current week?

Answer 1

I would suggest the use of function YEARWEEK()

SELECT *
FROM   tblVacina
WHERE  YEARWEEK(data_criacao, 1) = YEARWEEK(CURDATE(), 1)

(I’m trying to create Sqlfiddle but I can’t execute)

Answer 2

If you were to compare whether two arbitrary dates are in the same week:

SELECT
   campos
FROM
   tabela
WHERE
   DATE_SUB(data1, INTERVAL DAYOFWEEK(data1)-1 DAY)
   =
   DATE_SUB(data2, INTERVAL DAYOFWEEK(data2)-1 DAY);

Applying to your case:

WHERE
   DATE_SUB(data_criacao, INTERVAL DAYOFWEEK(data_criacao)-1 DAY)
   =
   DATE_SUB(CURRENT_DATE, INTERVAL DAYOFWEEK(CURRENT_DATE)-1 DAY);

Basically, if we take a date and extract the day of the current week, we have the Sunday immediately before. If it’s the same in both cases, it’s the same week.

If you don’t have future data in DB, you can simplify it like this:

SELECT
   campos
FROM
   tabela
WHERE
   data_criacao >= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFWEEK(CURRENT_DATE)-1 DAY)

As we are using today’s date as a reference, it is sufficient that the data_criacao is the same or younger than last Sunday.