Create a date range in 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.

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

My advice is: don't make your life harder, make it easier. Just create a table with one row for each calendar day, having as many rows as you think you reasonably need to last. In datawarehousing, this is the common solution, and it is so widely implemented this way that a dwh that doesn't have it, has a code smell.

Many people used to dealing with more traditional oltp/data entry apps feel a natural revulsion against this idea, because the feel the can generate the data anyway, and therefore it shouldn't be stored. But if you do create a table like that, you can adorn it with many useful attributes, such as whether it's a holdiday or a weekend, and you can store many common date representations (iso, european, us format etc) inside it, which can save you a ton of time when creating reports (since you don't have to bother figuring out how the date formatting works in each reporting tool you come by. Or you can go a step further and update your date table everyday to mark flags for the current day, current week, current month, current year, etc - all kinds of useful tools that make it much, much easier to build reports that need to work against some date range.

MySQL sample code as per request in comment:

delimiter //

DROP PROCEDURE IF EXISTS p_load_dim_date
//

CREATE PROCEDURE p_load_dim_date (
    p_from_date DATE
,   p_to_date   DATE
)
BEGIN
    DECLARE v_date DATE DEFAULT p_from_date;
    DECLARE v_month tinyint;
    CREATE TABLE IF NOT EXISTS dim_date (
        date_key               int          primary key
    ,   date_value             date
    ,   date_iso               char(10)
    ,   year                   smallint
    ,   quarter                tinyint
    ,   quarter_name           char(2)
    ,   month                  tinyint
    ,   month_name             varchar(10)
    ,   month_abbreviation     varchar(10)
    ,   week                   char(2)
    ,   day_of_month           tinyint
    ,   day_of_year            smallint
    ,   day_of_week            smallint
    ,   day_name               varchar(10)
    ,   day_abbreviation       varchar(10)
    ,   is_weekend             tinyint
    ,   is_weekday             tinyint
    ,   is_today               tinyint
    ,   is_yesterday           tinyint
    ,   is_this_week           tinyint
    ,   is_last_week           tinyint
    ,   is_this_month          tinyint
    ,   is_last_month          tinyint
    ,   is_this_year           tinyint
    ,   is_last_year           tinyint
    );
    WHILE v_date < p_to_date DO
        SET v_month := month(v_date);
        INSERT INTO dim_date(
            date_key
        ,   date_value
        ,   date_iso
        ,   year
        ,   quarter
        ,   quarter_name
        ,   month
        ,   month_name
        ,   month_abbreviation
        ,   week
        ,   day_of_month
        ,   day_of_year
        ,   day_of_week
        ,   day_name
        ,   day_abbreviation
        ,   is_weekend
        ,   is_weekday
        ) VALUES (
            v_date + 0
        ,   v_date
        ,   DATE_FORMAT(v_date, '%y-%c-%d')
        ,   year(v_date)
        ,   ((v_month - 1) DIV 3) + 1
        ,   CONCAT('Q', ((v_month - 1) DIV 3) + 1)
        ,   v_month
        ,   DATE_FORMAT(v_date, '%M')
        ,   DATE_FORMAT(v_date, '%b')
        ,   DATE_FORMAT(v_date, '%u')
        ,   DATE_FORMAT(v_date, '%d')
        ,   DATE_FORMAT(v_date, '%j')
        ,   DATE_FORMAT(v_date, '%w') + 1
        ,   DATE_FORMAT(v_date, '%W')
        ,   DATE_FORMAT(v_date, '%a')
        ,   IF(DATE_FORMAT(v_date, '%w') IN (0,6), 1, 0)
        ,   IF(DATE_FORMAT(v_date, '%w') IN (0,6), 0, 1)
        );
        SET v_date := v_date + INTERVAL 1 DAY;
    END WHILE;
    CALL p_update_dim_date();
END;
//

DROP PROCEDURE IF EXISTS p_update_dim_date;
//

CREATE PROCEDURE p_update_dim_date()
    UPDATE dim_date
    SET    is_today         = IF(date_value = current_date, 1, 0)
    ,      is_yesterday     = IF(date_value = current_date - INTERVAL 1 DAY, 1, 0)
    ,      is_this_week     = IF(year = year(current_date) AND week = DATE_FORMAT(current_date, '%u'), 1, 0)
    ,      is_last_week     = IF(year = year(current_date - INTERVAL 7 DAY) AND week = DATE_FORMAT(current_date - INTERVAL 7 DAY, '%u'), 1, 0)
    ,      is_this_month    = IF(year = year(current_date) AND month = month(current_date), 1, 0)
    ,      is_last_month    = IF(year = year(current_date - INTERVAL 1 MONTH) AND month = month(current_date - INTERVAL 1 MONTH), 1, 0)
    ,      is_this_year     = IF(year = year(current_date), 1, 0)
    ,      is_last_year     = IF(year = year(current_date - INTERVAL 1 YEAR), 1, 0)
    WHERE  is_today
    OR     is_yesterday
    OR     is_this_week
    OR     is_last_week
    OR     is_this_month
    OR     is_last_month
    OR     is_this_year
    OR     is_last_year
    OR     IF(date_value = current_date, 1, 0)
    OR     IF(date_value = current_date - INTERVAL 1 DAY, 1, 0)
    OR     IF(year = year(current_date) AND week = DATE_FORMAT(current_date, '%u'), 1, 0)
    OR     IF(year = year(current_date - INTERVAL 7 DAY) AND week = DATE_FORMAT(current_date - INTERVAL 7 DAY, '%u'), 1, 0)
    OR     IF(year = year(current_date) AND month = month(current_date), 1, 0)
    OR     IF(year = year(current_date - INTERVAL 1 MONTH) AND month = month(current_date - INTERVAL 1 MONTH), 1, 0)
    OR     IF(year = year(current_date), 1, 0)
    OR     IF(year = year(current_date - INTERVAL 1 YEAR), 1, 0)
    ;
//

delimiter ;

Using p_load_dim_date you uinitially load the dim_date table with say 25 years of data. And daily, prefereabluy round midnight, you run p_update_dim_date. Then you can use the flag fields is_today, is_yesterday, is_this_week, is_last_week and so on to select common ranges. Of course, you should amend this code to suit your particular needs but this is the idea. So no generaging ranges on the fly, you just preload for a long enough period of time ahead. For the time of day, a similar design can be set up - you should be able to manage that yourself going by this code.

For even fancier date dimensions that take care of holidays, and localized names for month and days, you can take a look at: http://rpbouman.blogspot.com/2007/04/kettle-tip-using-java-locales-for-date.html and http://rpbouman.blogspot.com/2010/01/easter-eggs-for-mysql-and-kettle.html

I've recently done some research to find and evaluate possible options. http://www.freeportmetrics.com/devblog/2012/11/02/how-to-quickly-add-date-dimension-to-pentaho-mondrian-olap-cube/.

You can use:

• kettle
• degenerated dimensions
• lucidb build-in function
• up-coming Mondrian built-in function
• your own custom script to generate SQL
• mysql script mentioned earlier

Please check the blog post for more details. It also contains improved version of Roland's sql script that will automatically calculate date range for given column and join it with date dimension.

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.

Try using a loop in a MySQL stored routine to create date ranges:

   declare iterDate date;
   set iterDate = startDate;

   DROP TABLE IF EXISTS MyDates;
   create temporary table MyDates (
      theDate date
   );

   label1: LOOP
     insert into MyDates(theDate) values (iterDate); 
     SET iterDate = DATE_ADD(iterDate, INTERVAL 1 DAY);
     IF iterDate <= endDate THEN
        ITERATE label1;
     END IF;
     LEAVE label1;
   END LOOP label1;

   select * from MyDates;
   DROP TABLE IF EXISTS MyDates;

startDate and endDate constitute the endpoints of the range and are supplied as parameters to the routine.

I realise this is an old post but, to keep Stack Overflow a bit up-to-date, I feel the urge to respond.

With the new SEQUENCE engine in MariaDB, this is possible within a SELECT statement without any stored routine or temporary table:

SELECT 
    DATE_ADD(
        CAST('2022-06-01' AS DATE), 
        INTERVAL `s1`.`seq` DAY
    ) AS `dates` 
FROM `seq_0_to_364` AS `s1`;

Any interval will work as long as it is within the limits of BIGINT(20) UNSIGNED, as this is the limit of the SEQUENCE engine.