Format curdate() in SELECT

Question

I am using the following to count new users over the last 10 days:

SELECT days.day, count(u.user_id)
FROM
(select curdate() as day
union select curdate() - interval 1 day
union select curdate() - interval 2 day
union select curdate() - interval 3 day
union select curdate() - interval 4 day
union select curdate() - interval 5 day
union select curdate() - interval 6 day
union select curdate() - interval 7 day
union select curdate() - interval 8 day
union select curdate() - interval 9 day) days
left join users u
on days.day = DATE(u.dateadded)
group by
days.day

Which works prefectly but gives date_format, as it should, 2014-03-18 - Ideally though, I want the date format to be e.g. 'Tues 18th Mar'

This is what I'm trying but only gives back 2 rows and they show null and BLOB under days column

SELECT days.day, count(u.user_id)
FROM
(select DATE_FORMAT(curdate(), '%a %D %b') as day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 1 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 2 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 3 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 4 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 5 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 6 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 7 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 8 day
union select DATE_FORMAT(curdate(), '%a %D %b') - interval 9 day) days
left join users u
on days.day = DATE_FORMAT(u.dateadded, '%a %D %b')
group by
days.day

Is there a better way to do this?

I'm using MySQL and PHP

Answer 1

The JOIN has to stay the same of course. No need to use DATE_FORMAT() there.

SELECT DATE_FORMAT(days.day, '%a %D %b') AS "day", count(u.user_id)
FROM
(select curdate() as day
union select curdate() - interval 1 day
union select curdate() - interval 2 day
union select curdate() - interval 3 day
union select curdate() - interval 4 day
union select curdate() - interval 5 day
union select curdate() - interval 6 day
union select curdate() - interval 7 day
union select curdate() - interval 8 day
union select curdate() - interval 9 day) days
left join users u
on days.day = DATE(u.dateadded)
group by
days.day

Answer 2

Try this option:

SELECT DATE_FORMAT(dateadded, '%a %D %b'), count(1)
  FROM users 
 WHERE dateadded > put_here_your_limit
 GROUP BY 1
 ORDER BY 1 DESC;

I hope it works fine for you.

Answer 3

Your problem was using the - interval on the result of date_format instead of the argument to it (resulting in a null, since after formatting, it didn't look like a valid date to subtract from - just one null result for all the union selects, because you didn't say union all). This would work:

select DATE_FORMAT(curdate(), '%a %D %b') as day
union select DATE_FORMAT(curdate() - interval 1 day , '%a %D %b')
union select DATE_FORMAT(curdate() - interval 2 day , '%a %D %b')
union select DATE_FORMAT(curdate() - interval 3 day , '%a %D %b')
union select DATE_FORMAT(curdate() - interval 4 day , '%a %D %b')
union select DATE_FORMAT(curdate() - interval 5 day , '%a %D %b')
union select DATE_FORMAT(curdate() - interval 6 day , '%a %D %b')
union select DATE_FORMAT(curdate() - interval 7 day , '%a %D %b')
union select DATE_FORMAT(curdate() - interval 8 day , '%a %D %b')
union select DATE_FORMAT(curdate() - interval 9 day , '%a %D %b')

but because your join against user would just be on e.g. 'Tue 18th Mar', you would get users added today but also users in other years, such as on Tuesday, March 18, 2008. I'm guessing that wasn't your intention.