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

最新推荐文章于 2024-07-30 03:57:19 发布

转载最新推荐文章于 2024-07-30 03:57:19 发布 · 317 阅读

文章标签：

#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.