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
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
.... where yourdate_column > DATE_SUB(now(), INTERVAL 6 MONTH)
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.
You can get last six month’s data by subtracting
interval of 6 month from
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();
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.
You can also use TIMESTAMPDIFF
TIMESTAMPDIFF(MONTH, your_date_column, now()) <= 6 )
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).