Zeitbasierte Auswertungen mit SQL: Daily, Weekly, Monthly Reports

1. Täglich und monatlich gruppieren mit DATE_FORMAT

Die häufigste Aufgabe in zeitbasierten SQL-Auswertungen ist die Gruppierung nach Zeiteinheiten. DATE_FORMAT() ist dabei das zentrale Werkzeug. Die Formatstrings folgen der C-Datumsbibliothek und sind in MySQL konsistent einsetzbar.

Für eine SQL Zeitreihe muss die GROUP-BY-Spalte exakt dem Formatstring in DATE_FORMAT() entsprechen. Ein häufiger Fehler ist, im SELECT DATE_FORMAT(created_at, '%Y-%m') zu verwenden, aber im GROUP BY nur MONTH(created_at) – das führt zu fehlerhaften Gruppierungen.

-- Täglich: DATE() oder DATE_FORMAT mit '%Y-%m-%d'
SELECT
    DATE(created_at)                          AS tag,
    COUNT(*)                                   AS bestellungen,
    SUM(grand_total)                           AS umsatz
FROM sales_order
WHERE status = 'complete'
  AND created_at >= '2024-01-01'
  AND created_at <  '2025-01-01'
GROUP BY DATE(created_at)
ORDER BY tag;

-- Monatlich: DATE_FORMAT mit '%Y-%m'
SELECT
    DATE_FORMAT(created_at, '%Y-%m')          AS monat,
    COUNT(*)                                   AS bestellungen,
    ROUND(SUM(grand_total), 2)                 AS umsatz,
    ROUND(AVG(grand_total), 2)                 AS durchschnitt_bestellwert
FROM sales_order
WHERE status = 'complete'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY monat;
    

Wichtig: DATE_FORMAT(created_at, '%Y-%m') gibt einen String zurück, keine Zahl. Die Sortierung funktioniert trotzdem korrekt, weil das Format JJJJ-MM lexikografisch der chronologischen Reihenfolge entspricht. Das ist einer der Gründe, warum dieses Format dem reinen YEAR()/MONTH()-Ansatz vorzuziehen ist.

2. ISO-Wochen mit WEEK()-Modi

Die WEEK()-Funktion in MySQL hat einen entscheidenden Parameter: den Modus. Standardmässig (Modus 0) beginnt die Woche am Sonntag und eine Woche, die auf zwei Jahre aufgeteilt ist, gehört zum Jahr, in dem sie beginnt. ISO 8601 (Modus 3) definiert dagegen Montag als Wochenstart und ordnet die erste Woche des Jahres der Woche zu, die mindestens 4 Tage im neuen Jahr hat.

-- ISO-Wochennummer (Modus 3: Montag-Start, ISO 8601)
SELECT
    YEARWEEK(created_at, 3)                   AS iso_woche,
    -- Format: YYYYWW, z.B. 202401 = Woche 1 des Jahres 2024
    WEEK(created_at, 3)                        AS wochennummer,
    YEAR(created_at)                           AS jahr,
    COUNT(*)                                   AS bestellungen,
    SUM(grand_total)                           AS umsatz
FROM sales_order
WHERE status = 'complete'
  AND created_at >= '2024-01-01'
GROUP BY YEARWEEK(created_at, 3)
ORDER BY iso_woche;

-- Wochenmodus-Übersicht:
-- WEEK(date, 0): Sonntag-Start, Woche 1 = erste Sonntag-Woche
-- WEEK(date, 1): Montag-Start, Woche 1 = erste Woche mit > 3 Tagen im Jahr
-- WEEK(date, 3): ISO 8601 – Montag-Start, gebräuchlich in DE/EU
-- WEEK(date, 5): Montag-Start, Woche 0 = Vorjahr-Woche

-- Praxis-Tipp: YEARWEEK() statt YEAR() + WEEK() kombinieren
-- verhindert falsches Jahreswechsel-Verhalten
    

3. Datumslücken füllen mit Kalender-CTE

