Not able to get the correct rows returned with datediff

Question

I have 2 tables:

• pictures: picture_id, picture_date
• picture_user: pictured_id, user_id (user who posted the picture)

I want to get the users who have posted a picture in less than a year. I am getting the three columns (pid, userid, p_date) but some rows that have their p_date greater than 1 year are also returned.

SELECT p_picuser.pid,p_date,userid from p_pictures, p_picuser 
where p_pictures.pid=p_picuser.pid  
    AND (datediff(STR_TO_DATE(p_date, '%Y-%m-%d'),CURDATE())<365)

Answer 1

MySQL documentation states that datediff is date1 - date2. https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff

If you are comparing now to dates in the past (trying to find out how old something is), you need to subtract those dates from curdate() -- not the other way around.

See these examples from the documentation:

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
        -> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
        -> -31