SQL – Datum und Zeit – Tricks, Hacks & Tipps

SQL ist eine mächtige Sprache für das Management und die Abfrage von Datenbanken. Besonders wichtig und manchmal knifflig sind dabei Datums- und Zeitfunktionen. Dieser umfassende Leitfaden bietet eine Vielzahl an Tricks, Hacks und Tipps, um das Arbeiten mit Datum und Zeit in SQL zu erleichtern und effizienter zu gestalten.

Grundlagen der Datums- und Zeitfunktionen in SQL

Datentypen für Datum und Zeit

In SQL gibt es verschiedene Datentypen, um Datum und Zeit zu speichern, darunter:

• DATE: Speichert nur das Datum.
• TIME: Speichert nur die Zeit.
• TIMESTAMP: Speichert sowohl Datum als auch Zeit.

Jeder dieser Datentypen hat spezifische Anwendungsfälle und Funktionen, die ihre Handhabung erleichtern.

Aktuelles Datum und aktuelle Zeit

Um das aktuelle Datum und die aktuelle Zeit zu erhalten, verwenden Sie:

• SYSDATE (Oracle) oder CURRENT_TIMESTAMP (Standard-SQL): Gibt Datum und Uhrzeit des Systems zurück.

SELECT SYSDATE FROM DUAL; -- Oracle
SELECT CURRENT_TIMESTAMP; -- Standard-SQL

Häufig verwendete Datums- und Zeitfunktionen

Datum generieren

Um eine Reihe von aufeinanderfolgenden Datumswerten zu generieren, kann man die LEVEL-Funktion und CONNECT BY in Oracle SQL nutzen:

SELECT TRUNC(SYSDATE) - LEVEL + 1 AS vDate
FROM DUAL
CONNECT BY LEVEL <= 20
ORDER BY vDate;

Monatsdaten generieren

Um die ersten Tage der letzten 12 Monate zu generieren:

SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), (LEVEL - 1)*-1) AS vFirstDayOfLast12Months
FROM DUAL
CONNECT BY LEVEL <= 12
ORDER BY vFirstDayOfLast12Months;

Bestimmte Wochentage finden

Um alle Montage seit einem bestimmten Datum zu finden:

SELECT vdate, TO_CHAR(vdate, 'DY')
FROM (SELECT TO_DATE('01.01.2019', 'DD.MM.YYYY') + LEVEL - 1 AS vdate
      FROM DUAL
      CONNECT BY LEVEL <= (SYSDATE - TO_DATE('01.01.2019', 'DD.MM.YYYY') + 1))
WHERE TO_CHAR(vdate, 'DY') = 'MO';

Fortgeschrittene Techniken

Erster und letzter Tag eines Monats oder Jahres

Um den ersten Tag des aktuellen Monats zu erhalten:

SELECT TRUNC(SYSDATE, 'MONTH') AS vFirstDayOfMonth FROM DUAL;

Um den letzten Tag des Jahres zu erhalten:

SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 12) - 1 AS vLastDayOfYear FROM DUAL;

Anzahl der Tage in einem Monat

Um die Anzahl der Tage im aktuellen Monat zu erhalten:

SELECT CAST(TO_CHAR(LAST_DAY(SYSDATE), 'dd') AS INT) AS vNumberOfDaysInMonth FROM DUAL;

Differenz zwischen zwei Daten berechnen

Die Differenz in Tagen zwischen zwei Datumswerten lässt sich einfach berechnen:

SELECT DATE1 - DATE2 AS DaysDifference FROM DUAL;

Dabei sind DATE1 und DATE2 die Datumsfelder, zwischen denen die Differenz berechnet werden soll.

Tipps für den praktischen Einsatz

Zeitzonen berücksichtigen

Beim Arbeiten mit internationalen Datenbanken ist es wichtig, Zeitzonen zu berücksichtigen. SQL bietet Funktionen wie AT TIME ZONE, um die Zeitzone eines Datums- oder Zeitwerts zu ändern:

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC' AS UtcTime;

Datum und Zeit formatieren

Um Datums- und Zeitwerte in einem bestimmten Format anzuzeigen, kann die TO_CHAR-Funktion verwendet werden:

SELECT TO_CHAR(SYSDATE, 'DD.MM.YYYY HH24:MI:SS') AS FormattedDate FROM DUAL;

Weitere Beispiele zu SQL – Datum und Zeit – Tricks, Hacks & Tipps

-- Example to generate dates
SELECT vDate
FROM (SELECT LEVEL, TRUNC(SYSDATE) - LEVEL + 1 AS vDate
      FROM DUAL
      CONNECT BY LEVEL <= 20)
ORDER BY vDate;
 

-- Example to generate 12 Months
SELECT TO_CHAR(vDate, 'MONTH') vMonthValues
FROM (SELECT LEVEL, ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -LEVEL) AS vDate
      FROM DUAL
      CONNECT BY LEVEL <= 12 )
ORDER BY vDate;
 

-- Exmaple to generate month dates (first day of month)
SELECT vFirstDayOfLast12Months
FROM (SELECT LEVEL, ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), (LEVEL - 1)*-1) AS vDate
      FROM DUAL
      CONNECT BY LEVEL <= 20 )
