Я бы использовал эту хранимую процедуру для генерации необходимых интервалов в таблице temp с именем time_intervals, а затем JOIN и агрегирования вашей таблицы данных с таблицей temp time_intervals.

Процедура может генерировать интервалы всех различных типов, которые вы видите в ней:

call make_intervals('2009-01-01 00:00:00','2009-01-10 00:00:00',1,'DAY')
.
select * from time_intervals  
.
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 23:59:59 
2009-01-02 00:00:00 2009-01-02 23:59:59 
2009-01-03 00:00:00 2009-01-03 23:59:59 
2009-01-04 00:00:00 2009-01-04 23:59:59 
2009-01-05 00:00:00 2009-01-05 23:59:59 
2009-01-06 00:00:00 2009-01-06 23:59:59 
2009-01-07 00:00:00 2009-01-07 23:59:59 
2009-01-08 00:00:00 2009-01-08 23:59:59 
2009-01-09 00:00:00 2009-01-09 23:59:59 
.
call make_intervals('2009-01-01 00:00:00','2009-01-01 02:00:00',10,'MINUTE')
. 
select * from time_intervals
.  
interval_start      interval_end        
------------------- ------------------- 
2009-01-01 00:00:00 2009-01-01 00:09:59 
2009-01-01 00:10:00 2009-01-01 00:19:59 
2009-01-01 00:20:00 2009-01-01 00:29:59 
2009-01-01 00:30:00 2009-01-01 00:39:59 
2009-01-01 00:40:00 2009-01-01 00:49:59 
2009-01-01 00:50:00 2009-01-01 00:59:59 
2009-01-01 01:00:00 2009-01-01 01:09:59 
2009-01-01 01:10:00 2009-01-01 01:19:59 
2009-01-01 01:20:00 2009-01-01 01:29:59 
2009-01-01 01:30:00 2009-01-01 01:39:59 
2009-01-01 01:40:00 2009-01-01 01:49:59 
2009-01-01 01:50:00 2009-01-01 01:59:59 
.
I specified an interval_start and interval_end so you can aggregate the 
data timestamps with a "between interval_start and interval_end" type of JOIN.
.
Code for the proc:
.
-- drop procedure make_intervals
.
CREATE PROCEDURE make_intervals(startdate timestamp, enddate timestamp, intval integer, unitval varchar(10))
BEGIN
-- *************************************************************************
-- Procedure: make_intervals()
--    Author: Ron Savage
--      Date: 02/03/2009
--
-- Description:
-- This procedure creates a temporary table named time_intervals with the
-- interval_start and interval_end fields specifed from the startdate and
-- enddate arguments, at intervals of intval (unitval) size.
-- *************************************************************************
   declare thisDate timestamp;
   declare nextDate timestamp;
   set thisDate = startdate;

   -- *************************************************************************
   -- Drop / create the temp table
   -- *************************************************************************
   drop temporary table if exists time_intervals;
   create temporary table if not exists time_intervals
      (
      interval_start timestamp,
      interval_end timestamp
      );

   -- *************************************************************************
   -- Loop through the startdate adding each intval interval until enddate
   -- *************************************************************************
   repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into time_intervals select thisDate, timestampadd(MICROSECOND, -1, nextDate);
      set thisDate = nextDate;
   until thisDate >= enddate
   end repeat;

 END;

Аналогичный пример сценария данных в нижней части этот пост, в котором я создал аналогичную функцию для SQL Server.

Для MSSQL вы можете использовать это. Это ОЧЕНЬ быстро.

Вы можете обернуть это в табличную функцию или сохраненную процедуру и проанализировать даты начала и окончания как переменные.

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '2011-01-01'
SET @endDate = '2011-01-31';

WITH dates(Date) AS 
(
    SELECT @startdate as Date
    UNION ALL
    SELECT DATEADD(d,1,[Date])
    FROM dates 
    WHERE DATE < @enddate
)

SELECT Date
FROM dates
OPTION (MAXRECURSION 0)
GO

Редактировать 2021/01 (д-р V): мне понравилось это решение, и я заставил его работать для mySQL V8. Вот код, превращающий его в процедуру:

DELIMITER //

CREATE PROCEDURE dates_between (IN from_date DATETIME,
                               IN to_date DATETIME) BEGIN
    WITH RECURSIVE dates(Date) AS
    (
        SELECT from_date as Date
        UNION ALL
        SELECT DATE_ADD(Date, INTERVAL 1 day) FROM dates WHERE Date < to_date
    )
    SELECT DATE(Date) FROM dates;
END//

DELIMITER ;

Вы можете использовать пользовательские переменные в MySQL следующим образом:

SET @num = -1;
SELECT DATE_ADD( '2009-01-01', interval @num := @num+1 day) AS date_sequence, 
your_table.* FROM your_table
WHERE your_table.other_column IS NOT NULL
HAVING DATE_ADD('2009-01-01', interval @num day) <= '2009-01-13'

