– Modus Blogg

Tidsstempler er avgjørende for forretnings-analyse for en svært enkel grunn: de forteller deg når ting skjer. Tenk å prøve å suss ut trender i dataene som månedlig web-trafikk, eller kvartalsvis inntjening, eller daglig for volum uten å vite når hendelser fant sted. Det ville være et mareritt.

PostgreSQL tilbyr et utvalg av dato funksjoner for å manipulere tidsstempler. Å skille nyttig fra obskure, vi deler fremgangsmåter for de mest brukte Postgres dato funksjoner og virksomhet scenarier hvor de kommer i hendig.,

Den mest brukte Postgres dato funksjoner og virksomhet scenarier hvor de kommer i hendig:

• Avrunding av tidsstempler med DATE_TRUNC
• å Finne hendelser i forhold til nåtid med NÅ() og CURRENT_DATE
• Isolere time-for-dag, og dag-til-uke-med-EKSTRAKT
• Beregning av tiden gått med ALDER

Vi har laget dataene for hvert eksempel er tilgjengelig i Modusen Offentlige Galleriet. Prøv hver dato funksjon i Modus når du arbeider deg gjennom disse eksemplene. Registrer deg for en konto i Modus Studio og åpne en ny rapport for å begynne.,

Avrunding av tidsstempler med DATE_TRUNC

DATE_TRUNC funksjon runder et tidsstempel verdi til et angitt intervall, noe som gir deg mulighet til å telle hendelser. Du kan runde av et tidsstempel til følgende enheter av gangen:

• mikrosekund
• millisekund
• andre
• minutter
• timers
• dag
• uke
• måned
• kvartal
• året
• tiår
• århundre
• millenium

DATE_TRUNC syntaks ser ut som dette: DATE_TRUNC('interval',timestamp).,

For eksempel, SELECT DATE_TRUNC('day','2015-04-12 14:44:18') ville returnere et resultat av 2015-04-12 00:00:00.

For en mer detaljert forklaring av DATE_TRUNC (og en utskriftsvennlig referanse kan du holde på pulten din!), sjekk ut dette innlegget.

Hvordan har internett-trafikk endret seg over tid?

Prøv DATE_TRUNC for deg selv ved å spørre tabellen modeanalytics.web_events, som inneholder eksempler på registreringer av besøk på nettstedet, inkludert en occurred_at kolonne. Du kan isolere måneden på besøk med DATE_TRUNC.,

for Å returnere en oversikt over web-besøk hver måned ved kanalen, legge til channel kolonne og en COUNT til SELECT uttalelsen, da gruppen av month og channel. (Måned siden, og kanalen er de to første verdiene i SELECT uttalelsen, kan du GROUP BY 1,2), som dette:

til Slutt, bruk ORDER BY 1,2 for å organisere resultatene i kronologisk rekkefølge (per måned) og alfabetisk (av-kanal).,

I-Modus, kan du bygge en linje diagrammet for å visualisere søkeresultater.

Modus Analytics

Finn hendelser i forhold til nåtid med NÅ() og CURRENT_DATE

NOW() dato-funksjonen returnerer den aktuelle tidsstempel i UTC (hvis tidssonen er uspesifisert). Du kan trekke mellomrom fra NOW() for å trekke hendelsene som skjedde i løpet av de siste time, siste dag, den siste uken, etc.

å Kjøre SELECT NOW() kl 9:00 UTC 11. oktober 2016 vil resultere i 2016-10-11 09:00:00.,

CURRENT_DATE funksjon returnerer gjeldende dato, ikke hele tidsstempel. Kjører SELECT CURRENT_DATE kl 9:00 UTC 11. oktober 2016 ville komme tilbake 2016-10-11.

Hva bestillinger som ble lagt inn i løpet av de siste 12 timer?

tabellen demo.ordrer som inneholder eksempler på registreringer av alle bestillinger, inkludert en occurred_at tidsstempel kolonne i UTC.,

for Å finne bestillinger som er foretatt i løpet av de siste 12 timer, bruk en WHERE klausulen til å returnere bare bestillinger som er plassert etter eller nøyaktig på (>=) gjeldende tidsstempel (NOW()) minus et intervall på 12 timer.,

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

Modus Analytics

I tillegg til hour, kan du bruke en av følgende intervaller:

• mikrosekunder
• millisekunder
• andre
• minutter
• timers
• dag
• uke
• måned
• året
• tiår
• århundre
• millennium

Du kan også kombinere ulike intervaller i samme uttrykk som dette:
interval '4 hours 3 minutes'

Hva ordrer ble plassert i går?,

Du kan bruke den samme tabellen for å finne gårsdagens bestillinger ved å kombinere DATE_TRUNC og CURRENT_DATE funksjoner.

Begynn ved hjelp av en DATE_TRUNC funksjonen til å rundt ditt occurred_at verdier av dagen (siden vi ønsker å vite om noe som skjedde i går). Bruk deretter en WHERE klausulen til å returnere bare verdier der occurred_at dag er lik den aktuelle datoen (med CURRENT_DATE funksjon) minus et intervall på en dag.,

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

Modus Analytics

