Sunday 24 August 2014

MySql return rows matching year month 0

If we want to get rows by filtering given month and year, We can use 2 popular ways.

  1. EXTRACT()
  2. 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...


0 comments:

Post a Comment