Ein häufiges Problem bei Zeitreihen-Abfragen: An Tagen ohne Bestellungen erscheinen diese Tage gar nicht im Ergebnis. Das macht Diagramme falsch und Reports irreführend. Die Lösung ist eine Kalendertabelle oder eine rekursive CTE, die alle Tage des gewünschten Zeitraums generiert.

-- Kalender-CTE: alle Tage eines Monats generieren (MySQL 8, rekursive CTE)
WITH RECURSIVE kalender AS (
    SELECT DATE('2024-01-01') AS tag
    UNION ALL
    SELECT tag + INTERVAL 1 DAY
    FROM kalender
    WHERE tag < DATE('2024-01-31')
)
SELECT
    k.tag,
    COALESCE(COUNT(o.entity_id), 0)           AS bestellungen,
    COALESCE(ROUND(SUM(o.grand_total), 2), 0)  AS umsatz
FROM kalender k
LEFT JOIN sales_order o
    ON DATE(o.created_at) = k.tag
    AND o.status = 'complete'
GROUP BY k.tag
ORDER BY k.tag;
    

-- Kalender-CTE für ganzes Jahr (parametrisierbar mit Variablen)
WITH RECURSIVE jahreskalender AS (
    SELECT DATE('2024-01-01') AS tag
    UNION ALL
    SELECT tag + INTERVAL 1 DAY
    FROM jahreskalender
    WHERE tag < DATE('2024-12-31')
)
SELECT
    DATE_FORMAT(k.tag, '%Y-%m')               AS monat,
    COUNT(DISTINCT DATE(k.tag))                AS tage_im_monat,
    COALESCE(COUNT(o.entity_id), 0)            AS bestellungen,
    COALESCE(SUM(o.grand_total), 0)            AS umsatz
FROM jahreskalender k
LEFT JOIN sales_order o
    ON DATE(o.created_at) = k.tag
    AND o.status = 'complete'
GROUP BY DATE_FORMAT(k.tag, '%Y-%m')
ORDER BY monat;
    

4. Timezone-Handling: CONVERT_TZ für Magento UTC

Magento 2 speichert alle Timestamps (created_at, updated_at) in UTC. Das ist korrekt und portabel. Aber für zeitbasierte Reports, die in lokaler Zeit dargestellt werden sollen, muss der Timestamp konvertiert werden – sonst werden Mitternachts-Bestellungen dem falschen Tag zugeordnet.

-- CONVERT_TZ: UTC nach Europe/Berlin (Sommer: +02:00, Winter: +01:00)
-- Wichtig: MySQL-Timezone-Tabellen müssen gefüllt sein (mysql_tzinfo_to_sql)
SELECT
    DATE(CONVERT_TZ(created_at, '+00:00', 'Europe/Berlin'))  AS tag_lokal,
    COUNT(*)                                                   AS bestellungen,
    SUM(grand_total)                                           AS umsatz
FROM sales_order
WHERE status = 'complete'
  AND created_at >= '2024-06-01'
  AND created_at <  '2024-07-01'
GROUP BY DATE(CONVERT_TZ(created_at, '+00:00', 'Europe/Berlin'))
ORDER BY tag_lokal;

-- Alternative: fester Offset (kein Timezone-Lookup, aber kein DST-Handling)
-- Im Sommer: UTC+2, im Winter: UTC+1
SELECT
    DATE(created_at + INTERVAL 2 HOUR) AS tag_sommer_utcplus2,
    COUNT(*) AS bestellungen
FROM sales_order
WHERE status = 'complete'
GROUP BY DATE(created_at + INTERVAL 2 HOUR);

-- Prüfen ob Timezone-Tabellen verfügbar sind:
SELECT COUNT(*) FROM mysql.time_zone_name;
-- Wenn 0: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
    

5. Geschäftsjahr-Gruppierung