@num равен -1, потому что вы добавляете к нему при первом использовании. Кроме того, вы не можете использовать «HAVING date_sequence», потому что при этом пользовательская переменная увеличивается вдвое для каждой строки.

У нас была аналогичная проблема с отчетами BIRT, поскольку мы хотели отчитываться в те дни, когда не было данных. Поскольку для этих дат не было записей, самым простым решением для нас было создать простую таблицу, в которой хранятся все даты, и использовать ее для получения диапазонов или объединения для получения нулевых значений для этой даты.

У нас есть задание, которое выполняется каждый месяц, чтобы обеспечить заполнение таблицы на 5 лет вперед. Таблица создается таким образом:

create table all_dates (
    dt date primary key
);

Несомненно, есть волшебные хитрые способы сделать это с помощью разных СУБД », но мы всегда выбираем самое простое решение. Требования к хранилищу для таблицы минимальны, что делает запросы намного проще и переносимыми. Такое решение почти всегда лучше с точки зрения производительности, поскольку не требует вычислений для каждой строки данных.

Другой вариант (и мы использовали его раньше) - обеспечить наличие записи в таблице для каждой даты. Мы периодически просматривали таблицу и добавляли нулевые записи для дат и / или времени, которых не было. Это может быть не вариант в вашем случае, это зависит от сохраненных данных.

Если вы действительно считаете, что поддерживать all_dates таблицу заполненной сложно, лучше всего использовать хранимую процедуру, которая вернет набор данных, содержащий эти даты. Это почти наверняка будет медленнее, поскольку вам придется вычислять диапазон каждый раз, когда он вызывается, а не просто извлекать предварительно рассчитанные данные из таблицы.

Но, честно говоря, вы могли бы заполнить таблицу в течение 1000 лет без каких-либо серьезных проблем с хранением данных - 365000 16-байтовых (например) дат плюс индекс, дублирующий дату плюс 20% накладных расходов для безопасности, я бы приблизительно оценил в около 14M [365 000 * 16 * 2 * 1,2 = 14 016 000 байт]), крохотная таблица в схеме вещей.

Заимствование идеи из этот ответ, вы можете создать таблицу с цифрами от 0 до 9 и использовать ее для создания списка дат.

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

select adddate('2009-01-01', numlist.id) as `date` from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2009-01-01', numlist.id) <= '2009-01-13';

Это позволит вам создать список до 1000 дат. Если вам нужно сделать больше, вы можете добавить еще одно перекрестное соединение во внутренний запрос.

Для доступа (или любого языка SQL)

1. Создайте одну таблицу с 2 полями, мы назовем эту таблицу tempRunDates:
   --Fields fromDate и toDate
   - Затем вставьте только 1 запись, у которой есть дата начала и дата окончания.

2. Создайте еще одну таблицу: Time_Day_Ref
   --Импортируйте список дат (создать список в Excel легко) в эту таблицу.
   - Имя поля в моем случае Greg_Dt, для григорианской даты
   - Я составлял свой список с 1 января 2009 года по 1 января 2020 года.

3. Запустите запрос:

   SELECT Time_Day_Ref.GREG_DT
   FROM tempRunDates, Time_Day_Ref
   WHERE Time_Day_Ref.greg_dt>=tempRunDates.fromDate And greg_dt<=tempRunDates.toDate;
   

Легкий!

Элегантное решение, использующее новую рекурсивную функциональность (общие табличные выражения) в MariaDB> = 10.3 и MySQL> = 8.0.

WITH RECURSIVE t as (
    select '2019-01-01' as dt
  UNION
    SELECT DATE_ADD(t.dt, INTERVAL 1 DAY) FROM t WHERE DATE_ADD(t.dt, INTERVAL 1 DAY) <= '2019-04-30'
)
select * FROM t;

Приведенное выше возвращает таблицу дат между «2019-01-01» и «2019-04-30».

Обычно для этой цели можно использовать вспомогательную таблицу чисел с некоторыми вариациями:

SELECT *
FROM (
    SELECT DATEADD(d, number - 1, '2009-01-01') AS dt
    FROM Numbers
    WHERE number BETWEEN 1 AND DATEDIFF(d, '2009-01-01', '2009-01-13') + 1
) AS DateRange
LEFT JOIN YourStuff
    ON DateRange.dt = YourStuff.DateColumn

Я видел варианты с табличными функциями и т. Д.

Вы также можете вести постоянный список свиданий. У нас есть это в нашем хранилище данных, а также список времени суток.

Мы использовали это в нашей системе управления персоналом, вы найдете это полезным

SELECT CAST(DAYNAME(daydate) as CHAR) as dayname,daydate
    FROM
    (select CAST((date_add('20110101', interval H.i*100 + T.i*10 + U.i day) )as DATE) as daydate
      from erp_integers as H
    cross
      join erp_integers as T
    cross
      join erp_integers as U
     where date_add('20110101', interval H.i*100 + T.i*10 + U.i day ) <= '20110228'
    order
        by daydate ASC
        )Days

