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

asked Jan 20 '12 at 12:18

Zabs

5,51638127219

3

if the field is really called datetime you'll need to use ` to surround it. datetime is a reserved word. Pablo's response would work xcept where he has your_dt_field you may need to put datetime with the proper escape ` character.

– xQbert
Jan 20 '12 at 12:27

add a comment |

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

asked Jan 20 '12 at 12:18

Zabs

5,51638127219

3

if the field is really called datetime you'll need to use ` to surround it. datetime is a reserved word. Pablo's response would work xcept where he has your_dt_field you may need to put datetime with the proper escape ` character.

– xQbert
Jan 20 '12 at 12:27

add a comment |

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

asked Jan 20 '12 at 12:18

Zabs

5,51638127219

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 sql

asked Jan 20 '12 at 12:18

Zabs

5,51638127219

asked Jan 20 '12 at 12:18

Zabs

5,51638127219

asked Jan 20 '12 at 12:18

Zabs

5,51638127219

asked Jan 20 '12 at 12:18

Zabs

5,51638127219

asked Jan 20 '12 at 12:18

Zabs

5,51638127219

3

if the field is really called datetime you'll need to use ` to surround it. datetime is a reserved word. Pablo's response would work xcept where he has your_dt_field you may need to put datetime with the proper escape ` character.

– xQbert
Jan 20 '12 at 12:27

add a comment |

3

if the field is really called datetime you'll need to use ` to surround it. datetime is a reserved word. Pablo's response would work xcept where he has your_dt_field you may need to put datetime with the proper escape ` character.

– xQbert
Jan 20 '12 at 12:27

