Mode Blog

tijdstempels zijn om een zeer eenvoudige reden cruciaal voor zakelijke analyse: ze vertellen je wanneer dingen gebeuren. Stel je voor dat je probeert trends in je gegevens uit te vinden, zoals maandelijks webverkeer, kwartaalwinsten of dagelijks ordervolume zonder te weten wanneer er zich gebeurtenissen hebben voorgedaan. Het zou een nachtmerrie zijn.

PostgreSQL biedt een verscheidenheid aan datumfuncties voor het manipuleren van tijdstempels. Om het nuttige van het obscure te scheiden, delen we how-tos voor de meest gebruikte Postgres-datumfuncties en bedrijfsscenario ‘ s waar ze van pas komen.,

De meest gebruikte Postgres-datumfuncties en bedrijfsscenario ‘ s waar ze van pas komen:

• tijdstempels afronden met DATE_TRUNC
• gebeurtenissen vinden met betrekking tot de huidige tijd met NOW() en CURRENT_DATE
• uur-van-dag en dag-van-week isoleren met EXTRACT
• verstreken tijd berekenen met leeftijd

we hebben de gegevens voor elk voorbeeld beschikbaar gemaakt in de mode public warehouse. Probeer elke datumfunctie in de modus terwijl u zich door deze voorbeelden heen werkt. Meld u aan voor een account in Mode Studio en open een nieuw rapport om te beginnen.,

afronding van tijdstempels met DATE_TRUNC

de DATE_TRUNC functie rondt een tijdstempelwaarde af naar een opgegeven interval, waarmee u gebeurtenissen kunt tellen. U kunt rond een tijdsstempel naar de volgende eenheden van tijd:

• microseconde
• milliseconde
• tweede
• minuten
• uur
• dag
• week
• maand
• wijk
• jaar
• decennium
• eeuw
• millenium

De DATE_TRUNC syntax ziet er als volgt uit: DATE_TRUNC('interval',timestamp).,

bijvoorbeeld, SELECT DATE_TRUNC('day','2015-04-12 14:44:18') zou een resultaat geven van 2015-04-12 00:00:00.

voor een meer gedetailleerde uitleg van DATE_TRUNC (en een afdrukbare referentie die u op uw bureau kunt bewaren!), check out dit bericht.

Hoe is het webverkeer in de loop van de tijd veranderd?

probeer DATE_TRUNC voor uzelf door de tabel modeanalytics te bevragen.web_events, dat voorbeeldrecords van websitebezoeken bevat, inclusief eenoccurred_at kolom. U kunt de maand van het bezoek isoleren met DATE_TRUNC.,

om een aantal webbezoeken per maand per kanaal te retourneren, voegt u de kolom channel en een COUNT toe aan de instructie SELECT en groepeert u vervolgens met month en channel. (Aangezien maand en kanaal de eerste twee waarden zijn in uwSELECT statement, kunt u GROUP BY 1,2), als volgt:

ten slotte gebruikt u ORDER BY 1,2 om uw resultaten chronologisch (per maand) en alfabetisch (per kanaal) te organiseren.,

in modus kunt u een lijndiagram maken om de resultaten van de query te visualiseren.

Mode Analytics

het vinden van gebeurtenissen relatief aan de huidige tijd met NOW () en CURRENT_DATE

de NOW() date functie geeft de huidige tijdstempel in UTC (als de tijdzone niet gespecificeerd is). U kunt intervallen aftrekken van NOW() om gebeurtenissen te Pullen die plaatsvonden in het laatste uur, de laatste dag, de laatste week, enz.

draaien SELECT NOW() om 9:00am UTC op 11 oktober 2016 zou resulteren in 2016-10-11 09:00:00.,

de functie CURRENT_DATE geeft alleen de huidige datum terug, niet de hele tijdstempel. Het draaien van SELECT CURRENT_DATE om 9:00am UTC op 11 oktober 2016 zou 2016-10-11teruggeven.

