Zine.net online

Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc
w Szukaj

dev2dev

Jaki będzie wynik zapytania: SELECT GETDATE() - GETDATE()?

Na wss.pl pojawił się wątek dotyczący działań na typach datetime.
Jeden z uczestników dyskusji stwierdził, że operacja odejmowania dwóch wartości datetime daje w wyniku czas.
Jak pokazałem, zapytanie

SELECT GETDATE() - GETDATE()

daje w wyniku 1900-01-01 00:00:00.000 czyli nadal typ datetime (należało się tego spodziewać) a dopiero po jawnym zrzutowaniu na float widzimy numeryczną reprezentację z różnicy dat (dlaczego nie na time, o tym mała uwaga na końcu notki). Ale pomyślałem sobie, że w pewnych warunkach wynik może być dodatni lub ujemny i to niedeterministycznie. Postanowiłem sprawdzić jak jest naprawdę.

Zrobiłem sobie funkcję:

ALTER FUNCTION retard
(
)
RETURNS datetime
AS
BEGIN

DECLARE @i int = 0

WHILE (@i < 10000000) set @i = @i+1

RETURN '1900-01-01 00:00:00.000'

END


i zrobiłem zapytanie:

select GETDATE()

select dbo.retard()

select GETDATE()




Na moim komputerze opóźnienie było kilkusekundowe. Wtedy zrobiłem kolejne zapytanie:

SELECT GETDATE() - dbo.retard() - GETDATE()


Ale tym razem wynik był zerowy (czyli 1900-01-01 00:00:00.000).

Hmm, dziwne. Po obejrzeniu planu zapytania widać, że dwa odwołania do funkcji getdate() generują ConstExpr1001 i ConstExpr1002 ale są one wartościowane w bliskim sobie czasie i server taktuje je jako stałe znane mu w czasie wykonania i w związku z tym wartościowane przed wartościowaniem innym składników zapytania.

Skoro tak to opakujmy funkcję getdate() o tak:

CREATE FUNCTION myGetdate
(
)
RETURNS datetime
AS
BEGIN
RETURN getdate()
END



i zróbmy zapytanie:
select dbo.mygetdate() - dbo.retard() - dbo.mygetdate()


Oooo i teraz jest efekt!
I widać z tego, że przy prostych operatorach arytmetycznych wartościowanie jest w kolejności wystąpienia (bo wynik jest mniejszy od 1900-01-01 00:00:00.000) . Hmm, skoro tak to zróbmy tak:

select dbo.mygetdate() - cast((dbo.retard() + dbo.mygetdate() ) as int)



Tym razem wynik jest większy od 1900-01-01 00:00:00.000 czyli drugi człon odejmowania jest wartościowany wcześniej. Ale nie ustając w poszukiwaniach zróbmy jeszcze takie zapytanie:


select dbo.mygetdate() - cast((dbo.retard() + dbo.mygetdate() ) as datetime)



Ale tym razem mimo, że spodziewalibyśmy się że drugi człon odejmowania będzie wartościowany wcześniej to wynik odejmowania jest mniejszy od 1900-01-01 00:00:00.000.

Zaglądając do planu zapytania widzimy, że mimo zastosowaliśmy jawne rzutowanie to parser zignorował je ponieważ wiedział, że nie ma takiej potrzeby skoro wszystkie składniki są typu datetime.

Jak widać po tych rozważaniach wynik zapytania będącego treścią tego wpisu jest trudny do przewidzenia. A przy okazji widać, że XML plan prawdę Ci powie ;-)


Rzutowanie różnicy dat na typ time.

Typ time ma dziedzinę wartości (dla maksymalnego rozmiaru time(7)) od 00:00:00.0000000 do 23:59:59.9999999 wobec tego nie istnieje coś takiego jak ujemny czas. Rzutowanie typu datetime na time zwraca z rezultatu datetime jedynie część informującą o czasie.Wobec tego zapytanie

SELECT cast(getdate() - (getdate()+1) AS time(7))

Zwróci wynik 00:00:00.0000000, podobnie jak zapytanie

SELECT cast((getdate()+1) - getdate() AS time(7))

Ale zapytanie

SELECT cast(getdate() - (getdate()+0.3333333333333) AS time(7))

zwróci wynik 16:00:00.0030000 podczas gdy zapytanie

SELECT cast((getdate()+0.3333333333333) - getdate() AS time(7))

zwróci wynik 07:59:59.9970000.

Dlatego operując na interwałach daty i czasu należy jednak polegać na funkcji DATEDIFF. Aby się przekonać co do następstwa dat (czyli wiedzieć czy tyle czasu upłynęło czy tyle czasu upłynie) wystarczyłoby wiedzieć jaki jest znak wyniku tej funkcji dla największej granulacji skali czasowej, czyli dla mikrosekund.

Czyli możemy zbudować zapytanie:

DECLARE @interval float = -0.000001
DECLARE @d1 datetime = getdate()
DECLARE @d2 datetime = getdate()+@interval
DECLARE @diff int = DATEDIFF(MICROSECOND, @d1, @d2)

IF (@diff > 0) print 'Trzeba czekać...'
ELSE print 'Minęło...'


I wszystko było pięknie dopóki nie ustawimy absolutnej wartości zmiennej @interwal na większą od 0.02485517 (tak wyszło z moich doświadczeń chociaż teoretycznie powinno to być więcej niż 0.0248551348032). Wówczas cała zabawa kończy się komunikatem

Msg 535, Level 16, State 0, Line 12
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.


Co jest oczywiste ze względu na fakt, że funkcja DATEDIFF jest typ int a zwracany wynik jest w mikrosekundach (dzień zawiera 86400000000 mikrosekund co jest poza zakresem tego typu).
Opublikowane 21 listopada 2009 11:35 przez marekpow
Filed under: , , , , , ,

Komentarze:

 

dotnetomaniak.pl said:

Dziękujemy za publikację - Trackback z dotnetomaniak.pl

listopada 23, 2009 12:18
Komentarze anonimowe wyłączone
W oparciu o Community Server (Personal Edition), Telligent Systems