Это решение работает с MySQL 5.0.
Создайте таблицу - mytable.
Схема не имеет значения. Важно количество строк в нем.
Итак, вы можете сохранить только один столбец типа INT с 10 строками, значениями от 1 до 10.

SQL:

set @tempDate=date('2011-07-01') - interval 1 day;
select
date(@tempDate := (date(@tempDate) + interval 1 day)) as theDate
from mytable x,mytable y
group by theDate
having theDate <= '2011-07-31';

Ограничение: максимальное количество дат, возвращаемых вышеуказанным запросом, будет
(rows in mytable)*(rows in mytable) = 10*10 = 100.

Вы можете увеличить этот диапазон, изменив часть формы в sql:
с mytable x, mytable y, mytable z
Итак, диапазон будет 10*10*10 =1000 и так далее.

Вы можете использовать это

SELECT CAST(cal.date_list AS DATE) day_year
FROM (
  SELECT SUBDATE('2019-01-01', INTERVAL 1 YEAR) + INTERVAL xc DAY AS date_list
  FROM (
        SELECT @xi:=@xi+1 as xc from
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc1,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc3,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc4,
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc5,
        (SELECT @xi:=-1) xc0
    ) xxc1
) cal
WHERE cal.date_list BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY cal.date_list DESC;

SET @s := '2020-01-01';
SET @e := @s + INTERVAL 1 YEAR - INTERVAL 1 DAY; -- set end date to select

SELECT CAST((DATE(@s)+INTERVAL (H+T+U) DAY) AS DATE) d -- generate a list of 400 days starting from @s date so @e can not be more then @s + 
FROM ( SELECT 0 H
    UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
  ) H CROSS JOIN ( SELECT 0 T
    UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
    UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
    UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
  ) T CROSS JOIN ( SELECT 0 U
    UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
    UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
    UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
  ) U
 -- generated lenght of date list can be calculated as fallow 1 * H(3 + 1) * T(9 + 1) * U(9 + 1) = 400
 -- it is crucial to preserve the numbering convention as 1, 2 ... 20, 30..., 100, 200, ... to retrieve chronological date selection
 -- add more UNION 400, 500, 600, ... H(6 + 1) if you want to select from more than 700 days!
WHERE
  (DATE(@s+INTERVAL (H+T+U) DAY)) <= DATE((@e))
ORDER BY d;

SELECT 1 * (3 + 1) * (9 + 1) * (9 + 1); -- 400 days starting from from @s

На основе этой статьи < br> Протестировано и работает должным образом

Без временной таблицы, без процедуры, без функций

SET @s := '2021-12-01'; -- start at
SET @e := @s + INTERVAL 1 MONTH - INTERVAL 1 DAY; -- 2021-12-31

WITH recursive d_range AS (
    select @s as Date
   union all
   select Date + interval 1 day
   from d_range
   where Date < @e)
select * from d_range;

Результат:

| Date       |
+------------+
| 2021-12-01 |
| 2021-12-02 |
| 2021-12-03 |
| 2021-12-04 |
| 2021-12-05 |
| 2021-12-06 |
| 2021-12-07 |
| 2021-12-08 |
| 2021-12-09 |
| 2021-12-10 |
| 2021-12-11 |
| 2021-12-12 |
| 2021-12-13 |
| 2021-12-14 |
| 2021-12-15 |
| 2021-12-16 |
| 2021-12-17 |
| 2021-12-18 |
| 2021-12-19 |
| 2021-12-20 |
| 2021-12-21 |
| 2021-12-22 |
| 2021-12-23 |
| 2021-12-24 |
| 2021-12-25 |
| 2021-12-26 |
| 2021-12-27 |
| 2021-12-28 |
| 2021-12-29 |
| 2021-12-30 |
| 2021-12-31 |
+------------+
-> 31 rows in set (0.037 sec)

Примечание. этот запрос выполняется довольно медленно. А при выборе более длинных диапазонов дат он станет еще медленнее.

Я использую Server version: 5.7.11-log MySQL Community Server (GPL)

Теперь решим это простым способом.

Я создал таблицу с названием "datetable"

mysql> describe datetable;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| colid   | int(11) | NO   | PRI | NULL    |       |
| coldate | date    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

теперь мы увидим вставленные записи внутри.

mysql> select * from datetable;
+-------+------------+
| colid | coldate    |
+-------+------------+
|   101 | 2015-01-01 |
|   102 | 2015-05-01 |
|   103 | 2016-01-01 |
+-------+------------+
3 rows in set (0.00 sec)

и здесь наш запрос на выборку записей за две даты, а не за эти даты.

mysql> select * from datetable where coldate > '2015-01-01' and coldate < '2016-01-01';
+-------+------------+
| colid | coldate    |
+-------+------------+
|   102 | 2015-05-01 |
+-------+------------+
1 row in set (0.00 sec)

надеюсь, что это поможет многим.