Get records of current month

Question

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

http://stackoverflow.com/questions/16500527/select-current-months-records-mysql-from-timestamp-column — Nagaraj S, Jan 22 '14 at 07:50

Aman Aggarwal · Accepted Answer · 2017-10-05T05:01:23.090

189

This query should work for you:

SELECT *
FROM table
WHERE MONTH(columnName) = MONTH(CURRENT_DATE())
AND YEAR(columnName) = YEAR(CURRENT_DATE())

edited Oct 05 '17 at 05:01

answered Jan 22 '14 at 07:49

Aman Aggarwal

16,184
9
53
77

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
welcome @MaheshJadhav – Aman Aggarwal Aug 17 '21 at 02:00

Saharsh Shah · Answer 2 · 2014-01-22T07:53:06.743

38

Check the MySQL Datetime Functions:

Try this:

SELECT * 
FROM tableA 
WHERE YEAR(columnName) = YEAR(CURRENT_DATE()) AND 
      MONTH(columnName) = MONTH(CURRENT_DATE());

edited Jan 22 '14 at 07:53

answered Jan 22 '14 at 07:44

Saharsh Shah

27,975
8
43
82

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
3

@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
3

select * from t.stuff where date between DATE_FORMAT(NOW(),"%Y-%m-01") and LAST_DAY(NOW()); – Martian2049 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

score 4 · Answer 3 · edited Mar 12 '14 at 01:34

4

Try this query:

SELECT *
FROM table 
WHERE MONTH(FROM_UNIXTIME(columnName))= MONTH(CURDATE())

edited Mar 12 '14 at 01:34

hichris123

9,955
15
53
68

answered Jan 22 '14 at 08:10

ankur

127
1
2
9

1

Bad: Will calculate the `MONTH()` for each row. Better try a range comparison on the column. – Sebastian Sep 20 '17 at 08:11
5

@Sebastian Please, provide an answer – programaths Nov 07 '17 at 10:06

Get records of current month

3 Answers3

Linked