Vă mulțumim pentru susținere

Cum se elimină porțiunea de timp a unei valori datetime (SQL Server)?

Iată ce folosesc:

SELECT CAST(FLOOR(CAST(getdate() as FLOAT)) as DATETIME)

Mă gândesc că poate exista un mod mai bun și mai elegant.

cerinţe:

  • Trebuie să fie cât mai rapid cu putință (mai puțin turnat, cu atât mai bine).
  • Rezultatul final trebuie să fie un tip datetime , nu un șir.
0
adăugat editat

6 răspunsuri

Codul dvs. CAST - FLOOR - CAST pare deja cel mai optim, cel puțin pe MS SQL Server 2005.

Unele alte soluții pe care le-am văzut au o conversie în șir, cum ar fi Select Convert (varchar (11), getdate (), 101) în ele, care este mai lent cu un factor de 10.

0
adăugat
Folosim metoda sugerată de Michael Stum într-unul dintre produsele noastre și funcționează ca un farmec.
adăugat autor Chris Roberts
Acesta nu este modul optim, destul de puțin. Consultați răspunsul meu pe aceeași pagină.
adăugat autor ErikE

SQL Server 2008 are un nou tip de date dată și simplifică această problemă pentru a:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
0
adăugat

Itzik Ben-Gan în Calculele DATETIME, Partea 1 (SQL Server Magazine, februarie 2007) arată trei metode de efectuare a unei astfel de conversii ( cel mai încet până la cel mai rapid , diferența dintre metoda a doua și a treia este mică):

SELECT CAST(CONVERT(char(8), GETDATE(), 112) AS datetime)

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

SELECT CAST(CAST(GETDATE() - 0.50000004 AS int) AS datetime)

Tehnica dvs. (turnarea la float ) este sugerată de un cititor în numărul din aprilie al revistei. Potrivit lui, el are o performanță comparabilă cu cea a celei de-a doua tehnici prezentate mai sus.

0
adăugat
@Emtucifor Sunt de acord că metoda a treia este foarte obscură din cauza valorii 0.50000004 , dar este cea mai rapidă și testele confirmă că . Astfel, aceasta satisface cerința cât mai rapid posibil .
adăugat autor Marek Grzenkowicz
@Emtucifor De asemenea, iată ce scrie articolul despre valoarea 0.50000004 : Deși această expresie este scurtă (și eficientă, după cum voi demonstra în scurt timp), trebuie să spun că mă simt neliniștit de asta </​​b>. Nu sunt sigur că pot să-mi pun degetul exact de ce? Poate pentru că este prea tehnic și nu puteți vedea logica legată de datetime în el.
adăugat autor Marek Grzenkowicz
În opinia mea, aruncarea la plutire nu este cea mai bună. Vă rugăm să vedeți răspunsul meu
adăugat autor ErikE
Dacă vom folosi această metodă, aș prefera SELECT CAST (CAST (GETDATE () - '12: 00: 00.003 'AS int) AS datetime) și este mult mai ușor de reținut.
adăugat autor ErikE
Acest lucru este acum cel mai rapid în SQL 2008: Convert (date, GetDate ()) .
adăugat autor ErikE

SQL Server 2008 și mai sus

În SQL Server 2008 și în sus, desigur, cel mai rapid mod este Convert (date, @date) . Aceasta poate fi redată înapoi la datetime sau datetime2 , dacă este necesar.

Ce este foarte bun în SQL Server 2005 și mai vechi?

Am văzut revendicări inconsecvente cu privire la ceea ce este mai rapid pentru trunchierea timpului dintr-o dată în SQL Server, iar unii chiar au spus că au făcut teste, dar experiența mea a fost diferită. Deci, să facem niște teste mai stricte și să lăsăm pe toată lumea să aibă scenariul, așa că dacă fac greșeli, oamenii mă pot corecta.

Conversiile de flotare nu sunt precise

Mai întâi, aș sta departe de a transforma datetime în float , deoarece nu convertește corect. S-ar putea să scăpați cu exactitate, dar cred că este o idee proastă să o utilizați, deoarece implicit îi comunică dezvoltatorilor că aceasta este o operațiune sigură și nu este . Aruncati o privire:

declare @d datetime;
set @d = '2010-09-12 00:00:00.003';
select Convert(datetime, Convert(float, @d));
-- result: 2010-09-12 00:00:00.000 -- oops

Nu este ceva ce ar trebui să învățăm oamenii în codul nostru sau în exemplele noastre online.

