MySQL Order By Date -

i have mysql database. database contains documents datetime column called "created". want group day in order have document count per day. however, days have 0 documents , result not part of output. example need '2001-01-01' have 0 count if documents not exist.

i thinking of creating table date range interested on , outer join table. can group date have results.

is there better way of doing such thing?

my sql code:

select date(created_at),c.text dates d left outer join classifier c on d.n=date(c.created_at)     c.classifier="2014streamlrall"     , date(c.created_at)>='2014-03-01' , date(c.created_at)<='2014-05-01' order d.n; 

the left join still not work.

there no better way in mysql.

it lacks both method generate arbitrary length resultset (similar postgresql's generate_series) , recursive sql required emulate such method (which used in sql server , oracle).

even on sql server, populating , keeping table 100 years worth of dates (which takes little more 73k records) gives better performance on reports similar yours using generated resultset.