Nicht alle Unternehmen verwenden das Kalenderjahr als Geschäftsjahr. Wenn das Geschäftsjahr zum Beispiel im Juli beginnt, muss die Jahreszuordnung entsprechend verschoben werden. Das lässt sich mit einer CASE-Logik elegant lösen.

-- Geschäftsjahr-Gruppierung (GJ beginnt im Juli)
-- Regel: Monate Jan-Jun gehören zum GJ des Vorjahres
--        Monate Jul-Dez gehören zum aktuellen GJ
SELECT
    CASE
        WHEN MONTH(created_at) < 7
            THEN CONCAT('GJ', YEAR(created_at) - 1, '/', YEAR(created_at))
        ELSE
            CONCAT('GJ', YEAR(created_at), '/', YEAR(created_at) + 1)
    END                                       AS geschaeftsjahr,
    DATE_FORMAT(created_at, '%Y-%m')           AS monat,
    SUM(grand_total)                           AS umsatz
FROM sales_order
WHERE status = 'complete'
GROUP BY
    CASE
        WHEN MONTH(created_at) < 7
            THEN CONCAT('GJ', YEAR(created_at) - 1, '/', YEAR(created_at))
        ELSE
            CONCAT('GJ', YEAR(created_at), '/', YEAR(created_at) + 1)
    END,
    DATE_FORMAT(created_at, '%Y-%m')
ORDER BY geschaeftsjahr, monat;
    

6. Rollierende 30-Tage-Fenster vs. Kalendermonat

Zwei konzeptionell verschiedene Zeitfenster-Typen werden oft verwechselt: Das rollierende 30-Tage-Fenster ("die letzten 30 Tage ab heute") und das Kalendermonat ("aktueller Monat von 1. bis 30./31."). Beide haben unterschiedliche Use-Cases und unterschiedliche SQL-Formulierungen.

-- Rollierendes 30-Tage-Fenster: immer "letzte 30 Tage ab CURRENT_DATE"
SELECT
    COUNT(*)         AS bestellungen_letzte_30_tage,
    SUM(grand_total) AS umsatz_letzte_30_tage
FROM sales_order
WHERE status = 'complete'
  AND created_at >= CURRENT_DATE - INTERVAL 30 DAY
  AND created_at <  CURRENT_DATE + INTERVAL 1 DAY;

-- Kalendermonat: 1. bis letzter Tag des aktuellen Monats
SELECT
    COUNT(*)         AS bestellungen_diesen_monat,
    SUM(grand_total) AS umsatz_diesen_monat
FROM sales_order
WHERE status = 'complete'
  AND created_at >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
  AND created_at <  DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') + INTERVAL 1 MONTH;

-- Rollierende 12 Monate pro Monat (Window Function)
SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS monat,
    SUM(grand_total) AS monatsumsatz,
    SUM(SUM(grand_total)) OVER (
        ORDER BY DATE_FORMAT(created_at, '%Y-%m')
        ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
    ) AS rolling_12m_umsatz
FROM sales_order
WHERE status = 'complete'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY monat;
    

7. Vorjahresvergleich mit JOIN und LAG

Vorjahresvergleiche sind in Reporting-Abfragen Standard. Es gibt zwei Haupt-Ansätze: einen Self-Join auf dieselbe Tabelle mit unterschiedlichem Datumsfilter, oder LAG() mit PARTITION BY month_of_year.

-- Vorjahresvergleich via Self-Join
SELECT
    a.monat,
    a.umsatz                                  AS umsatz_aktuell,
    v.umsatz                                  AS umsatz_vorjahr,
    ROUND(
        (a.umsatz - v.umsatz) / NULLIF(v.umsatz, 0) * 100,
        1
    )                                          AS wachstum_yoy_pct