De asemenea, nu este chiar cea mai rapidă cale!

Dovada? Testarea performanțelor

Dacă doriți să efectuați niște teste pentru a vedea cum într-adevăr se comportă metodele diferite, atunci veți avea nevoie de acest script de configurare pentru a rula testele mai jos:

create table AllDay (Tm datetime NOT NULL CONSTRAINT PK_AllDay PRIMARY KEY CLUSTERED);
declare @d datetime;
set @d = DateDiff(Day, 0, GetDate());
insert AllDay select @d;
while @@ROWCOUNT != 0
   insert AllDay
   select * from (
      select Tm =
         DateAdd(ms, (select Max(DateDiff(ms, @d, Tm)) from AllDay) + 3, Tm)
      from AllDay
   ) X
   where Tm < DateAdd(Day, 1, @d);
exec sp_spaceused AllDay;  -- 25,920,000 rows

Please note that this creates a 427.57 MB table in your database and will take something like 15-30 minutes to run. If your database is small and set to 10% growth it will take longer than if you size big enough first.

Acum pentru scriptul real de testare a performanței. Rețineți că intenționăm să nu întoarcem rândurile înapoi la client, deoarece acest lucru este nebun de scump pe 26 de milioane de rânduri și ar ascunde diferențele de performanță dintre metode.

Rezultate de performanță

set statistics time on;
-- (All queries are the same on io: logical reads 54712)
GO
declare
    @dd date,
    @d datetime,
    @di int,
    @df float,
    @dv varchar(10);

-- Round trip back to datetime
select @d = CONVERT(date, Tm) from AllDay; -- CPU time = 21234 ms,  elapsed time = 22301 ms.
select @d = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 23031 ms, elapsed = 24091 ms.
select @d = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23782 ms, elapsed = 24818 ms.
select @d = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 36891 ms, elapsed = 38414 ms.
select @d = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 102984 ms, elapsed = 109897 ms.
select @d = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 103390 ms,  elapsed = 108236 ms.
select @d = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 123375 ms, elapsed = 135179 ms.

-- Only to another type but not back
select @dd = Tm from AllDay; -- CPU time = 19891 ms,  elapsed time = 20937 ms.
select @di = CAST(Tm - 0.50000004 AS int) from AllDay; -- CPU = 21453 ms, elapsed = 23079 ms.
select @di = DATEDIFF(DAY, 0, Tm) from AllDay; -- CPU = 23218 ms, elapsed = 24700 ms
select @df = FLOOR(CAST(Tm as float)) from AllDay; -- CPU = 29312 ms, elapsed = 31101 ms.
select @dv = CONVERT(VARCHAR(8), Tm, 112) from AllDay; -- CPU = 64016 ms, elapsed = 67815 ms.
select @dv = CONVERT(CHAR(8), Tm, 112) from AllDay; -- CPU = 64297 ms,  elapsed = 67987 ms.
select @dv = CONVERT(VARCHAR(10), Tm, 101) from AllDay; -- CPU = 65609 ms, elapsed = 68173 ms.
GO
set statistics time off;

Analiza pariurilor

Câteva note despre asta. Mai întâi, dacă efectuați doar o comparație GROUP sau o comparație, nu este nevoie să efectuați conversia înapoi la datetime . Deci, puteți salva unele CPU, evitând acest lucru, dacă nu aveți nevoie de valoarea finală pentru afișare. Puteți chiar GROUPA prin valoarea neconvertită și puneți conversia numai în clauza SELECT:

select Convert(datetime, DateDiff(dd, 0, Tm))
from (select '2010-09-12 00:00:00.003') X (Tm)
group by DateDiff(dd, 0, Tm)

Also, see how the numeric conversions only take slightly more time to convert back to datetime, but the varchar conversion almost doubles? This reveals the portion of the CPU that is devoted to date calculation in the queries. There are parts of the CPU usage that don't involve date calculation, and this appears to be something close to 19875 ms in the above queries. Then the conversion takes some additional amount, so if there are two conversions, that amount is used up approximately twice.

More examination reveals that compared to Convert(, 112), the Convert(, 101) query has some additional CPU expense (since it uses a longer varchar?), because the second conversion back to date doesn't cost as much as the initial conversion to varchar, but with Convert(, 112) it is closer to the same 20000 ms CPU base cost.

Iată acele calcule pe timpul procesorului pe care l-am folosit pentru analiza de mai sus:

     method   round  single   base
-----------  ------  ------  -----
       date   21324   19891  18458
        int   23031   21453  19875
   datediff   23782   23218  22654
      float   36891   29312  21733
