How do I search for records saved in the current week?

Navigation
9

I'm doing a query on a database, and I want to list the records for the current week. It would be something based on the question about fetching data from the last 7 days from the current date , but instead from the current date, would be the start of the week.

See below the columns of the tbl_vacina table: 

+------------+--------------+-------------------+
|     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 return all records saved in the current week?

    
asked by anonymous 25.01.2017 / 17:51

2 answers

11

I suggested using the YEARWEEK () 

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

(I am trying to create SQLFiddle but I am not able to execute)

    
25.01.2017 / 18:19
9

If it were to compare if 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 current day of the week, we have the Sunday immediately preceding. If it is the same in both cases, it is the same week.


If you do not have future data in the 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 enough that data_criacao is equal to or newer than last Sunday.

    
25.01.2017 / 17:56
What is the difference between match_parent and fill_parent? How to dynamically create properties in C #?