Hacken met datumfuncties in SQLite

Enkele van de handigste functies in Postgres-implementaties van SQL (zoals Amazon Redshift) zijn DATE_DIFF en DATE_TRUNC:

• DATE_DIFF geeft de hoeveelheid tijd die is verstreken tussen twee verschillende datums. De volgende code geeft bijvoorbeeld het aantal dagen tussen date1 en date2:

DATE_DIFF('day', date1, date2)
Mogelijk gemaakt door

DATE_DIFF is ideaal om het aantal dagen van aanmelding tot opzegging te berekenen, of het aantal uren van inloggen tot uitloggen.

• DATE_TRUNC rondt een datum af naar de dichtstbijzijnde dag, week, maand of jaar. De volgende code geeft bijvoorbeeld de dichtstbijzijnde maandag bij de timestamp my_timestamp:

DATE_TRUNC('week', my_timestamp)
Mogelijk gemaakt door

DATE_TRUNC is geweldig voor het aggregeren van data. Zo kunnen we het gebruiken om het aantal Maandelijks Actieve Gebruikers (MAU) te vinden door af te kappen naar de dichtstbijzijnde maandstart.

Maar niet elke SQL-implementatie heeft deze handige functies. Voor onze Learn SQL from Scratch-code gebruiken we SQLite, een lichte SQL-implementatie die op een enkele Docker-instance kan draaien. SQLite is geweldig voor website-backends en kleine projecten, maar mist mijn twee favoriete functies. Gelukkig zijn er workarounds.

Om DATE_DIFF te emuleren, kunnen we een minder bekende functie gebruiken, genaamd juliandate. Volgens Wikipedia is “het Julian Day Number (JDN) het geheel getal dat wordt toegekend aan een volledige zonnedag in de Juliaanse dagtelling, die begint om 12 uur ’s middags Universal Time, waarbij Juliaanse dag 0 is toegekend aan de dag die begint om 12 uur ’s middags op maandag 1 januari 4713 v.Chr.”. Door een datum om te zetten naar een zwevendekommagetal, kunnen we met aftrekken het verschil tussen twee tijdstempels vinden.

We kunnen ons antwoord zelfs omrekenen naar uren door te vermenigvuldigen met 24 of naar minuten door te vermenigvuldigen met 24 * 60.

We kunnen een deel van de functionaliteit van DATE_TRUNC emuleren met strftime. Deze functie zet een timestamp om naar een string met een opgegeven uitvoer.

%ddag van de maand: 00 %ffractieseconden: SS.SSS %Huur: 00-24%jdag van het jaar: 001-366 %JJuliaanse dagnummer%mmaand: 01-12 %Mminuut: 00-59 %sseconden sinds 1970-01-01 %Sseconden: 00-59 %wdag van de week 0-6 met zondag==0 %Wweek van het jaar: 00-53 %Jjaar: 0000-9999

Normaal gebruiken we dit om tussen verschillende timestamp-formaten te converteren, zoals JJJJ-MM-DD naar MM-DD-JJJJ:

strftime('%M-%D-%Y', mydate)
Mogelijk gemaakt door

Maar we kunnen slim kiezen welke formattering we gebruiken om af te kappen op het juiste punt. Als we bijvoorbeeld naar de dichtstbijzijnde maand willen afkappen, kunnen we:

strftime('%M/%Y', mydate)
Mogelijk gemaakt door

Of we kappen af naar de dichtstbijzijnde week met:

strftime('%Y-%w', mydate)
Mogelijk gemaakt door

Met deze twee simpele trucs kun je met SQLite soortgelijke analyses doen als met Amazon Redshift!

Wil je meer leren over de basis van SQL? Volg dan DataCamps cursus Intro to SQL for Data Science en bekijk onze SQL-tutorial voor beginners.