Uzyskaj listę dat między dwiema datami

Korzystanie ze standardowych funkcji mysql umożliwia napisanie zapytania, które zwróci listę dni między dwiema datami.

np. dane 2009-01-01 i 2009-01-13 zwróci jedną kolumnową tabelę z wartościami:

 2009-01-01 
 2009-01-02 
 2009-01-03
 2009-01-04 
 2009-01-05
 2009-01-06
 2009-01-07
 2009-01-08 
 2009-01-09
 2009-01-10
 2009-01-11
 2009-01-12
 2009-01-13

Edycja: Wygląda na to, że nie było jasne. Chcę WYGENEROWAĆ tę listę. Mam wartości przechowywane w bazie danych (według daty i godziny), ale chcę, aby były zagregowane na lewym sprzężeniu zewnętrznym do listy dat jak powyżej (oczekuję wartości null po prawej stronie niektórych z tego sprzężenia przez kilka dni i poradzę sobie z tym ).

Użyłbym tej procedury składowanej do wygenerowania potrzebnych interwałów w tabeli tymczasowej o nazwie time_intervals , a następnie DOŁĄCZ i zagreguj tabelę danych z tabelą temp time_intervals .

Procedura może generować interwały wszystkich różnych typów, które w niej widzisz:

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;

Podobny przykładowy scenariusz danych na dole tego postu , w którym zbudowałem podobną funkcję dla SQL Server.

W przypadku MSSQL możesz tego użyć. To jest BARDZO szybkie.

Możesz zawrzeć to w funkcji wartościowanej w tabeli lub zapisać proces i przeanalizować datę początkową i końcową jako zmienne.

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

Mieliśmy podobny problem z raportami BIRT, ponieważ chcieliśmy raportować te dni, w których nie było danych. Ponieważ nie było wpisów dla tych dat, najłatwiejszym rozwiązaniem dla nas było utworzenie prostej tabeli przechowującej wszystkie daty i użycie jej do uzyskania zakresów lub łączenia, aby uzyskać zerowe wartości dla tej daty.

Mamy zadanie, które jest uruchamiane co miesiąc, aby zapewnić zapełnienie tabeli za 5 lat w przyszłość. Tabela jest tworzona w ten sposób:

create table all_dates (
    dt date primary key
);

Bez wątpienia istnieją magiczne, trudne sposoby na zrobienie tego z różnymi DBMS, ale zawsze wybieramy najprostsze rozwiązanie. Wymagania dotyczące pamięci dla tabeli są minimalne, co sprawia, że ​​zapytania są o wiele prostsze i przenośne. Tego rodzaju rozwiązanie jest prawie zawsze lepsze z punktu widzenia wydajności, ponieważ nie wymaga obliczeń danych dla każdego wiersza.

Inną opcją (i używaliśmy tego wcześniej) jest zapewnienie wpisu w tabeli dla każdej daty. Okresowo przeglądaliśmy tabelę i dodawaliśmy zero wpisów dla dat i / lub godzin, które nie istniały. W twoim przypadku może to nie być opcja, zależy to od przechowywanych danych.

Jeśli naprawdę uważasz, że utrzymywanie all_dateswypełnienia tabeli jest kłopotliwe , najlepszym rozwiązaniem jest procedura składowana, która zwróci zestaw danych zawierający te daty. Będzie to prawie na pewno wolniejsze, ponieważ musisz obliczyć zakres za każdym razem, gdy jest wywoływany, zamiast po prostu pobierać wstępnie obliczone dane z tabeli.

Ale szczerze mówiąc, możesz wypełniać tabelę przez 1000 lat bez poważnych problemów z przechowywaniem danych - 365 000 16-bajtowych (na przykład) dat plus indeks powielający datę plus 20% narzut dla bezpieczeństwa, szacuję z grubsza na około 14 M [365 000 * 16 * 2 * 1,2 = 14 016 000 bajtów]), maleńka tabela w schemacie rzeczy.

Możesz użyć zmiennych użytkownika MySQL w następujący sposób:

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 to -1, ponieważ dodajesz do niego przy pierwszym użyciu. Nie możesz też użyć „HAVING date_sequence”, ponieważ powoduje to dwukrotny przyrost zmiennej użytkownika dla każdego wiersza.