welke bestellingen zijn in de afgelopen 12 uur geplaatst?

de demo van de tabel.orders bevat voorbeeldrecords van alle orders, inclusief een occurred_at tijdstempelkolom in UTC.,

om orders te vinden die in de laatste 12 uur zijn geplaatst, gebruikt u eenWHERE clausule om alleen orders te retourneren die zijn geplaatst na of precies op (>=) de huidige tijdstempel (NOW()) minus een interval van 12 uur.,

SELECT * FROM demo.orders WHERE occurred_at >= NOW() - interval '12 hour'

Modus Analytics

naast hour, kunt u gebruik maken één van de volgende tijdstippen:

• ms
• milliseconden
• tweede
• minuten
• uur
• dag
• week
• maand
• jaar
• decennium
• eeuw
• millennium

U kunt ook het combineren van verschillende tijdstippen in dezelfde expressie als volgt uit:
interval '4 hours 3 minutes'

Wat bestellingen geplaatst gisteren?,

u kunt dezelfde tabel gebruiken om de orders van gisteren te vinden door de functies DATE_TRUNC en CURRENT_DATE te combineren.

begin met een DATE_TRUNC functie om uw occurred_at waarden per dag af te ronden (omdat we willen weten of er gisteren iets is gebeurd). Gebruik dan eenWHERE clausule om alleen waarden te retourneren waarbij deoccurred_at dag gelijk is aan de huidige datum (met behulp van deCURRENT_DATE functie) minus een interval van één dag.,

SELECT * FROM demo.orders WHERE DATE_TRUNC('day',occurred_at) = CURRENT_DATE - interval '1 day'

Mode Analytics

het isoleren van uur-van-dag en dag-van-week met EXTRACT

met de functie EXTRACT Datum kunt u subvelden zoals jaar of uur isoleren van tijdstempels.

Hier is de syntaxis: EXTRACT(subfield FROM timestamp). Het uitvoeren van EXTRACT(month FROM '2015-02-12') zou een resultaat geven van 2.

houd er rekening mee dat, terwijl het onderstaande voorbeeld zich richt op het subveld uur (uur-van-dag), u vele andere subvelden tot uw beschikking heeft, variërend van millennium tot microsecond., U kunt de uitgebreide lijst van beschikbare subvelden hier bekijken.

hoeveel bestellingen worden per uur van de dag geplaatst?

een bedrijf dat een fulfillment center runt, wil misschien meer medewerkers als het grootste deel van de bestellingen binnenkomt. Om te achterhalen wanneer orders gedurende de dag worden geplaatst, kunt u de functie EXTRACT gebruiken en het subveld hour gebruiken om het uur van de dag (van 0 tot 23) te isoleren waarin een volgorde plaatsvond.,

SELECT EXTRACT(hour from occurred_at) AS hour FROM demo.orders

Gebruik de functie COUNT om orders te tellen, en vervolgens GROUP BY uur. (Aangezien hour de eerste waarde is in uwSELECT statement, kunt u GROUP BY 1).

SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1

ten slotte, om uw resultaten sequentieel te ordenen, gebruik ORDER BY 1.

SELECT EXTRACT(hour from occurred_at) AS hour, COUNT(*) AS orders FROM demo.orders GROUP BY 1 ORDER BY 1

en hier zijn de resultaten! Het lijkt erop dat het zou kunnen helpen om wat extra werknemers op de klok vroeg in de ochtend en rond lunchtijd.,

Mode Analytics

Wat is het gemiddelde ordervolume per weekdag?

om het gemiddelde volume van orders te bepalen dat zich per weekdag heeft voorgedaan, gebruik EXTRACT en het dow subveld om de dag van de week te isoleren (van 0-6, waarbij 0 zondag is) waarin een volgorde is opgetreden.

SELECT EXTRACT(dow from occurred_at) AS dow FROM demo.orders

