Downsampling, aber Intervallbereich definieren?

[martinw42]

Hallo Forumsgemeinde,

ich habe Messdaten mit Zeitstempel im Sekundentakt aufgezeichnet. Diese Daten haben jedoch Lücken, da eine Aufzeichnung nur während einer Messung anfällt. Ich möchte nun die Daten auswerten und muss dafür ein Downsampling umsetzen (also bspw. "stelle eine Wochen Daten dar, aber als Durchschnitt von 60 Minuten").

Bisher habe ich das wie folgt gemacht: (die Idee stammt hiervon)

# die 3600 bezieht sich auf die "1 Std = 3600 Sek" für das Downsampling Intervall
SELECT FROM_UNIXTIME((UNIX_TIMESTAMP(datetime) div (3600))*(3600)+(3600)) as FinalStamps,
round(avg(col1),1) as col1,
round(avg(col2),1) as col2,
round(avg(col3),1) as col3
FROM `myTable`
GROUP BY 1

Das klappt auch soweit ganz gut (vor allem ist es schnell).

Nun habe ich aber folgendes Problem, was ich an einem Beispiel illustrieren möchte:
Ich möchte einen 2-Stunden-Durchschnitt der Daten beginnend ab 00:30 Uhr. Dann kann ich das zwar über eine "WHERE datetime between [Start] and [Ende]" eingrenzen, aber mein erstes Intervall ist dann ein Durchschnitt für 00:00 bis 1:59, mein zweites Intervall ist dann von 2:00 bis 3:59 usw.

Ich benötige aber die Funktion, dass ich mein Intervall bei einer angegebenen Uhrzeit BEGINNEN lasse, und nicht (lediglich) angebe, welcher Bereich über meine Intervalle abgedeckt werden.

Hat da jemand eine gute Idee für mich?

Danke
Martin

 

[akretschmer]

PostgreSQL hat für sowas die Funktion date_bin():

[HEADING=2]9.9.3. date_bin[/HEADING]
    
The function date_bin “bins” the input timestamp into the specified interval (the stride) aligned with a specified origin.
   date_bin(stride, source, origin)

 
source is a value expression of type timestamp or timestamp with time zone. (Values of type date are cast automatically to timestamp.) stride is a value expression of type interval. The return value is likewise of type timestamp or timestamp with time zone, and it marks the beginning of the bin into which the source is placed.
   
Examples:
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
Result: 2020-02-11 15:30:00

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
Result: 2020-02-11 15:32:30

 
In the case of full units (1 minute, 1 hour, etc.), it gives the same result as the analogous date_trunc call, but the difference is that date_bin can truncate to an arbitrary interval.
   
The stride interval must be greater than zero and cannot contain units of month or larger.

Quelle: 9.9. Date/Time Functions and Operators

Vielleicht hat MySQL was vergleichbares.