ORDER BY vDate;
 

-- Exmaple to generate month dates (last day of month)
SELECT vLastDayOfLast12Months
FROM (SELECT LEVEL, ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), (LEVEL - 2)*-1)-1 AS vDate
      FROM DUAL
      CONNECT BY LEVEL <= 20 )
ORDER BY vDate;
 

-- Example to find MONDAY since 2019
SELECT vdate, TO_CHAR(vdate,'DY')
FROM ( SELECT TO_DATE('01.01.2019','DD.MM.YYYY')+LEVEL-1 vdate
      FROM DUAL
      CONNECT BY LEVEL <= ( SYSDATE - TO_DATE('01.01.2019','DD.MM.YYYY')+1 )
    )
WHERE TO_CHAR(vdate, 'DY') = 'MO';
 

-- Example to find first Monday in the year 2019
SELECT vdate, TO_CHAR(vdate,'DY')
FROM ( SELECT TO_DATE('01.01.2019','DD.MM.YYYY')+LEVEL-1 vdate
      FROM DUAL
      CONNECT BY LEVEL <= ( TO_DATE('31.12.2019','DD.MM.YYYY') - TO_DATE('01.01.2019','DD.MM.YYYY')+1 )
    )
WHERE TO_CHAR(vdate, 'DY') = 'MO'
ORDER BY vdate
FETCH NEXT 1 ROWS ONLY; -- same as LIMIT 1
 

-- Example to find last Monday in the year 2019
SELECT vdate, TO_CHAR(vdate,'DY')
FROM ( SELECT TO_DATE('01.01.2019','DD.MM.YYYY')+LEVEL-1 vdate
      FROM DUAL
      CONNECT BY LEVEL <= ( TO_DATE('31.12.2019','DD.MM.YYYY') - TO_DATE('01.01.2019','DD.MM.YYYY')+1 )
    )
WHERE TO_CHAR(vdate, 'DY') = 'MO'
ORDER BY vdate DESC
FETCH NEXT 1 ROWS ONLY; -- same as LIMIT 1
 

-- Example to get last day of the month
SELECT TRUNC(LAST_DAY(SYSDATE)) AS vLastDayOfMonth
FROM DUAL;
 

-- Example to first day of the month
SELECT TRUNC(SYSDATE, 'MONTH') AS vFirstDayOfMonth
FROM DUAL;
 

-- Example to get last day of the year
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 12) - 1 AS vLastDayOfYear
FROM DUAL;
 

-- Example to get the number of days in current month
SELECT CAST(TO_CHAR(LAST_DAY(SYSDATE), 'dd') AS INT) AS vNumberOfDaysInMonth
FROM DUAL;
 

-- Example to get the number of days per month
SELECT TO_CHAR(vdate, 'fmMONTH: ') || CAST(TO_CHAR(LAST_DAY(vDate), 'dd') AS INT) AS vNumberOfDaysInMonth
FROM (SELECT LEVEL, ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), LEVEL - 1) AS vDate
      FROM DUAL
      CONNECT BY LEVEL <= 12 )
ORDER BY vDate;
 

-- Example to get number of days left in current monnth
SELECT SYSDATE vToday, LAST_DAY(SYSDATE) AS vLastDayOfMonth, LAST_DAY(SYSDATE) - SYSDATE AS vDaysLeft
FROM DUAL;
 

-- Example to get number of days left in current year
SELECT SYSDATE vToday, ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12)-1 AS vLastDayOfYear, ROUND(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12)-1 - SYSDATE) AS vDaysLeft
FROM DUAL;
 

-- Example to get number of days between 2 dates
SELECT ROUND((MONTHS_BETWEEN('31.12.2020', '11.09.2020') * 30.5), 0) AS vNumOfDays -- approximation with 30.5 days/month
FROM DUAL;
 

-- Get the number of seconds since morning / from midnigt
SELECT (SYSDATE - TRUNC(SYSDATE)) * 24 * 60 * 60 AS vNumberSecondsSinceMorning
FROM DUAL;
 

-- Get the number of seconds till midnight / end of the day
SELECT (TRUNC(SYSDATE+1) - SYSDATE) * 24 * 60 * 60 AS VNumberSeccondsLeft
FROM DUAL;
 

Zusammenfassung SQL – Datum und Zeit – Tricks, Hacks & Tipps

Das Arbeiten mit Datum und Zeit in SQL kann komplex sein, bietet jedoch mächtige Werkzeuge zur Verwaltung und Analyse von Daten. Durch die Verwendung der richtigen Tricks und Tipps können viele Aufgaben effizienter und präziser durchgeführt werden. Regelmäßiges Üben und Experimentieren mit diesen Funktionen hilft, die eigenen SQL-Fähigkeiten zu verbessern und Probleme schneller zu lösen.

Mehr über SQL

Mehr zum Thema SQL findest du auf der Übersichtsseite über Datenbanken, SQL und PL/SQL sowie auf der Seite über SQL mit vielen Beispiel SQL Statements. Solltest du Formatierungen vornehmen wollen, dann kannst du dich im Beitrag über Formatierung von Nummern und Datumswerten in SQL informieren.