volgende ronde de volgorde tijdstempels per dag met DATE_TRUNC., Het nemen van een COUNT van orders gegroepeerd met dow en day retourneert het aantal geplaatste bestellingen per dag samen met de overeenkomstige dag-van-week.

SELECT EXTRACT(dow from occurred_at) AS dow, DATE_TRUNC('day',occurred_at) AS day, COUNT(id) AS orders FROM demo.orders GROUP BY 1,2

gebruik de vorige query als een subquery (aliased as a) om het gemiddelde ordervolume op weekdagen te vinden. Neem het gemiddelde van orders (met behulp van deAVG() functie), en gebruik dan eenWHERE clausule om zaterdag en zondag uit te filteren.

grootgetal grafieken zijn geweldig voor het weergeven van geaggregeerde statistieken., Om het ordervolume in de gaten te houden, verzamel metrics zoals deze in één dashboard.

Mode Analytics

berekenen van de verstreken tijd met de leeftijd

de functie AGE datum berekent hoe lang geleden een gebeurtenis heeft plaatsgevonden. De syntaxis is vrij eenvoudig: pas AGE() toe op een enkele tijdstempel, en uw zoekopdracht zal de hoeveelheid tijd teruggeven sinds die gebeurtenis plaatsvond. Het uitvoeren van SELECT AGE( '2010-01-01' ) op 1 januari 2011 zou een resultaat opleveren van 1 years 0 months 0 days.,

AGE() kan ook bepalen hoeveel tijd er tussen twee gebeurtenissen is verstreken. In plaats van een enkele tijdstempel tussen de haakjes te plaatsen, voegt u beide tijdstempels in (beginnend met de meest recente tijdstempel) en scheidt u ze met een komma. Het uitvoeren van SELECT AGE( '2012-12-01',‘2010-01-01’) zou 2 years 11 months 0 daysteruggeven.

merk op dat deze toepassing van de functie AGE gelijk is aan het aftrekken van de tijdstempels: SELECT '2012-12-01' - '2010-01-01'.

hoe oud is een klantenaccount?,

stel dat uw verkoopteam wensen wil personaliseren op basis van hoe lang de klant uw product al gebruikt. U kunt vinden hoeveel tijd er is verstreken sinds het aanmaken van een account met behulp van de functie AGE.

de tabel modeanalytics.customer_accounts bevat records van monster klantaccounts. Selecteer de kolom met accountnamen (name) en pas de functie AGE() toe op de kolom met tijdstempels die tonen wanneer elk account is aangemaakt (created).,

SELECT name, AGE(created) AS account_age FROM modeanalytics.customer_accounts

Mode Analytics

hoe lang duurt het gemiddeld elke maand voor gebruikers hun profiel invullen?

de tabel modeanalytics.profilecreationevents bevat voorbeeldgegevens van gebruikers die een profiel hebben gemaakt, inclusief start-en eindtijdstempels.

om de gemiddelde tijd te vinden om elke maand een profiel in te vullen, moet u beginnen met het vinden van de tijd die elke gebruiker nodig had om een profiel in te vullen, evenals de maand waarin het profielcreatieproces werd gestart. Eerst, rond destarted_at tijdstempel per maand, met behulp van deDATE_TRUNC functie., Zoek vervolgens de verstreken tijd van started_at naar ended_at voor elk profiel met behulp van de functie AGE.

zoek het gemiddelde voor elke maand door de functie AVG toe te passen op de verstreken tijdwaarde (uw AGE statement) en te groeperen per maand.

om waarden in een consistente eenheid voor grafieken te retourneren, past u de EXTRACT functie en epoch subveld toe op uw waarden om resultaten te retourneren als een aantal seconden.

Mode Analytics

Aanbevolen artikelen

• Hoe Anti-Joins te beheersen en toe te passen op zakelijke problemen
• denken in SQL vs denken in Python
• DATE_TRUNC: een SQL-Tijdstempelfunctie waarop u kunt rekenen