How to select last 6 months from news table using MySQL

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

Contents hide

Answers:

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.

Method 1

Use DATE_SUB

 .... where yourdate_column > DATE_SUB(now(), INTERVAL 6 MONTH)

Method 2

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.

Method 3

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();

Method 4

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.

Method 5

You can also use TIMESTAMPDIFF

    TIMESTAMPDIFF(MONTH, your_date_column, now()) <= 6 )

Method 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).

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating