DanialartBiz DanialartBiz - 4 months ago 16
MySQL Question

How to select last 7 days results from mysql (with custom data field)?

I need to select data from mysql for last 7 days. I have field named 'date' and which have values in mm.dd.yy format.
So i tried to find special mysql request to do that, but its not work with my field, i gues that beacause date in wrong format.
How i can do that from php (use some variable to get mysql entries), or with custom select query ?

Answer

You can use STR_TO_DATE() to convert your idiosyncratic date format to a standard DATE value. An expression like this will do the trick

 STR_TO_DATE('07.17.97', '%m.%d.%y')

Then you can say

WHERE STR_TO_DATE(`date`, '%m.%d.%y') >= CURDATE() - INTERVAL 7 DAY

in your query to filter items with date values starting a week ago.

But, if you have a lot of rows to filter you will have poor performance: this kind of WHERE clause is not sargable.

Comments