Returning Rows from MySql less than 60 Days Old

php mysql sql datetime select

11,014

Solution 1

    $query="SELECT * FROM $tbl_name WHERE DATEDIFF(CURDATE(), STR_TO_DATE(date,'%d %M %Y')) <60 ORDER BY job_id DESC"

Solution 2

If date is stored like a varchar in database, your query should be:

SELECT * 
FROM $tbl_name 
WHERE TO_DATE(date, 'dd MON yyyy') >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
ORDER BY job_id DESC

if date is stored like a date, use:

SELECT * 
FROM $tbl_name 
WHERE date >= DATE_SUB(CURDATE(), INTERVAL 60 DAY)
ORDER BY job_id DESC

Solution 3

SELECT * FROM $tbl_name FROM_UNIXTIME(date) >= DATE_SUB(CURDATE(), INTERVAL 60 DAY) ORDER BY job_id DESC

11,014

Author by

Dan

Getting better. Still ask the simple questions though.

Updated on June 25, 2022

Comments

Dan almost 2 years
I have the following query returning all rows from my table:
```
$query="SELECT * FROM $tbl_name ORDER BY job_id DESC";
```
I'd like to limit those results by entries less than 60 days old. I record the date an entry was made to the database using:
```
$dt=date('d M Y');
```
And this is stored in a column called 'date'.

Can someone help me to modify my query?

Thanks Dan
Dan almost 13 years

Thanks but sadly this isn't working. It's not returning an error, but it isn't filtering results. Could it be because i'm storing the data in 'd M Y' format, when CURDATE is YYYY MM DD format?
Dan almost 13 years

Thanks but as above, sadly this isn't working. It's not returning an error, but it isn't filtering results. Could it be because i'm storing the data in 'd M Y' format, when CURDATE is YYYY MM DD format?
Daniel Gruszczyk almost 13 years

are you storing your date in a date field or varchar?
Daniel Gruszczyk almost 13 years

my last edit should work provided you store your date in the database as a string (in varchar or text field)
Dan almost 13 years

Hi Daniel, it is stored as a varchar and your edit works a treat. Thanks so much. Dan
Daniel Gruszczyk almost 13 years

You are welcome Dan. May I ask why do you store the date in varchar field?
Dan almost 13 years

Ignorance i suppose, i don't know any better (currently my third week of PHP MySQL development). I will have read a tutorial or some guidance somewhere, or perhaps it was because of how i wanted to display the date to the user, i.e. 'Posted on 03 Jul 2011'. What is the best way to store it? Thanks Dan
Daniel Gruszczyk almost 13 years

You should store dates in date field (datetime data type). That makes it much easier to manipulate, compare, order etc. And if you want to display it nicely for users, then you can format it easily in PHP.
Dan almost 13 years

@DanielGruszczyk let us continue this discussion in chat