Isolere time-for-dag, og dag-til-uke-med-EKSTRAKT

EXTRACT dato-funksjonen gir deg mulighet til å isolere subfields, for eksempel år eller time fra tidsstempler.

Her er de syntaks: EXTRACT(subfield FROM timestamp). Kjører EXTRACT(month FROM '2015-02-12') ville returnere et resultat av 2.

husk at mens eksemplet nedenfor fokuserer på subfield time (time-of-day), har du mange andre subfields til din disposisjon alt fra millennium til microsecond., Du kan sjekke ut den omfattende liste over tilgjengelige subfields her.

Hvor mange bestillinger hver time av dagen?

Et selskap som kjører en oppfyllelse center kanskje ønsker å ansatte flere ansatte når mesteparten av bestillingene kommer inn. For å finne ut når ordre er plassert hele dagen, kan du bruke EXTRACT funksjon og hour subfield å isolere time-of-day (fra 0 til 23) som en ordre oppstått.,

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

for COUNT funksjon å samle bestillinger, og deretter GROUP BY time. (Siden time er den første verdien i SELECT uttalelsen, kan du GROUP BY 1).

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

til Slutt, å organisere resultatene sekvensielt, bruk ORDER BY 1.

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

Og her er resultatene! Ser ut som det kan hjelpe å ha noen ekstra arbeidere på klokken tidlig på morgenen og rundt lunsj tid.,

Modus Analytics

Hva er gjennomsnittlig ukedag for volum?

for Å finne gjennomsnittlig volum av bestillinger som oppstod ved ukedag, bruk EXTRACT og dow subfield å isolere den dag-til-uke (fra 0-6, der 0 er søndag) hvor en ordre oppstått.

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

Neste runde for tidsstempler av dagen med DATE_TRUNC., Ta en COUNT ordrer gruppert etter dow og day vil returnere antall bestillinger hver dag sammen med den tilsvarende dagen-av-uke.

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

for Å finne gjennomsnittlig ukedag for volum, bruk den forrige spørring som et subquery (aliased som a). Ta gjennomsnittet av ordrer (ved bruk av AVG() funksjon), og deretter bruke en WHERE klausulen til å filtrere ut lørdager og søndager.

Stort Antall diagrammer er stor for visning aggregerte beregninger., For å holde øye med bestilling volum, samle beregninger som dette i én oversikt.

Modus Analytics

Beregning av tiden gått med ALDER

AGE dato-funksjonen beregner hvor lenge det er siden en hendelse. Syntaksen er ganske enkelt: bruk AGE() til en enkelt tidsstempel, og søket vil returnere beløpet av tid siden hendelsen tok sted. Kjører SELECT AGE( '2010-01-01' ) 1. januar 2011 vil returnere et resultat av 1 years 0 months 0 days.,

AGE() kan også bestemme hvor mye tid som gikk mellom to hendelser. I stedet for å sette en enkelt tidsstempel i parentes, sett både tidsstempler (begynn med det siste tidsstempel) og skiller du dem med komma. Kjører SELECT AGE( '2012-12-01',‘2010-01-01’) ville komme tilbake 2 years 11 months 0 days.

Merk at dette programmet av AGE funksjon er det samme som å trekke fra tidsstempler: SELECT '2012-12-01' - '2010-01-01'.

Hvor gammel er en kunde konto?,

Tenk deg ditt salgsteam ønsker å tilpasse hilsener basert på hvor lenge kunden har vært å bruke produktet. Du kan finne ut hvor mye tid som har gått siden du oppretter kontoen ved hjelp av AGE funksjon.

tabellen modeanalytics.customer_accounts inneholder oppføringer for eksempel kunde-kontoer. Velg kolonnen av konto navn (name) og anvende AGE() funksjonen til rekken av tidsstempler som viser når hver konto ble opprettet (created).,

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

Modus Analytics

Hvor lang tid tar det brukere til å fullføre profilen sin hver måned, i gjennomsnitt?

tabellen modeanalytics.profilecreationevents inneholder eksempeldata av brukere som har opprettet en profil, inkludert start-og slutt-tidsstempler.

for Å finne den gjennomsnittlige tiden for å fullføre en profil hver måned, med start ved å finne tiden det tok hver bruker for å fullføre en profil, så vel som den måned profil prosessen var i gang. Første runde started_at tidsstempel av måneden, bruk av DATE_TRUNC funksjon., Neste, finn den tiden som har gått fra started_at til ended_at for hver profil ved hjelp av AGE funksjon.

Finn gjennomsnittet for hver måned ved å bruke AVG funksjonen til den tiden som er gått verdi (din AGE statement) og gruppering av måneden.

for Å returnere verdier på en konsistent enhet for kartlegging, bruk EXTRACT funksjon og epoke subfield til dine verdier å returnere resultater som teller sekunder.

Modus Analytics –

– >

Anbefalte artikler

• Hvordan å Mestre Anti Sammenføyninger og Bruke Dem til Business Problemer
• Tenkning i SQL vs Tenkning i Python
• DATE_TRUNC: EN SQL-Timestamp-Funksjonen Kan Du Stole På