awesome-robot

MySQL Fuzzy Dates

So I was in a discussion in a how-to forum I like to frequent to both learn new stuff and help others when it was proposed that MySQL couldn't do "fuzzy dates." I felt I had to prove them wrong, as any good internet debater inevitably ends up doing. Fuzzy dates are when you see blog posts or forum posts and they say stuff like "Posted 5 days ago" or "Last updated 3 years ago."  So, how can it be done?  Here you go:

SELECT 
    CASE 
        WHEN DATEDIFF(NOW(),`posted_date`) <1  THEN  'today')
        WHEN DATEDIFF(NOW(),`posted_date`) =1  THEN  '1 day')
        WHEN DATEDIFF(NOW(),`posted_date`)<=31 THEN  CONCAT(DATEDIFF(NOW(),`posted_date`),' days')
        WHEN DATEDIFF(NOW(),`posted_date`) BETWEEN 31 AND 60 THEN '1 month')
        WHEN DATEDIFF(NOW(),`posted_date`) BETWEEN 61 AND 365 THEN  CONCAT(ROUND(DATEDIFF(NOW(),`posted_date`)/31),' months')
        WHEN DATEDIFF(NOW(),`posted_date`) BETWEEN 365 AND 730 THEN '1 year')
        WHEN DATEDIFF(NOW(),`posted_date`)>730 THEN  CONCAT(ROUND(DATEDIFF(NOW(),`posted_date`)/356),' years')
    END `fuzzy_date`,
    `article_id`,
    `anything_else`
FROM 
    `articles_table`  ORDER BY `posted_date` DESC LIMIT 25

Basically what we're doing is subtracting the "posted date" of the article from today's date. For that, we're using MySQL's built in "NOW()" function.  We're manually clumping the various date ranges in to make them more audience friendly.  If you know of a better way to do this, by all means, post here and let me know.  Also, if you've found this helpful and have found a way to modify it to fit your application, let us know!