CODEWITHSUNDEEP
mysql
metost
metost

Reputation: 33

MySQL select records for the past 3 months

what will be the smartest way to select all rows from MySQL table for the past 3 months if the table has the following columns:

| id (int) | year (int)| month (int) |

Considering that if the current month & year are for example 2.2016 I need to select all records for 11.2015 & 12.2015 & 1.2016

It is easy if the current month is greater than 3 because all months that I need to select are in the same year so I can subtract 3 from the current month and run simple query 

SELECT * FROM mytabe where year=2016 and month >= xx

Upvotes: 3

Views: 5596

Answers (3)

Morris Muriuki Muthigani
Morris Muriuki Muthigani

Reputation: 11

Please try this

Select * from mytable where STR_TO_DATE(concat(year,"-",month,"-01"),'%Y-%m-%d')>date_sub(curdate(),Interval 3 month) ;

Upvotes: 0

Muhammad Mehdi
Muhammad Mehdi

Reputation: 472

You can select three Months records by these queries follow this. The columnName means which column data want you select. The tableName means which table data want you select. The dateColumnName means which column date base you want to select data.

It would return Last Month data from today.

SELECT columName FROM tableName WHERE dateColumName BETWEEN ( DATE(NOW()) - INTERVAL 1 MONTH) AND Date(Now())

It would return Second Last Month data from today.

SELECT columName FROM tableName WHERE dateColumName BETWEEN ( DATE(NOW()) - INTERVAL 2 MONTH) AND ( DATE(NOW()) - INTERVAL 1 MONTH)

It would return Third Last Month data from today.

SELECT columName FROM tableName WHERE dateColumName BETWEEN ( DATE(NOW()) - INTERVAL 3 MONTH) AND ( DATE(NOW()) - INTERVAL 2 MONTH)

May It help to others.

Upvotes: 3

undefined_variable
undefined_variable

Reputation: 6228

Select * from mytable where STR_TO_DATE(concat(year,"-",month,"-01"),'%Y-%m-%d')>date_sub(curdate(),Interval 3 month) ;

The above query will get fetch year and month from date 3 months before today

Upvotes: 4

Related Questions