if the field is really called datetime you'll need to use ` to surround it. datetime is a reserved word. Pablo's response would work xcept where he has your_dt_field you may need to put datetime with the proper escape ` character.

– xQbert
Jan 20 '12 at 12:27

add a comment |

6 Answers
6

active

oldest

votes

Use DATE_SUB

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

answered Jan 20 '12 at 14:11

user319198

DATE_SUB() function subtracts the interval from the given date. In this case "now() - 6 months".

– prageeth
Jul 11 '12 at 13:08

Inside DATE_SUB() function, Can i also use MAX(<date_column>) instead of now()??? Because i'm facing a similar situation and i have to check the last 6 months data from the last date present in the date column instead of today's date.

– LearneR
Nov 17 '15 at 0:45

add a comment |

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.

answered Jan 20 '12 at 12:25

Pablo Santa Cruz

134k25199251

your_dt_field what will be it's format ? also how can use there systems current date

– Ashish Karpe
Jul 5 '16 at 11:35

add a comment |

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.

answered Jun 28 '17 at 12:29

Bufaroosha

235

add a comment |

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;

queryResults

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

answered Oct 3 '18 at 19:19

stamster

558614

add a comment |

You can also use TIMESTAMPDIFF

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

answered Oct 30 '18 at 6:32

Md. Mahmud Hasan

478214

add a comment |

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

answered Nov 14 '18 at 13:21

Haritsinh Gohil

668411

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f8941377%2fhow-to-select-last-6-months-from-news-table-using-mysql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

6 Answers
6

active

oldest

votes

6 Answers
6

active

oldest

votes

Use DATE_SUB

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

answered Jan 20 '12 at 14:11

user319198

DATE_SUB() function subtracts the interval from the given date. In this case "now() - 6 months".

– prageeth
Jul 11 '12 at 13:08

Inside DATE_SUB() function, Can i also use MAX(<date_column>) instead of now()??? Because i'm facing a similar situation and i have to check the last 6 months data from the last date present in the date column instead of today's date.

– LearneR
Nov 17 '15 at 0:45

add a comment |

Use DATE_SUB

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

answered Jan 20 '12 at 14:11

user319198

DATE_SUB() function subtracts the interval from the given date. In this case "now() - 6 months".

– prageeth
Jul 11 '12 at 13:08

Inside DATE_SUB() function, Can i also use MAX(<date_column>) instead of now()??? Because i'm facing a similar situation and i have to check the last 6 months data from the last date present in the date column instead of today's date.

– LearneR
Nov 17 '15 at 0:45

add a comment |

Use DATE_SUB

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

answered Jan 20 '12 at 14:11

user319198

Use DATE_SUB

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

answered Jan 20 '12 at 14:11

user319198

answered Jan 20 '12 at 14:11

user319198

answered Jan 20 '12 at 14:11

user319198

answered Jan 20 '12 at 14:11

user319198

DATE_SUB() function subtracts the interval from the given date. In this case "now() - 6 months".

– prageeth
Jul 11 '12 at 13:08

Inside DATE_SUB() function, Can i also use MAX(<date_column>) instead of now()??? Because i'm facing a similar situation and i have to check the last 6 months data from the last date present in the date column instead of today's date.

– LearneR
Nov 17 '15 at 0:45

add a comment |

DATE_SUB() function subtracts the interval from the given date. In this case "now() - 6 months".

– prageeth
Jul 11 '12 at 13:08

Inside DATE_SUB() function, Can i also use MAX(<date_column>) instead of now()??? Because i'm facing a similar situation and i have to check the last 6 months data from the last date present in the date column instead of today's date.

– LearneR
Nov 17 '15 at 0:45

DATE_SUB() function subtracts the interval from the given date. In this case "now() - 6 months".

– prageeth
Jul 11 '12 at 13:08

Inside DATE_SUB() function, Can i also use MAX(<date_column>) instead of now()??? Because i'm facing a similar situation and i have to check the last 6 months data from the last date present in the date column instead of today's date.

– LearneR
Nov 17 '15 at 0:45

add a comment |

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.

answered Jan 20 '12 at 12:25

Pablo Santa Cruz

134k25199251

your_dt_field what will be it's format ? also how can use there systems current date

– Ashish Karpe
Jul 5 '16 at 11:35

add a comment |

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.

answered Jan 20 '12 at 12:25

Pablo Santa Cruz

134k25199251

your_dt_field what will be it's format ? also how can use there systems current date

– Ashish Karpe
Jul 5 '16 at 11:35

add a comment |

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.

answered Jan 20 '12 at 12:25

Pablo Santa Cruz

134k25199251

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.

answered Jan 20 '12 at 12:25

Pablo Santa Cruz

134k25199251

answered Jan 20 '12 at 12:25

Pablo Santa Cruz

134k25199251

answered Jan 20 '12 at 12:25

Pablo Santa Cruz

134k25199251

answered Jan 20 '12 at 12:25

Pablo Santa Cruz

134k25199251

your_dt_field what will be it's format ? also how can use there systems current date

– Ashish Karpe
Jul 5 '16 at 11:35

add a comment |

your_dt_field what will be it's format ? also how can use there systems current date

– Ashish Karpe
Jul 5 '16 at 11:35

your_dt_field what will be it's format ? also how can use there systems current date

– Ashish Karpe
Jul 5 '16 at 11:35

add a comment |

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.

answered Jun 28 '17 at 12:29

Bufaroosha

235

add a comment |

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.

answered Jun 28 '17 at 12:29

Bufaroosha

235

add a comment |

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.

answered Jun 28 '17 at 12:29

Bufaroosha

235

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.

answered Jun 28 '17 at 12:29

Bufaroosha

235

answered Jun 28 '17 at 12:29

Bufaroosha

235

answered Jun 28 '17 at 12:29

Bufaroosha

235

answered Jun 28 '17 at 12:29

Bufaroosha

235

add a comment |

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;

queryResults

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

answered Oct 3 '18 at 19:19

stamster

558614

add a comment |

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;

queryResults

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

answered Oct 3 '18 at 19:19

stamster

558614

add a comment |

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;

queryResults

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

answered Oct 3 '18 at 19:19

stamster

558614

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;

queryResults

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

answered Oct 3 '18 at 19:19

stamster

558614

answered Oct 3 '18 at 19:19

stamster

558614

answered Oct 3 '18 at 19:19

stamster

558614

answered Oct 3 '18 at 19:19

stamster

558614

add a comment |

You can also use TIMESTAMPDIFF

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

answered Oct 30 '18 at 6:32

Md. Mahmud Hasan

478214

add a comment |

You can also use TIMESTAMPDIFF

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

answered Oct 30 '18 at 6:32

Md. Mahmud Hasan

478214

add a comment |

You can also use TIMESTAMPDIFF

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

answered Oct 30 '18 at 6:32

Md. Mahmud Hasan

478214

You can also use TIMESTAMPDIFF

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

answered Oct 30 '18 at 6:32

Md. Mahmud Hasan

478214

answered Oct 30 '18 at 6:32

Md. Mahmud Hasan

478214

answered Oct 30 '18 at 6:32

Md. Mahmud Hasan

478214

answered Oct 30 '18 at 6:32

Md. Mahmud Hasan

478214

add a comment |

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

answered Nov 14 '18 at 13:21

Haritsinh Gohil

668411

add a comment |

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

answered Nov 14 '18 at 13:21

Haritsinh Gohil

668411

add a comment |

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

answered Nov 14 '18 at 13:21

Haritsinh Gohil

668411

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

answered Nov 14 '18 at 13:21

Haritsinh Gohil

668411

answered Nov 14 '18 at 13:21

Haritsinh Gohil

668411

answered Nov 14 '18 at 13:21

Haritsinh Gohil

668411

answered Nov 14 '18 at 13:21

Haritsinh Gohil

668411

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky