How to select last 6 months from news table using MySQL
MysqlSqlMysql Problem Overview
I am trying to select the last 6 months of entries in a table, I have a column called datetime and this is in a datetime mysql format.
I have seen many ways using interval and other methods - which method should I use? Thanks
Mysql Solutions
Solution 1 - Mysql
Use DATE_SUB
.... where yourdate_column > DATE_SUB(now(), INTERVAL 6 MONTH)
Solution 2 - Mysql
Try this:
select *
from table
where your_dt_field >= date_sub(now(), interval 6 month);
Query reads: give me all entries in table where the field corresponding to the entry date is newer than 6 months.
Solution 3 - Mysql
You can get last six month's data by subtracting interval of 6 month from CURDATE() (CURDATE() is MySQL function which returns Today's date).
SELECT * FROM table
WHERE your_date_field >= CURDATE() - INTERVAL 6 MONTH;
Or you can use BETWEEN operator of MySQL as Below:
SELECT * FROM table
WHERE your_date_field BETWEEN CURDATE() - INTERVAL 6 MONTH AND CURDATE();
Solution 4 - Mysql
I tried @user319198 answer to display last 6 months (sum of) sales, it worked but I faced one issue in the oldest month, i do not get the sales amount of the whole month. The result starts from the equivalent current day of that month.
Just I want to share my solution if any one interested:-
yourdate_column > DATE_SUB(now(), INTERVAL 7 MONTH)
limit 6
Also it will be great if anyone has better solution for my case J.
Solution 5 - Mysql
You can also use TIMESTAMPDIFF
TIMESTAMPDIFF(MONTH, your_date_column, now()) <= 6 )
Solution 6 - Mysql
To me, this looks like a solution as I'm using it with MariaDB, take a look at WHERE clause:
SELECT MONTH(yourTimestampOrDateColumn) AS MONTH, USER, ActionID, COUNT(*) AS TOTAL_ACTIONS, ROUND(SUM(totalpoints)) AS TOTAL_PTS
FROM MyTable
WHERE MONTH(yourTimestampOrDateColumn) BETWEEN MONTH(CURDATE() - INTERVAL 6 MONTH) AND MONTH(CURDATE())
GROUP BY MONTH;
On the image we see only months where user had actual data recorded in a DB (thus showing only 4 months instead of 6).
So this month is 10th (October), 6 months ago was 4th month (April), thus query will look for that interval (from 4 to 10).