Ottieni un elenco di date tra due date

Usando le funzioni standard di mysql c’è un modo per scrivere una query che restituirà un elenco di giorni tra due date.

Ad esempio, per il 2009-01-01 e il 2009-01-13, si restituirebbe una tabella di una colonna con i valori:

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 

Modifica: Sembra che non sia stato chiaro. Voglio GENERARE questa lista. Ho valori memorizzati nel database (per data / ora) ma voglio che vengano aggregati su un join esterno sinistro a un elenco di date come sopra (mi aspetto nulla dal lato destro di alcuni di questo join per alcuni giorni e gestirò questo ).

Vorrei utilizzare questa procedura memorizzata per generare gli intervalli necessari nella tabella temporanea denominata time_intervals , quindi unire e aggregare la tabella dei dati con la tabella temp_intervals temporanea.

La procedura può generare intervalli di tutti i diversi tipi che vedi specificati in esso:

 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; 

Esempio di scenario di dati di esempio nella parte inferiore di questo post , in cui ho creato una funzione simile per SQL Server.

Per MSSQL puoi usare questo. È MOLTO rapido.

È ansible racchiuderlo in una funzione valutata a livello di tabella o memorizzato proc e analizzare nelle date di inizio e di fine come variabili.

 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 

Puoi usare le variabili utente di MySQL in questo modo:

 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 perché lo aggiungi alla prima volta che lo usi. Inoltre, non puoi usare "HAVING date_sequence" perché questo fa aumentare la variabile dell'utente due volte per ogni riga.

Abbiamo avuto un problema simile con i report BIRT in quanto volevamo segnalare quei giorni in cui non c’erano dati. Poiché non c’erano voci per quelle date, la soluzione più semplice per noi era creare una tabella semplice che memorizzasse tutte le date e usasse quella per ottenere intervalli o join per ottenere valori zero per quella data.

Abbiamo un lavoro che viene eseguito ogni mese per garantire che la tabella venga popolata per 5 anni nel futuro. La tabella è così creata:

 create table all_dates ( dt date primary key ); 

Senza dubbio ci sono dei modi magici per farlo con diversi DBMS ‘ma optiamo sempre per la soluzione più semplice. I requisiti di archiviazione per la tabella sono minimi e rendono le query molto più semplici e portatili. Questo tipo di soluzione è quasi sempre migliore dal punto di vista delle prestazioni poiché non richiede calcoli per riga sui dati.

L’altra opzione (e l’abbiamo già usata prima) è assicurarsi che ci sia una voce nella tabella per ogni data. Abbiamo controllato periodicamente la tabella e aggiunto zero voci per date e / o orari che non esistevano. Questo potrebbe non essere un’opzione nel tuo caso, dipende dai dati memorizzati.

Se pensate davvero che sia una seccatura mantenere la tabella all_dates popolata, una procedura memorizzata è la strada da percorrere che restituirà un set di dati contenente tali date. Questo sarà quasi certamente più lento dal momento che devi calcolare l’intervallo ogni volta che viene chiamato piuttosto che tirare solo i dati precalcolati da una tabella.

Ma, ad essere onesti, è ansible compilare il tavolo per 1000 anni senza problemi di archiviazione dei dati gravi: 365.000 date di 16 byte (ad esempio) più un indice che duplica la data più il 20% di costi aggiuntivi per la sicurezza, stimerei approssimativamente a circa 14 milioni [365.000 * 16 * 2 * 1.2 = 14.016.000 byte]), una tabella minuscola nello schema delle cose.

Prendendo in prestito un’idea da questa risposta, puoi impostare una tabella da 0 a 9 e usarla per generare il tuo elenco di date.

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

Questo ti permetterà di generare un elenco di fino a 1000 date. Se devi aumentare, puoi aggiungere un altro cross join alla query interna.