Pożyczając pomysł z tej odpowiedzi, możesz ustawić tabelę od 0 do 9 i użyć jej do wygenerowania listy dat.

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';

Umożliwi to wygenerowanie listy maksymalnie 1000 dat. Jeśli chcesz zwiększyć rozmiar, możesz dodać kolejne sprzężenie krzyżowe do zapytania wewnętrznego.

Access (lub dowolny język SQL)

1. Utwórz jedną tabelę, która ma 2 pola, nazwiemy tę tabelę tempRunDates:
   --Pola fromDatei - Następnie toDate
   wstaw tylko 1 rekord, który ma datę początkową i końcową.

2. Utwórz kolejną tabelę: - Time_Day_Ref
   Importuj listę dat (lista w programie Excel jest łatwa) do tej tabeli.
   - Nazwa pola w moim przypadku to Greg_Dtdata gregoriańska -
   sporządziłem listę od 1 stycznia 2009 do 1 stycznia 2020.

3. Uruchom zapytanie:

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

Łatwy!

Zwykle można użyć pomocniczej tabeli liczb, którą zwykle przechowujesz tylko w tym celu, z pewnymi odmianami:

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

Widziałem odmiany z funkcjami o wartościach tabelarycznych itp.

Możesz także zachować stałą listę dat. Mamy to w naszej hurtowni danych, a także listę pór dnia.

Jak znaleźć daty między dwiema podanymi datami na serwerze SQL, wyjaśniono na http://ektaraval.blogspot.com/2010/09/writing-recursive-query-to-find-out-all.html

