How to make an appointment to get the dates according to the weekdays?

Question

How to make a certain query in MYSQL by picking the dates according to the day of the week?

For example:

+-------+---------------------+
| id    | created_at          |
+-------+---------------------+
| 49185 | 2015-02-19 02:35:10 |
| 49186 | 2015-09-03 06:22:51 |
| 49190 | 2015-05-27 03:09:28 |
| 49191 | 2015-09-04 01:32:00 |
| 51876 | 2016-04-18 16:52:14 |
| 51880 | 2016-06-23 06:03:34 |
| 51881 | 2016-06-23 07:02:01 |
| 51882 | 2016-06-24 07:18:10 |
| 51883 | 2016-07-12 13:17:33 |
| 51885 | 2016-09-28 07:28:26 |
| 51886 | 2016-10-27 06:35:25 |
| 51887 | 2016-12-07 14:59:44 |
+-------+---------------------+

In this example, how could I return only the dates that refer to Saturday or Sunday?

Answer 1

Just use

WHERE DAYOFWEEK( data ) = dia_desejado

The days of the week are numbered from 1 (Sunday) to 7 (Saturday).

Applying to your case:

SELECT campos FROM tabela WHERE DAYOFWEEK( data ) IN ( 1, 7 );

Handbook:

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek