MySQL: Fetching rows from specified period of time

2012-06-26 Databases

Let's say that in one of our tables we store informations when our employees had their holidays. Columns "absent" stores date in one of MySQL format. How, from thousands of rows (where each describes one day), fetch only those from specific month? For purpose of this article let's say that format of our stored dates will look like this:

yyyy-MM-dd HH:mm:ss

We will use built-in functions from MySQL. Simple query has been shown below. I'm going to fetch all days where fictitious employee (John Kowalski) was on holidays (starting from beginning of his employment):

mysql select name, surname, absent from employee_holidays where name = 'John' and surname = 'Kowalski';

As a result I'll get a table:

+------+----------+---------------------+
| name | surname  | absent              |
+------+----------+---------------------+
| John | Kowalski | 2010-07-28 00:00:00 |
| John | Kowalski | 2010-08-09 00:00:00 |
| John | Kowalski | 2010-08-10 00:00:00 |
| John | Kowalski | 2010-08-19 00:00:00 |
| John | Kowalski | 2010-09-06 00:00:00 |
| John | Kowalski | 2010-09-07 00:00:00 |
| John | Kowalski | 2010-09-08 00:00:00 |
+------+----------+---------------------+ 7 rows in set (0.00 sec)

This is ofcourse an example. I know that data should be fetched using different fields like primary key (user ID) or things like that but that's not the thing that You should focus on.

Now, we want to fetch all days, where Kowalski was enjoying sun tan, but in August 2010. So:

mysql select name, surname, absent from employee_holidays where name = 'John' and surname = 'Kowalski' and ( year(absent) = 2010 and month(absent) = 8 );

As You can see, MySQL will reduce our result simply to all rows with matching criteria:

+------+----------+---------------------+
| name | surname  | absent              |
+------+----------+---------------------+
| John | Kowalski | 2010-08-09 00:00:00 |
| John | Kowalski | 2010-08-10 00:00:00 |
| John | Kowalski | 2010-08-19 00:00:00 |
+------+----------+---------------------+ 3 rows in set (0.00 sec)
Do NOT follow this link or you will be banned from the site!