Date-Funktionen in SQLite clever nutzen

Einige der nützlichsten Funktionen in Postgres-Implementierungen von SQL (wie Amazon Redshift) sind DATE_DIFF und DATE_TRUNC:

• DATE_DIFF liefert die verstrichene Zeit zwischen zwei Datumswerten. Zum Beispiel gibt der folgende Code die Anzahl der Tage zwischen date1 und date2 zurück:

DATE_DIFF('day', date1, date2)
Bereitgestellt von

DATE_DIFF ist ideal, um etwa die Anzahl der Tage von Anmeldung bis Kündigung oder die Stunden von Login bis Logout zu berechnen.

• DATE_TRUNC schneidet ein Datum auf den nächsten Tag, die Woche, den Monat oder das Jahr zu. Zum Beispiel gibt der folgende Code den Montag der betreffenden Woche für den Zeitstempel my_timestamp zurück:

DATE_TRUNC('week', my_timestamp)
Bereitgestellt von

DATE_TRUNC eignet sich hervorragend zum Aggregieren von Daten. So lässt sich damit etwa die Zahl der Monthly Active Users (MAU) ermitteln, indem auf den Monatsanfang gekürzt wird.

Aber nicht jede SQL-Implementierung hat diese praktischen Funktionen. Für unseren Learn SQL-from-Scratch-Code nutzen wir SQLite, eine schlanke SQL-Implementierung, die auf einer einzelnen Docker-Instanz laufen kann. SQLite ist großartig für Website-Backends und kleine Projekte, aber es fehlen meine zwei Lieblingsfunktionen. Zum Glück gibt es Workarounds.

Um DATE_DIFF zu emulieren, können wir eine wenig bekannte Funktion namens juliandate verwenden. Laut Wikipedia ist „die Julianische Tageszahl (JDN) die ganze Zahl, die einem vollen Sonnentag in der Zählung der Julianischen Tage zugeordnet ist und um 12:00 Uhr Weltzeit beginnt, wobei der Julianischen Tageszahl 0 der Tag zugeordnet ist, der am Montag, dem 1. Januar 4713 v. Chr., um 12:00 Uhr beginnt“. Indem wir ein Datum in eine Fließkommazahl umwandeln, können wir per Subtraktion die Differenz zwischen zwei Zeitstempeln berechnen.

Wir können das Ergebnis sogar in Stunden umrechnen, indem wir mit 24 multiplizieren, oder in Minuten mit 24 * 60.

Einen Teil der Funktionalität von DATE_TRUNC können wir mit strftime nachbilden. Diese Funktion konvertiert einen Zeitstempel in eine Zeichenkette mit einem gewünschten Format.

%dTag des Monats: 00 %fDezimalstellen der Sekunde: SS.SSS %HStunde: 00-24 %jTag des Jahres: 001-366 %JJulianische Tageszahl %mMonat: 01-12 %MMinute: 00-59 %sSekunden seit 1970-01-01 %SSekunden: 00-59 %wWochentag 0-6, Sonntag==0 %WWoche des Jahres: 00-53 %JJahr: 0000-9999

Normalerweise nutzt man das, um zwischen Zeitstempelformaten wie YYYY-MM-DD und MM-DD-YYYY zu wechseln:

strftime('%M-%D-%Y', mydate)
Bereitgestellt von

Wir können das Format aber auch so wählen, dass es auf die gewünschte Granularität kürzt. Wenn wir zum Beispiel auf den Monat kürzen möchten:

strftime('%M/%Y', mydate)
Bereitgestellt von

Oder wir kürzen auf die Woche, indem wir Folgendes verwenden:

strftime('%Y-%w', mydate)
Bereitgestellt von

Mit diesen zwei einfachen Tricks kannst du mit SQLite viele der gleichen Analysen durchführen wie mit Amazon Redshift!

Wenn du mehr über die Grundlagen von SQL lernen möchtest, besuche DataCamps Kurs Intro to SQL for Data Science und wirf einen Blick auf unser SQL Tutorial for Beginners.