CREATE FUNCTION [dbo].[_DATES]
(
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS 
@DATES TABLE(
    DATE1 DATETIME
)
AS
BEGIN
    WHILE @startDate <= @endDate
    BEGIN 
        INSERT INTO @DATES (DATE1)
            SELECT @startDate   
    SELECT @startDate = DATEADD(d,1,@startDate) 
    END
RETURN
END

Eleganckie rozwiązanie wykorzystujące nową funkcję rekurencyjną (Common Table Expressions) w MariaDB> = 10.3 i 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;

Powyższe zwraca tabelę dat od „2019-01-01” do „2019-04-30”.

Wykorzystaliśmy to w naszym systemie HRMS, uznasz to za przydatne

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

To rozwiązanie współpracuje z MySQL 5.0
Utwórz tabelę - mytable.
Schemat nie ma znaczenia. Liczy się liczba wierszy w nim.
Możesz więc zachować tylko jedną kolumnę typu INT z 10 wierszami, wartościami - od 1 do 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';

Ograniczenie: maksymalna liczba dat zwróconych przez powyższe zapytanie będzie wynosić
(rows in mytable)*(rows in mytable) = 10*10 = 100.

Możesz zwiększyć ten zakres, zmieniając część formularza w sql:
z mytable x, mytable y, mytable z
Tak więc zakres be 10*10*10 =1000i tak dalej.

Utwórz procedurę składowaną, która przyjmuje dwa parametry a_begin i a_end. Utwórz w nim tymczasową tabelę o nazwie t, zadeklaruj zmienną d, przypisz a_begin do d i uruchom WHILEpętlę INSERTd do t i wywołaj ADDDATEfunkcję, aby zwiększyć wartość d. Wreszcie SELECT * FROM t.

Użyłbym czegoś podobnego do tego:

DECLARE @DATEFROM AS DATETIME
DECLARE @DATETO AS DATETIME
DECLARE @HOLDER TABLE(DATE DATETIME)

SET @DATEFROM = '2010-08-10'
SET @DATETO = '2010-09-11'

INSERT INTO
    @HOLDER
        (DATE)
VALUES
    (@DATEFROM)

WHILE @DATEFROM < @DATETO
BEGIN

    SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
    INSERT 
    INTO
        @HOLDER
            (DATE)
    VALUES
        (@DATEFROM)
END

SELECT 
    DATE
FROM
    @HOLDER

Następnie @HOLDERtabela Variable zawiera wszystkie daty powiększone o dzień między tymi dwoma datami, gotowe do połączenia się z zadowoleniem.

Walczę z tym już od dłuższego czasu. Ponieważ jest to pierwszy hit w Google, kiedy szukałem rozwiązania, pozwól mi opublikować, gdzie dotarłem do tej pory.

SET @d := '2011-09-01';
SELECT @d AS d, cast( @d := DATE_ADD( @d , INTERVAL 1 DAY ) AS DATE ) AS new_d
  FROM [yourTable]
  WHERE @d <= '2012-05-01';

Zastąp [yourTable]tabelą z bazy danych. Sztuczka polega na tym, że liczba wierszy w wybranej tabeli musi wynosić> = liczba dat, które chcesz zwrócić. Próbowałem użyć symbolu zastępczego tabeli DUAL, ale zwróciłby tylko jeden wiersz.

select * from table_name where col_Date between '2011/02/25' AND DATEADD(s,-1,DATEADD(d,1,'2011/02/27'))

Tutaj najpierw dodaj dzień do bieżącej daty zakończenia, będzie to 2011-02-28 00:00:00, a następnie odejmij jedną sekundę, aby data zakończenia 2011-02-27 23:59:59. W ten sposób możesz uzyskać wszystkie daty między podanymi interwałami.

wyjście:
2011/02/25
2011/02/26
2011/02/27

DELIMITER $$  
CREATE PROCEDURE popula_calendario_controle()
   BEGIN
      DECLARE a INT Default 0;
      DECLARE first_day_of_year DATE;
      set first_day_of_year = CONCAT(DATE_FORMAT(curdate(),'%Y'),'-01-01');
      one_by_one: LOOP
         IF dayofweek(adddate(first_day_of_year,a)) <> 1 THEN
            INSERT INTO calendario.controle VALUES(null,150,adddate(first_day_of_year,a),adddate(first_day_of_year,a),1);
         END IF;
         SET a=a+1;
         IF a=365 THEN
            LEAVE one_by_one;
         END IF;
      END LOOP one_by_one;
END $$

ta procedura wstawi wszystkie daty od początku roku do teraz, po prostu zastąp dni „początku” i „końca” i jesteś gotowy do pracy!

Potrzebowałem listy zawierającej wszystkie miesiące między 2 datami do statystyk. Dwie daty to data rozpoczęcia i data zakończenia subskrypcji. Tak więc lista pokazuje wszystkie miesiące i liczbę subskrypcji miesięcznie.

MYSQL

CREATE PROCEDURE `get_amount_subscription_per_month`()
BEGIN
   -- Select the ultimate start and enddate from subscribers
   select @startdate := min(DATE_FORMAT(a.startdate, "%Y-%m-01")), 
          @enddate := max(DATE_FORMAT(a.enddate, "%Y-%m-01")) + interval 1 MONTH
   from subscription a;

   -- Tmp table with all months (dates), you can always format them with DATE_FORMAT) 
   DROP TABLE IF EXISTS tmp_months;
   create temporary table tmp_months (
      year_month date,
      PRIMARY KEY (year_month)
   );


   set @tempDate=@startdate;  #- interval 1 MONTH;

   -- Insert every month in tmp table
   WHILE @tempDate <= @enddate DO
     insert into tmp_months (year_month) values (@tempDate);
     set @tempDate = (date(@tempDate) + interval 1 MONTH);
   END WHILE;

   -- All months
   select year_month from tmp_months;

   -- If you want the amount of subscription per month else leave it out
   select mnd.year_month, sum(subscription.amount) as subscription_amount
   from tmp_months mnd
   LEFT JOIN subscription ON mnd.year_month >= DATE_FORMAT(subscription.startdate, "%Y-%m-01") and mnd.year_month <= DATE_FORMAT(subscription.enddate, "%Y-%m-01")
   GROUP BY mnd.year_month;

 END

Możesz tego użyć

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;

ja używam Server version: 5.7.11-log MySQL Community Server (GPL)

Teraz rozwiążemy to w prosty sposób.

Utworzyłem tabelę o nazwie „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)

teraz zobaczymy wstawione rekordy wewnątrz.

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)

i tutaj nasze zapytanie, aby pobrać rekordy z dwóch dat, a nie z tych dat.

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)

mam nadzieję, że to pomoże wielu osobom.