61

How can I select Current Month records from a table of MySql database??

Like now current month is January. I would like to get records of January Month, Where data type of my table column is timestamp.I would like to know the sql query.

Thanks

Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
Foysal Vai
  • 1,365
  • 3
  • 13
  • 19
  • 1
    http://stackoverflow.com/questions/16500527/select-current-months-records-mysql-from-timestamp-column – Nagaraj S Jan 22 '14 at 07:50

3 Answers3

203

This query should work for you: 

SELECT *
FROM table
WHERE MONTH(columnName) = MONTH(CURRENT_DATE())
AND YEAR(columnName) = YEAR(CURRENT_DATE())
Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
  • 6
    This is not quite correct. If I have records with `2015-12-25` and `2016-12-30` they will both be selected, which I don't think is the expected behavior. Instead try the solution by @saharsh-shah below. – Pila Jun 22 '17 at 12:12
  • 4
    Bad: Will calculate the `MONTH()` for each row. Better try a range comparison on the column. – Sebastian Sep 20 '17 at 08:11
  • 10
    @Sebastian Please, provide an answer – programaths Nov 07 '17 at 10:06
  • 4
    The question is marked as duplicated, see the linked questions. New answers can't be added. You should go for something like `columnName > "2017-11-01 00:00:00"`. – Sebastian Nov 08 '17 at 10:50
  • 2
    @Pila's comment was accurate at the time, but the answer has since been updated for the query to result in the correct functionality. – David Sawyer Aug 30 '18 at 14:52
  • For postgre people you just need date_trunc like SELECT * FROM tableA WHERE date_trunc('month',columnName) = date_trunc('month',CURRENT_DATE) – amar Mar 23 '20 at 06:02
  • Thank you @Aman for the quick one! Worked! – Mahesh Jadhav Aug 16 '21 at 07:12
41

Check the MySQL Datetime Functions:

Try this: 

SELECT * 
FROM tableA 
WHERE YEAR(columnName) = YEAR(CURRENT_DATE()) AND 
      MONTH(columnName) = MONTH(CURRENT_DATE());
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • 5
    Bad: Will calculate the `MONTH()` for each row. Better try a range comparison on the column. – Sebastian Sep 20 '17 at 08:11
  • 2
    @Sebastian Please, provide an answer – programaths Nov 07 '17 at 10:06
  • 4
    @Sebastian why not provide an answer? – Kishy Nivas Mar 02 '18 at 16:36
  • There are two duplicates linked at the top of the page. Both provide good answers. Why should I add another one? Anyway: Adding answers to duplicate questions isn't possible. – Sebastian Mar 04 '18 at 12:10
  • 6
    select * from t.stuff where date between DATE_FORMAT(NOW(),"%Y-%m-01") and LAST_DAY(NOW()); – MartianMartian Aug 29 '18 at 17:29
  • @Sebastian I am a newbie for SQL. Will you please explain how is this a bad answer? What is meant by Will calculate the MONTH() for each row. Better try a range comparison on the column – Bhaumik Bhatt Nov 27 '19 at 12:36
  • MySQL will walk through the table and calculate MONTH(x) for each row, then check if it's today. @Martian2049's example will allow using an index or at least avoid calculation MONTH() on each row. Imagine a 1.200.000 rows table starting ten yrs. ago: Using an index and no MONTH() will reduce the amount to check to 100k instead if 1,2M. – Sebastian Dec 02 '19 at 09:40
4

Try this query:

SELECT *
FROM table 
WHERE MONTH(FROM_UNIXTIME(columnName))= MONTH(CURDATE())
hichris123
  • 10,145
  • 15
  • 56
  • 70
ankur
  • 127
  • 1
  • 2
  • 10