Datofeltenes forbannelse

Av og til må man hente ut data fra en tabell basert på dato eller tid. Slike tabeller har gjerne en egen tidsmerkekolonne, eller timestamp som det egentlig heter. Problemet er at datatypen som passer i de aller fleste databasemotorer også har en tidskomponent, slik at det er forskjell på 20100830153312 og 20100830073658 selv om de begge er fra samme dag. Man kan selvsagt jukse og sette denne til null, slik at alt blir merket med dato sånn og sånn klokken 00:00:00, men det er ikke så praktisk og krever at man tar hensyn til det hver gang man legger inn data. Og for å gjøre dette morsommere finnes det knapt noen datofunksjoner som er standardiserte på tvers av databaseplattformer, så man må finne de som passer til den versjonen man har. Dette har gitt meg mye “glede” opp gjennom årene, og siden jeg jobber med sånt forholdsvis sjelden forsøker jeg nå å dokumentere noe av det jeg stadig må finne opp på nytt.

Finne dagens poster

Noen ganger ønsker man å fordele mer nøyaktig, men av og til er man  bare interessert i å finne ut hvilke/hvor mange poster som er opprettet en bestemt dag, for eksempel i dag.

Eksempel: (Henter unike userdn poster som er datostemplet i dag)

select distinct (userdn)
from mbstat
where CONVERT(varchar, GETDATE(), 111) = CONVERT(varchar, TStamp, 111)

Convert(varchar, [DatoTidsfelt],101) er det springende punktet her. Den konverterer datoen til tekst og fjerner tid-komponenten fra DateTime verdiene. Om vi gjør det samme med en getdate(), som gir dato og tid i det skriptet kjøres kan vi hente ut poster som er merket i dag. Ved å legge på DateADD kommandoen kan vi se ting som er fra i går, eller en annen dag:

select distinct (userdn)
from mbstat
where CONVERT(varchar, DATEADD(day, -1, GETDATE()), 111) = CONVERT(varchar, TStamp, 111)

Gruppere poster per dag

Nyttig når man lager statistikkfunksjoner. Eksempel:

SELECT UserDN, MAX(Size) AS Msize, MAX(TotalItems) AS MItems,
CONVERT(nvarchar, TStamp, 111) AS day
FROM MBStat
GROUP BY UserDN, CONVERT(nvarchar, TStamp, 111)

Rosinen i pølsa her er GROUP BY UserDN, CONVERT(nvarchar, TStamp, 111) som grupperer på userdn og dag, altså en gruppe per userdn per dag. Om man så ønsker å aggregere enda mer, kan man bruke denne som en inline select i en ny spørring slik:

SELECT SUM(Msize) AS TSize, SUM(MItems) AS TItems, day AS Day

FROM (SELECT UserDN, MAX(Size) AS Msize, MAX(TotalItems) AS MItems,
CONVERT(nvarchar, TStamp, 111) AS day

FROM MBStat
GROUP BY UserDN, CONVERT(nvarchar, TStamp, 111)) AS A
GROUP BY day
ORDER BY Day

Eksempelet er fra TS Tools mailstats og brukes til å vise hvordan totalt antall meldinger og samlet postkassestørrelse forandrer seg i exchange over tid.

Poster som er x måneder gamle

For å hente inn poster med en bestemt alder kan vi bruke dateadd funksjonen. Alderen vil da gjelde fra nå og bakover, eksempel:

WHERE (TStamp >= DATEADD(m, - @Param1, GETDATE()))

@Param1 erstattes med antall måneder i dette eksempelet, noe som vil gi poster som har en TStamp fra og med i dag akkurat nå og til og med samme klokkeslett for @Param1 måneder siden. Det er m som angir at vi bruker måneder. Den kan erstattes med andre koder for andre tidsrom. Et lite utdrag:

yy eller yyy År
mm eller m Måned
wk eller ww Uke
dd eller d Dag
hh Time
mi eller n minutt
ss eller s Sekund
ms Millisekund

@Param1 må forøvrig være et heltall/int.

Author: DizzyBadger

SQL Server DBA, Cluster expert, Principal Analyst

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.