Gruppo DateTime in intervalli di 5,15,30 e 60 minuti

Sto cercando di raggruppare alcuni record in intervalli di 5, 15, 30 e 60 minuti:

SELECT AVG(value) as "AvgValue", sample_date/(5*60) as "TimeFive" FROM DATA WHERE id = 123 AND sample_date >= 3/21/2012 

Voglio eseguire più query, ognuna delle quali raggrupperebbe i miei valori medi negli incrementi di tempo desiderati. Quindi la query di 5 minuti restituirebbe risultati come questo:

 AvgValue TimeFive 6.90 1995-01-01 00:05:00 7.15 1995-01-01 00:10:00 8.25 1995-01-01 00:15:00 

La query di 30 minuti risulterebbe in questo:

 AvgValue TimeThirty 6.95 1995-01-01 00:30:00 7.40 1995-01-01 01:00:00 

La colonna datetime è nel formato yyyy-mm-dd hh:mm:ss

Sto ottenendo errori di conversione impliciti della mia colonna datetime . Ogni aiuto è molto apprezzato!

utilizzando

 datediff(minute, '1990-01-01T00:00:00', yourDatetime) 

ti darà il numero di minuti dal 1990-1-1 (puoi usare la data base desiderata).

Quindi puoi dividere per 5, 15, 30 o 60 e raggruppare in base al risultato di questa divisione. L’ho tirato indietro sarà valutato come una divisione intera, quindi otterrai un numero intero che puoi utilizzare per raggruppare.

vale a dire

 group by datediff(minute, '1990-01-01T00:00:00', yourDatetime) /5 

AGGIORNAMENTO Poiché la domanda originale è stata modificata per richiedere la visualizzazione dei dati in formato data-ora dopo il raggruppamento, ho aggiunto questa semplice query che farà ciò che l’OP desidera:

 -- This convert the period to date-time format SELECT -- note the 5, the "minute", and the starting point to convert the -- period back to original time DATEADD(minute, AP.FiveMinutesPeriod * 5, '2010-01-01T00:00:00') AS Period, AP.AvgValue FROM -- this groups by the period and gets the average (SELECT P.FiveMinutesPeriod, AVG(P.Value) AS AvgValue FROM -- This calculates the period (five minutes in this instance) (SELECT -- note the division by 5 and the "minute" to build the 5 minute periods -- the '2010-01-01T00:00:00' is the starting point for the periods datediff(minute, '2010-01-01T00:00:00', T.Time)/5 AS FiveMinutesPeriod, T.Value FROM Test T) AS P GROUP BY P.FiveMinutesPeriod) AP 

NOTA: l’ho diviso in 3 sottoquery per chiarezza. Dovresti leggerlo dall’interno. Potrebbe, naturalmente, essere scritto come una query singola e compatta

NOTA: se si modifica il periodo e la data di inizio, è ansible ottenere qualsiasi intervallo necessario, ad esempio settimane a partire da un determinato giorno o qualsiasi altra cosa di cui si possa avere bisogno

Se si desidera generare dati di test per questa query, utilizzare questo:

 CREATE TABLE Test ( Id INT IDENTITY PRIMARY KEY, Time DATETIME, Value FLOAT) INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:00:22', 10) INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:03:22', 10) INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:04:45', 10) INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:07:21', 20) INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:10:25', 30) INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:11:22', 30) INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:14:47', 30) 

Il risultato dell’esecuzione della query è questo:

 Period AvgValue 2012-03-22 00:00:00.000 10 2012-03-22 00:05:00.000 20 2012-03-22 00:10:00.000 30 

Quindi, nel caso abbiate cercato su google, ma avete bisogno di farlo in mysql, che era il mio caso:

Puoi farlo in MySQL

 GROUP BY CONCAT( DATE_FORMAT(`timestamp`,'%m-%d-%Y %H:'), FLOOR(DATE_FORMAT(`timestamp`,'%i')/5)*5 ) 

Basandosi sulla risposta di @ JotaBe (a cui non posso commentare, altrimenti lo farei), potresti anche provare qualcosa di simile che non richiederebbe una sottoquery.

  SELECT AVG(value) AS 'AvgValue', -- Add the rounded seconds back onto epoch to get rounded time DATEADD( MINUTE, (DATEDIFF(MINUTE, '1990-01-01T00:00:00', your_date) / 30) * 30, '1990-01-01T00:00:00' ) AS 'TimeThirty' FROM YourTable -- WHERE your_date > some max lookback period GROUP BY (DATEDIFF(MINUTE, '1990-01-01T00:00:00', your_date) / 30) 

Questa modifica rimuove le tabelle temporanee e le sottoquery. Utilizza la stessa logica di base per il raggruppamento di intervalli di 30 minuti ma, quando si presentano i dati come parte del risultato, sto solo invertendo il calcolo dell’intervallo per ottenere la data e l’ora arrotondate.