FROM (
    SELECT DATE_FORMAT(created_at, '%m') AS monat,
           SUM(grand_total) AS umsatz
    FROM sales_order
    WHERE status = 'complete' AND YEAR(created_at) = 2024
    GROUP BY DATE_FORMAT(created_at, '%m')
) a
LEFT JOIN (
    SELECT DATE_FORMAT(created_at, '%m') AS monat,
           SUM(grand_total) AS umsatz
    FROM sales_order
    WHERE status = 'complete' AND YEAR(created_at) = 2023
    GROUP BY DATE_FORMAT(created_at, '%m')
) v ON v.monat = a.monat
ORDER BY a.monat;
    

-- Vorjahresvergleich via LAG() mit PARTITION BY Monatsnummer
SELECT
    DATE_FORMAT(created_at, '%Y-%m')          AS monat_str,
    MONTH(created_at)                          AS monatsnummer,
    YEAR(created_at)                           AS jahr,
    SUM(grand_total)                           AS umsatz,
    LAG(SUM(grand_total), 1, NULL) OVER (
        PARTITION BY MONTH(created_at)
        ORDER BY YEAR(created_at)
    )                                          AS umsatz_vorjahr,
    ROUND(
        (SUM(grand_total) - LAG(SUM(grand_total), 1, NULL) OVER (
            PARTITION BY MONTH(created_at)
            ORDER BY YEAR(created_at)
        )) / NULLIF(LAG(SUM(grand_total), 1, NULL) OVER (
            PARTITION BY MONTH(created_at)
            ORDER BY YEAR(created_at)
        ), 0) * 100,
        1
    )                                          AS yoy_wachstum_pct
FROM sales_order
WHERE status = 'complete'
  AND YEAR(created_at) IN (2023, 2024)
GROUP BY YEAR(created_at), MONTH(created_at)
ORDER BY jahr, monatsnummer;
    

8. Magento: created_at immer UTC

In Magento 2 werden alle created_at-Felder in sales_order, catalog_product_entity und anderen Tabellen in UTC gespeichert. Das ist eine wichtige Grundregel für alle zeitbasierten Auswertungen. Wer diesen Zusammenhang ignoriert, bekommt falsche Tages- und Monatsgrenzen – besonders in Regionen mit Zeitverschiebung oder Sommerzeit.

-- Magento: immer CONVERT_TZ für korrekte Tageszuordnung verwenden
-- Beispiel: Tagesreport für Deutschland (Europe/Berlin)

SELECT
    DATE(CONVERT_TZ(o.created_at, '+00:00', 'Europe/Berlin'))  AS tag_de,
    COUNT(*)                                                      AS bestellungen,
    SUM(o.grand_total)                                            AS umsatz,
    SUM(o.base_grand_total)                                       AS umsatz_base_currency
FROM sales_order o
WHERE o.status NOT IN ('canceled', 'closed')
  AND o.created_at >= CONVERT_TZ('2024-12-01 00:00:00', 'Europe/Berlin', '+00:00')
  AND o.created_at <  CONVERT_TZ('2025-01-01 00:00:00', 'Europe/Berlin', '+00:00')
GROUP BY DATE(CONVERT_TZ(o.created_at, '+00:00', 'Europe/Berlin'))
ORDER BY tag_de;
    

Ein häufiger Fehler: Der Datumsfilter wird in lokaler Zeit angegeben, ohne Konvertierung. Das führt dazu, dass UTC-Mitternachtsbestellungen (z.B. um 23:30 Uhr MEZ = 22:30 Uhr UTC) dem falschen Tag zugeordnet werden. Für konsistente Ergebnisse: Immer den Filter in UTC angeben und die Anzeige per CONVERT_TZ umrechnen.

10. Zusammenfassung

Zeitbasierte SQL-Auswertungen klingen simpel, haben aber viele Fallstricke. DATE_FORMAT() für konsistente Gruppierungen, WEEK(date, 3) für ISO 8601-Wochen, rekursive CTEs zum Füllen von Datumslücken und CONVERT_TZ() für Magento UTC – das sind die vier wichtigsten Werkzeuge für korrekte Zeitreihen-Queries.

11. FAQ: SQL Zeitreihe und zeitbasierte Reports