If we want to get rows by filtering given month and year, We can use 2 popular ways.
SOLUTION 1 :
- EXTRACT()
- BETWEEN
1. EXTRACT( ):
The EXTRACT() function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.
SYNTAX:
EXTRACT(unit FROM date)
This function have a following units to use.
MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.
This function is really useful when working with dates in mysql. Ok, Let we see how to use EXTRACT() in this case,
SOLUTION 1 :
SELECT * FROM table
WHERE EXTRACT(YEAR_MONTH FROM timestamp_field) = EXTRACT(YEAR_MONTH FROM NOW())
We can replace NOW() with our date like this below code.
SELECT * FROM table
WHERE EXTRACT(YEAR_MONTH FROM timestamp_field) = EXTRACT(YEAR_MONTH FROM '2014-08-25')
2. BETWEEN :
We can use BETWEEN clause to replace a combination of "greater than equal AND less than equal" conditions.
SYNTAX:
BETWEEN A AND B
Ok, Let we see how to use BETWEEN in this case,
SOLUTION 2:
SELECT *
FROM myTable
WHERE date_column BETWEEN '2014-08-01' AND '2014-08-31
We should make sure our date_column have a date format like 'Y-m-d' when using this method. Or, simply change your timestamp format to DATE_FORMAT().
Note:
You could extract the year and month using a function, but that will not be able to use an index.
If you want scalable performance, you need to use BETWEEN.
Still have any doubt? Feel free to comment here...
Posts
