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.