Sök efter Date och Time värden
Förord
Anta att du skriver en SQL-sats som ska finna alla uttryck skrivna den 6 januari 2003. Du vet från kontrollen att det skrevs totalt 122 uttryck den dagen, men när du kör följande SQL-sats:
SELECT * FROM Invoices
WHERE InvoiceDate = '2003-01-06'
så returneras ett tomt resultatset. Vad är det som står på?
Sök efter Date och Time värden med SQL Server 2000
av Bryan Syverson
Hur Date och Time lagras i SQL Server
Innan du kan göra en effektiv utfrågning av Date/Time (eller temporära) data så måste du vet lite om hur Date/Time värden lagras. SQL Server stödjer två Date/Time datatyper: datetime och smalldatetime. Skillnaden mellan de är hur mycket utrymme de tar upp när de lagras. Datetime tar upp 8 bytes av lagringsutrymme, medan smalldatetime bara tar upp 4 bytes. På grund av detta så kan datetime representera Date/Time värden inom ett större område, och med en större precision än smalldatetime. Dessa skillnader kan summeras i tabellen här nedan.| Typ | Minimum | Maximum | Precision |
|---|---|---|---|
| DateTime | 1 Jan 1853 midnatt | 31 Dec 9999 23:59:59.997 (0.003 sekunder till midnatt) | Till närmaste 3.33 millisekunder. |
| SmallDateTime | 1 Jan 1900 midnatt | 6 Jun 2079 23:59 (1 minut till midnatt) | Till närmaste minut |
Både datetime och smalldatetime representerar alla Date och Time som ett värde ekvivalent till antalet dagar i relation till basdatumet. I SQL Server så är basdatumet på midnatt den 1 januari 1900. Som du kan se i tabellen så kan smalldatetime endast representera värden från det datumet och framåt. Datetime däremot kan representera datum som infaller innan den 1 januari 1900, och för att kunna göra det så lagras dessa värden som negativa tal.
För att kunna demonstrera hur Date/Time värden lagras så kan du föreställa dig att de består av två delar. Heltalet representerar antalet hela dagar som har gått sedan den 1 januari 1900, och decimaldelen representerar hur stor del av dagen som har passerat sedan midnatt. Ett Date/Time värde som t ex visar tolvtiden den 4 januari 1900 lagras som 3.5. I det här fallet så är 3 antalet hela dagar som har gått sedan basdatumet, och 0.5 är en halv dag mellan midnatt och tolvslaget. För att se det så kan du köra följande SQL-sats:
SELECT CAST(CAST('1900-01-04 12:00' AS datetime) AS float)
Not. CAST funktionen gör en specifik ändring av datatypen för det specificerade värdet. I det här uttrycket så ändrar den inre CASTen strängen '1900-01-04 12:00' till ett värde som datatypen datetime. Sen ändrar den yttre CASTen datetime-värdet till ett flyttal. Det slutgiltiga resultatet blir då en flytande ompresentation av det datetime-värde som representerar tolvslaget den 4 januari 1900.
Hittills har det gått bra. Men de problem som uppstår när man gör en utfrågning av date/time värden orsakas av en förbryllning över två grundläggande fakta som inte alltid är så uppenbara. För det första så är datatypen Date/Time ungefärliga värden, och inte exakta. Och för det andra så kan man inte lagra datum utan tid, eller tid utan datum.
Date/Time värden är ungefärliga värden
Datetime och smalldatetime är precis som de flytande datatyperna, float eller real, i den mening att de är ungefärliga värden. Det innebär att de värden som returneras från SQL Server kan skilja sig något från de värden som egentligen lagrades där. Om du t ex lagrar uttrycket 10/3.0 i en kolumn med datatypen float så kommer värdet 3.3333330000000001 att returneras. Även om det är en relativt bra avrundning av tio tredjedelar så är det inte ett exakt värde eftersom den avrundats efter den sjätte siffran. Faktum är att om du multiplicerar detta värde med tre så får du 9.9999990000000007, och inte 10. Självklart så förstår de flesta programmerarna att detta är en avrundningsfel, samt att det är ett problem som inte bara gäller för datorer som kör SQL Server – utan för alla digitala datorer. Men du måste ändå vara medveten om det då du kodar sökvillkor. Om du i motsats till detta arbetar med exakta värden så returnerar SQL Server exakt de värden som lagrades från början. Om du t ex lagrar 10/3.0 i en kolumn med datatypen int så både lagras och returneras värdet 3. I det här fallet så klassificerar SQL Server det resulterande uttrycket som ett realvärde, 3.333333. Sen klassificerar SQL Server 3.333333 till ett heltal, eftersom det är lagrat i en kolumn med datatypen int. Trots att det fortfarande är ett avrundningsfel så inträffar det innan värdet lagras. Det är alltså inget resultat av någon fysisk begränsning av lagringsutrymmet. Med andra ord så introducerades detta error av att man använde fel datatyp, inte av någon ärftlig begränsning av själva datatypen. Eftersom systemet alltid returnerar samma värde som är lagrat, så är datatypen exakt.
Nu ska vi se hur det här påverkar Date/Time värdena, och vi ska använda värdena 08.00 den 4 januari 1900. Som du såg ovan så lagras middagstiden för den här dagen som 3.5, alltså halvvägs genom den fjärde dagen. 08.00 är alltså en tredjedel genom den fjärde dagen, så dess presentation kommer att bli ungefärlig. För att kunna se det här själv så kan du köra följande SQL-sats:
SELECT CAST(CAST('1900-01-04 08:00' AS datetime) AS float)
Då kommer du att få följande resultat:
3.3333333333333335
Men om du istället kör den här SQL-satsen:
SELECT CAST(3.3333333 AS datetime), CAST(3.3333334 AS datetime)
Så kommer du att få följande resultat:
1900-01-04 07:59:59.997 1900-01-04 08:00:00.003
Som du kan se så är dessa tre värden relativt lika. Faktum är att de är så pass lika för att kunna räknas som 08.00 i de flesta applikationer. Men i ett sökvillkor som är baserat på ett enda värde, som t ex:
WHERE (DTValue = '1900-01-04 08:00')
så kommer du endast att matcha de poster där det lagrade värdet är exakt 3.3333333333333335. Du kommer att få lära dig hur man kringgår det här problemet senare i artikeln.
Datum utan tid och tider utan datum
SQL Server innefattar inga datatyper där du endast kan lagra datum eller tid. Så om du lagrar en Date/Time utan någon specifik tid så kommer decimaldelen att lagras som noll, vilket representerar midnatt som 00:00:00. Samma sak gäller om du lagrar en Date/Time utan något specifikt datum. Om du gör det så kommer heltalsdelen att lagras som noll, vilket representerar den 1 januari 1900. För att se det så kan du köra följande SQL-sats:
SELECT CAST('1900-01-04' AS datetime), CAST('10:00' AS datetime)
vilken returnerar följande resultat:
1900-01-04 00:00:00.000 1900-01-01 10:00:00.000
Hurvida du kan ignorera datum- eller tidskomponenten när du frågar ut en Date/Time kolumn beror helt på hur kolumnen är designad till att användas.
Databasdesignens påverkan på utfrågning
Databasdesigners använder inte alltid Date/Time kolumnerna korrekt. När databasen väl är designad så måste det vara bestämt om Date/Time kolumnerna ska lagra både datum och tid, bara datum eller bara tid. Designaren kan – genom att använda defaults, begränsningar och triggers – tvinga fram dessa regler för att förhindra oavsiktlig lagring av värden som antingen är onödiga eller oanvändbara.Ta t ex en kolumn i ett kontos betalsystem där ett datum returneras, där behövs det knappast någon tid. I det här fallet så bör designaren planera att använda kolumnen till endast datumvärden, och inte lagra någon tidskomponent. Man skulle kunna lägga till en trigger som förhindrar att icke-heltalsdelen av datumet lagras när man uppdaterar eller lägger in posten.
Fast generellt sett så tvingas ofta en programmerare att arbeta med en redan existerande databas. I det fallet så bör du kontrollera hur Date/Time värdena hanteras, innan du drar någon slutsats om huruvida den föregående designaren har gjort sitt jobb korrekt.
Det lättaste sättet att göra det är på är genom att köra en SQL-sats med ett sökvillkor som den som följer här nedan. I SQL-satsen så är DT själva Date/Time kolumnen i fråga:
WHERE CAST(FLOOR(CAST(DT AS float))AS datetime) = 0 OR
DT - CAST(FLOOR(CAST(DT AS float))AS datetime) = 0
Not. FLOOR funktionen returnerar det största heltalet som är mindre än eller lika med det specificerade värdet. I det här uttrycket så har man applicerat FLOOR funktionen till den flytande ompresentationen av DT kolumnen. Den tar helt enkelt bort decimaldelen av värdet.
Det första uttrycket returnerar datumet (heltalet) av värdet, medan det andra uttrycket returnerar tiden. Om inte den här SQL-satsen returnerar några poster så är det högst troligt att den här kolumnen har använts till att lagra både datum och tid, eftersom varken datumet eller tiden kan vara satt till 0.
Men kom självklart ihåg att om det returneras några poster så innebär inte det nödvändigtvis att kolumnen har använts till att endast lagra datum eller tid. Om tiden råkar vara exakt midnatt eller om datumet verkligen är den 1 januari 1900 så kommer det att visas i det resulterande setet. I så fall så kan du söka i kolumnen efter endast-datum eller endast-tid värden genom att använda någon av följande två SQL-satser:
WHERE TOnly <> Tonly - (CAST(FLOOR(CAST(TOnly AS float))AS datetime))
WHERE DOnly <> CAST(FLOOR(CAST(DOnly AS float))AS datetime)
Här representerar TOnly och DOnly Date/Time kolumnerna där du förväntar dig att endast finna endast datum respektive endast tid. Om SQL-satsen returnerar några poster så innehåller inte posterna den typ av data som du söker efter.
För att genomföra en intelligent utfrågning av kolumner så är det viktigt att man bestämmer vilken typ av data som ska lagras i Date/Time kolumnerna i varje tabell. Om kolumnerna används till antingen datum eller tid så blir det jobbet lättare. Men om kolumnerna används till både datum och tid så vet du åtminstone vilken SQL-sats som du måste hålla dig undan från om du ska koda SQL-satser.
Prestandaövervägningar vid utfrågning
En sökning som är baserad på Indexerade kolumner genomförs snabbare än en sökning som är baserad på en icke-Indexerad kolumn. Så Date/Time kolumner som genomsöks väldigt ofta borde Indexeras. Men var försiktigt, för om du sedan använder en funktion i sökargumentet så kan inte Indexet användas på samma sätt, och det försämrar prestandan. Sökningar som exekveras tusentals gånger om dagen på en produktionsdatabas kan orsaka markanta prestandaproblem. På grund av det så bör du undvika att använda funktioner i sådana sökargument så ofta du kan. Som du kommer att få se i exemplet nedan så kan det ibland resultera i lösningar som är mindre flexibla än för de som använder funktioner.Kom dessutom ihåg att vissa applikationer kräver att du söker efter delar av en Date/Time kolumn. Dessa delar kan vara endast datum, endast tid, eller mindre delar såsom år eller timmar. I sådana fall så kan det vara bra för prestandan om du delar upp en Date/Time kolumn till två eller fler separata kolumner, och sedan Indexera de som du tror söks oftast.
Hur du söker efter datum
Ibland kanske du ofta söker i en Date/Time kolumn efter ett specifikt datum – oavsett tid. Om alla data i kolumnen används till endast datum (med tiden satt till 0) så är det inga problem. Då är det bara att du söker efter datumet du vill åt. Men se över följande tabell, vi kallar den DateSample:
ID DateVal
-- -----------------------
1 2001-02-28 10:00:00.000
2 2002-02-28 13:58:32.823
3 2002-02-29 00:00:00.000
4 2002-02-28 00:00:00.000
Som du kan se så används DateVal kolumnen oregelbundet. De tredje och fjärde värdena indikerar på att kolumnen kan ha varit avsedd till att endast lagra datum, men de första och andra värdena indikerar på att detta i så fall inte var framtvingat.
Om du använder följande SQL-sats till att returnera poster med datumet 28 februari 2002
SELECT * FROM DateSample
WHERE DateVal = '2002-02-28'
så inkluderar det resulterande setet endast den fjärde posten, istället för både den andra och fjärde. Det beror på att datumdelen är direkt klassificerad som ett datetimevärde, som i det här fallet har en nollad tidskomponent. Och eftersom inte det matchar värdet i post 2 så returneras inte den posten.
Så, hur kommer man runt tidskomponenten? Om SQL-satsen körs ofta så bör du basera sökargumentet på ett intervall av olika värden, som här:
SELECT * FROM DateSample
WHERE DateVal BETWEEN '2002-02-28' AND '2002-02-28 23:59:59.997'
Kom ihåg att BETWEEN klausulen returnerar poster som är lika med min- och maxbegränsningen, så du kan inte bara koda maxbegränsningen som ’2002-02-29’. Om du gör det så kommer även den tredje posten att returneras, vilket den inte ska. Du kan även använda jämförelseoperatorer, vilket ger samma resultat:
SELECT * FROM DateSample
WHERE DateVal >= '2002-02-28' AND DateVal < '2002-02-29'
Om inte den här SQL-satsen körs så ofta (om du t ex ska skapa en rapport endast en gång i månaden) så kan du koda in ett uttryck i WHERE klausulen som trimmar bort decimalkomponenten av date/time värdet. Den här SQL-satsen, t ex:
SELECT * FROM DateSample
WHERE CAST(FLOOR(CAST(DateVal AS float)) AS datetime) = '2002-02-28'
returnerar både post 2 och 4. Det finns även flera andra uttryck som du kan använda för att uppnå samma resultat. Du kan finna dem i t ex ’my SQL Book’ och ’Murach’s SQL for SQL Server’.
Om du förresten önskar returnera poster med den 28 februari, oavsett år, så kan du använda följande SQL-sats:
SELECT * FROM DateSample
WHERE MONTH(DateVal) = 2 AND DAY(DateVal) = 28
vilken returnerar post 1, 2 och 4. Men eftersom det inte går att utföra det här utan att använda sig av en eller flera funktioner så bör man inte köra den här SQL-satsen mot en produktionsdatabas väldigt ofta. Om du måste utföra en sådan här sökning med en SQL-sats som körs ofta så bör du (om möjligt) ändra databasens design. Sen kan du skapa en separat, Indexerad kolumn för att lagra den delen av date/time värdet som du ofta vill komma åt.
Hur du söker efter tid
Att genomsöka en kolumn efter en specifik tid (oavsett datum) är inte helt olikt det att söka efter endast-datumvärden. Om kolumnen enbart lagrar tidsvärden så är sökningen mycket enkel. Men till skillnad från datumvärden så representeras tidsvärdena av ungefärliga siffror. Så trots att datumdelen kan ignoreras så måste du ändå räkna med vissa avrundningsfel.För att illustrera sökningar efter enbart tid så kan du se över följande tabell, kallad TimeSample:
ID TimeVal
-- -----------------------
1 2002-02-28 10:00:00.000
2 1900-01-01 13:58:32.823
3 1900-01-01 09:59:59.997
4 1900-01-01 10:00:00.000
Här används TimeVal kolumnen oregelbundet, ibland lagrar den enbart tid och ibland lagrar den både datum och tid. Så om du använder följande SQL-sats för att returnera poster med tiden 10.00:
SELECT * FROM TimeSample
WHERE TimeVal = '10:00:00'
så kommer endast post 4 att returneras. Den första posten returneras inte eftersom datumet är helt klassificerad som ett datetime värde, där tidskomponenten är noll, vilket inte matchar datumkomponenten i den första posten. Och post 3 returneras inte eftersom detta värde är nära, men inte exakt 10.00.
För att ignorera datumkomponenten i en kolumn så kan du koda ett uttryck som trimmar bort heltalsvärdet från date/time värdet, som här:
SELECT * FROM TimeSample
WHERE TimeVal - CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) = '10:00'
som returnerar post 1 och 4. Tyvärr finns det ingen metod för att utföra det här utan en eller flera funktioner. På grund av det så är det viktigt att endast-tid data lagras korrekt från början. Om du måste utföra en sådan här sökning ofta så bör du (om möjligt) ändra databasens design.
För att söka efter tidsvärden som är nästan exakt så kodar man helt enkelt in ett intervall av värden som ska sökas efter. Om endast-tid data lagras utan någon datumdel så kan du använda någon av följande två SQL-satser:
SELECT * FROM TimeSample
WHERE TimeVal BETWEEN '09:59' AND '10:01'
SELECT * FROM TimeSample
WHERE TimeVal > '09:59' AND TimeVal < '10:01'
Båda SQL-satserna returnerar post 3 och 4. Du måste självklart välja vilka värden du ska välja in i ditt ungefärliga intervall vid sökning.
Men om dina data med endast tid lagras oregelbundet så måste du använda både den nollade datumkomponenten tillsammans med ditt intervall av tidsvärden. Du kan t ex använda följande SQL-sats:
SELECT * FROM TimeSample
WHERE TimeVal - CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) > '09:59'
AND TimeVal - CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) < '10:01'
vilken returnerar post 1, 3 och 4. Återigen så går det tyvärr inte att utföra det här utan någon funktion. Så om du kan så bör du ändra databasens design om du vill utföra det här ofta.
Ett annat sätt för att använda ungefärliga tidsvärden är genom att använda smalldatetime istället för datatypen datetime i originaltabellen. Eftersom smalldatetime alltid avrundar tidsvärdet till närmaste minut så kommer tider i intervallet 09:59:29.999 to 10:00:29.998 att lagras som 10.00. Om det räcker med en avrundning till närmaste minut för din applikation så kan användandet av smalldatetime förhindra sökning av ett intervall tidsvärden.
0 Kommentarer