在mysql中创建一个日期范围 [英] Create a date range in mysql

问题描述

所以我可以避免在我的报告上的空行，如果没有给定的活动一天。

主要是为了避免这个问题：

解决方案

在MySQL中没有直接的方法。您最好的选择是在您选择的服务器端语言中生成一个daterange数组，然后从数据库中提取数据，并使用日期作为关键字将结果数组与daterange数组合并。

您正在使用哪种服务器端语言？

编辑：

基本上你会do是（伪代码）：

  //创建一个给定范围的所有日期的数组
 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）; 
 
} 
  

最终你可以循环的日期数组，具有或没有与之相关联的活动数据的日期。

可以轻松修改，按小时对数据进行分组/过滤。

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.

Mostly to avoid this issue: What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

解决方案

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.

这篇关于在mysql中创建一个日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！