MySql return rows matching year month andrey воскресенье, 24 августа 2014 г. No Comment

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...

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...

Tweet

by Jillur Rahman

Jillur Rahman is a Web designers. He enjoys to make blogger templates. He always try to make modern and 3D looking Templates. You can by his templates from Themeforest.

Follow him @ Twitter | Facebook | Google Plus

Tags: Без рубрики