Per accesso (o qualsiasi linguaggio SQL)

  1. Crea una tabella con 2 campi, chiameremo questa tabella tempRunDates :
    – Campi da fromDate e toDate
    – Quindi inserire solo 1 record, che ha la data di inizio e la data di fine.

  2. Crea un’altra tabella: Time_Day_Ref
    –Importa un elenco di date (fai una lista in Excel è facile) in questa tabella.
    –Il nome del campo nel mio caso è Greg_Dt , per data gregoriana
    – Ho creato la mia lista dal primo gennaio 2009 fino al 1 gennaio 2020.

  3. Esegui la query:

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

Facile!

Normalmente si userebbe una tabella di numeri ausiliari che di solito si aggira proprio per questo scopo con qualche variazione su questo:

 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 

Ho visto variazioni con funzioni con valori di tabella, ecc.

Puoi anche tenere un elenco permanente di date. Lo abbiamo nel nostro data warehouse e un elenco di orari del giorno.

Ecco come trovare le date tra due date date nel server SQL è spiegato su 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 

Lo abbiamo usato nel nostro sistema HRMS lo troverete utile

 SELECT CAST(DAYNAME(daydate) as CHAR) as dayname,daydate FROM (select CAST((date_add('20110101', interval Hi*100 + Ti*10 + Ui 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 Hi*100 + Ti*10 + Ui day ) <= '20110228' order by daydate ASC )Days 

Questa soluzione funziona con MySQL 5.0
Crea una tabella – mytable .
Lo schema non è materiale. Ciò che conta è il numero di righe al suo interno.
Quindi, puoi mantenere solo una colonna di tipo INT con 10 righe, valori da 1 a 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'; 

Limitazione: il numero massimo di date restituite dalla query precedente sarà
(rows in mytable)*(rows in mytable) = 10*10 = 100.

È ansible aumentare questo intervallo modificando la parte del modulo in sql:
da mytable x, mytable y, mytable z
Quindi, l'intervallo è 10*10*10 =1000 e così via.

Crea una stored procedure che prende due parametri a_begin e a_end. Crea una tabella temporanea al suo interno chiamata t, dichiara una variabile d, assegna a_begin a d, e avvia un ciclo WHILE INSERT ing d in t e chiama la funzione ADDDATE per incrementare il valore d. Infine SELECT * FROM t .

Vorrei usare qualcosa di simile a questo:

 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 

Quindi la tabella delle variabili @HOLDER contiene tutte le date incrementate di giorno tra quelle due date, pronte per essere aggiunte al contenuto del tuo cuore.

Ho combattuto con questo per un bel po ‘. Dato che questo è il primo hit su Google quando ho cercato la soluzione, lascia che postino fino a dove sono arrivato.

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

Sostituisci [yourTable] con una tabella dal tuo database. Il trucco è che il numero di righe nella tabella selezionata deve essere> = il numero di date che si desidera restituire. Ho provato a utilizzare il segnaposto DUAL della tabella, ma restituirebbe solo una singola riga.

 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 $$ 

questa procedura inserirà tutte le date dall’inizio dell’anno fino ad ora, sostituendo semplicemente i giorni di “inizio” e “fine”, e sei pronto per partire!

Avevo bisogno di una lista con tutti i mesi tra 2 date per le statistiche. Le 2 date sono l’inizio e la fine da un abbonamento. Quindi l’elenco mostra tutti i mesi e l’importo degli abbonamenti al mese.

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 @[email protected]; #- 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 

Sto usando la Server version: 5.7.11-log MySQL Community Server (GPL)

Ora risolveremo questo in un modo semplice.

Ho creato una tabella denominata “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) 

ora, wee vedrà i record inseriti all’interno.

 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) 

e qui la nostra query per recuperare i record entro due date piuttosto che quelle date.

 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) 

spero che questo possa aiutare molti.

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

Qui, per prima cosa aggiungi un giorno alla data di fine attuale, sarà 2011-02-28 00:00:00, quindi sottrai un secondo per rendere la data di fine 2011-02-27 23:59:59. In questo modo, è ansible ottenere tutte le date tra gli intervalli indicati.

produzione:
2011/02/25
2011/02/26
2011/02/27