mysql创建一个日期,在mysql中创建一个日期范围

Best way to create on the fly, date ranges, for use with report.

So I can avoid empty rows on my report if there's no activity for a given day.

解决方案

There is no straightforward way to do that in MySQL. Your best bet is to generate a daterange array in your server-side language of choice, and then pull data from the database and merge the resulting array with your daterange array using the date as a key.

Which server side language are you using?

Edit:

Basically what you would do is (pseudocode):

// Create an array with all dates for a given range

dates = makeRange(startDate, endDate);

getData = mysqlQuery('SELECT date, x, y, z FROM a WHERE a AND b AND c');

while (r = fetchRowArray(getData)) {

dates[ date(r['date']) ] = Array ( x, y, z);

}

You end up with an array of dates you can loop through, with the dates that have or don't have activity data associated to them.

Can easily be modified to group / filter data by hours.