CODEWITHSUNDEEP
mysql
user1783933
user1783933

Reputation: 1145

Returning records from the last 3 months only in MySQL

I have a table with a timestamp field. How do I get data from the last 3 months?

In particular, March is my current month let say, 03/2012. I need to return records from the months March, February, and January only.

Upvotes: 42

Views: 96193

Answers (8)

harshit rathod
harshit rathod

Reputation: 64

select metric,MONTH(Created) as month,YEAR(Created) as year
from table
where metric = value
AND ( ( YEAR(Created) = YEAR(NOW()) AND DATE(Created) >= DATE(NOW() - INTERVAL :month MONTH) )
   OR ( Year(Created) = YEAR(NOW()) - 1 AND Month(Created) > MONTH(INTERVAL :month MONTH + NOW()) ) )
order by Created;

Mysql query to show last X months data.

Note : This query wouldn't work if X > 12

Upvotes: 0

Thorskii
Thorskii

Reputation: 1

Use this code here to get the previous 3 months from a certain date

SELECT * FROM table WHERE date_column>= DATE_FORMAT(current_date(), '%Y-%m-01') - INTERVAL 3 MONTH and date_column< DATE_FORMAT(current_date(), '%Y-%m-01')

Upvotes: 0

maryisdead
maryisdead

Reputation: 1810

Another possibility would be:

SELECT * WHERE your_date_column > LAST_DAY(CURRENT_DATE - INTERVAL 3 MONTH);

Upvotes: 0

Atish
Atish

Reputation: 1

WHERE ((timestamp >= NOW() - DATE_FORMAT(CURDATE(), '%Y-%m-01'))
OR  (timestamp >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 2 MONTH))

Upvotes: -1

MLeblanc
MLeblanc

Reputation: 1884

Assuming you're using SQL Server (Oracle, MySQL and others have similar date functions), you can use the dateadd function to add or subtract an interval to the current date.

If you want a full three months, you can subtract 3 months from today : DATEADD(m,-3,getdate())

But, as you state, you only want data from January, February and March. You have to make some calculation based on today's date: dateadd(m,-2, CONVERT(datetime, CONVERT(VARCHAR(2), MONTH(getdate())) + '/01/' + CONVERT(VARCHAR(4), YEAR(getdate()))))

And in the end, get a query like 

SELECT fields 
FROM table 
WHERE timestampfield > DATEADD(m,-2, CONVERT(datetime, CONVERT(VARCHAR(2), MONTH(getdate()))  + '/01/' + CONVERT(VARCHAR(4), YEAR(getdate()))))

--- edit --- erf, I just noticed the "mysql" tag... you can get more information on MySQL date functions here : https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Upvotes: 3

Pete_1
Pete_1

Reputation: 1011

I know this is an old question, but to possibly save others time and to sum the above answers up for the case of needing (1) dates from current month and (2) dates from the prior 2 months (common when displaying data statistics):

WHERE ((timestamp >= NOW() - DATE_FORMAT(CURDATE(), '%Y-%m-01'))
OR  (timestamp >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 2 MONTH))

Upvotes: 3

AlwaysBTryin
AlwaysBTryin

Reputation: 1964

3 months before today:

select * from table where timestamp >= now()-interval 3 month;

Start with first of month:

select * from table where timestamp >= last_day(now()) + interval 1 day - interval 3 month;

Upvotes: 96

fthiella
fthiella

Reputation: 49049

To get the first day of the current month, you could use this:

DATE_FORMAT(CURDATE(), '%Y-%m-01')

if current date is 2013-03-13, it will return 2013-03-01, and we can just substract 2 months from this date to obtain 2013-01-01. Your query could be like this:

SELECT *
FROM yourtable
WHERE data >= DATE_FORMAT(CURDATE(), '%Y-%m-01') - INTERVAL 2 MONTH

Upvotes: 15

Related Questions