varchar-112  102984   64016  25048
varchar-101  123375   65609   7843
  • round is the CPU time for a round trip back to datetime.

  • single is CPU time for a single conversion to the alternate data type (the one that has the side effect of removing the time portion).

  • base is the calculation of subtracting from single the difference between the two invocations: single - (round - single). It's a ballpark figure that assumes the conversion to and from that data type and datetime is approximately the same in either direction. It appears this assumption is not perfect but is close because the values are all close to 20000 ms with only one exception.

Un lucru mai interesant este faptul că costul de bază este aproape egal cu metoda Convert (date) (care trebuie să fie aproape zero, deoarece serverul poate extrage intern porțiunea de zi întregă chiar din primii patru octeți ai tipului de date datetime ).

Concluzie

So what it looks like is that the single-direction varchar conversion method takes about 1.8 ?s and the single-direction DateDiff method takes about 0.18 ?s. I'm basing this on the most conservative "base CPU" time in my testing of 18458 ms total for 25,920,000 rows, so 23218 ms / 25920000 = 0.18 ?s. The apparent 10x improvement seems like a lot, but it is frankly pretty small until you are dealing with hundreds of thousands of rows (617k rows = 1 second savings).

Chiar și datorită acestei mici îmbunătățiri absolute, după părerea mea, metoda DateAdd câștigă pentru că este cea mai bună combinație de performanță și claritate. Răspunsul care necesită un "număr magic" de 0.50000004 va mușca pe cineva într-o zi (cinci zerouri sau șase ???) și este mai greu de înțeles.

Note suplimentare

Când voi obține ceva timp voi schimba 0.50000004 la '12: 00: 00.003 ' și veți vedea cum se întâmplă. Este convertit la aceeași valoare datetime și mi se pare mult mai ușor de reținut.

Pentru cei interesați, testele de mai sus au fost difuzate pe un server unde @@ Versiunea returnează următoarele:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 9 iulie 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition pe Windows NT 5.2 (Build 3790: Service Pack 2) / p>

0
adăugat
@ Gabe mulțumesc, fixat. Char pare să fie exact același ca varchar.
adăugat autor ErikE
@ user3341592 Vă rugăm să puneți o nouă întrebare. Simțiți-vă libertatea de a comenta cu un link.
adăugat autor ErikE
Downvoter vă rog să comentați? A rade!
adăugat autor ErikE
@Roman Dacă lucrați cu SQL Server 2008 și în sus, da, convertirea la date este cea mai rapidă, așa cum se arată în testele mele de mai sus.
adăugat autor ErikE
@ErikE, terminat. Vedeți stackoverflow.com/questions/40193719/… .
adăugat autor user3341592
Partea de timp ca "HH: MM: SS" și ca "HH: MM" variante, în cazul în care aceasta face o diferență?
adăugat autor user3341592
Ar fi interesant să știi contrariul: care este cel mai bun mod de a păstra doar partea de timp?
adăugat autor user3341592
Se pare că ai singur și rotund înapoi în tabel. De asemenea, există vreo diferență în timp dacă folosiți char în loc de varchar ?
adăugat autor Gabe
@Denis, în Oracle există funcția simple trunc (). Dacă utilizați o rundă, cred că veți găsi că timpul după ora 12 este rotunjit în ziua următoare, în loc de data curentă.
adăugat autor Rick
+1 răspuns bun, dar nu este convertit la data de lucru mai repede decât datediff?
adăugat autor Roman Pekar
+1 Ce versiune de SQL Server ați testat acest lucru pe drum?
adăugat autor Martin Smith
+1 Mi-a plăcut punctul în care conversiile prin float nu sunt corecte
adăugat autor A-K
@ Rick, mulțumesc! De fapt, mi-ați deschis ochii cu privire la semnificația acestui cuvânt cheie în contextul datei-timp.
adăugat autor Denis Valeev
În Oracle există selectați runda (sysdate) din dual și cu siguranță avem nevoie de acel server Sql.
adăugat autor Denis Valeev

Te rog incearca:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
0
adăugat

SQL2005: Vă recomandăm distribuirea în loc de dateadd. De exemplu,

select cast(DATEDIFF(DAY, 0, datetimefield) as datetime)

în medie de 10% mai rapid în setul de date, decât

select DATEADD(DAY, DATEDIFF(DAY, 0, datetimefield), 0)

(și turnarea în timpul smalldate a fost mai rapidă)

0
adăugat