1

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 ?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
DanialartBiz
  • 47
  • 5
  • use explode to make it proper format and then try again what have you tried before. – Niklesh Raut Jul 14 '16 at 10:39
  • it's a really poor practice to store your date as a varchar and in mm.dd.yy format. Stick to the convention and make it DATE or DATETIME format and store it in yyyy-mm-dd format. – Indrasis Datta Jul 14 '16 at 10:51
  • @ObjectManipulator how can i make date field in format which i need? And will it work with data manipulations in sql? – DanialartBiz Jul 14 '16 at 10:54
  • I'd suggest you to seect type as date in yy-mm-dd format. And every time you fetch it, you can format it as per your need(Maybe write a function to convert date in your desired format and call the function every time). This way, in future, if you need multiple date formats for different users, it will be easy to handle. – Indrasis Datta Jul 14 '16 at 10:57

2 Answers2

3

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.

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
-1

First read your table and change the date format

$new_date_format = date('Ymd',mktime(0,0,0,substr($date,0,2),substr($date,3,2),substr($date,6,2)));

After that you can make comparisions

Konstantinos Tsourdinis
  • 133
  • 5