Πώς να αφαιρέσετε τον χρόνο τμήμα της αξίας datetime (SQL Server);

ψήφοι
77

Εδώ είναι τι μπορώ να χρησιμοποιήσω:

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

Σκέφτομαι μπορεί να υπάρχει μια καλύτερη και πιο κομψό τρόπο.

Απαιτήσεις:

  • Πρέπει να είναι όσο το δυνατόν γρηγορότερα (το λιγότερο χύτευσης, τόσο το καλύτερο).
  • Το τελικό αποτέλεσμα πρέπει να είναι ένα datetimeείδος, δεν είναι ένα string.
Δημοσιεύθηκε 05/08/2008 στις 21:08
πηγή χρήστη
Σε άλλες γλώσσες...                            


6 απαντήσεις

ψήφοι
106

SQL Server 2008 και πάνω

Στον SQL Server 2008 και μέχρι, φυσικά, ο πιο γρήγορος τρόπος είναι Convert(date, @date). Αυτό μπορεί να ρίχνει πίσω στη μια datetimeή datetime2εάν είναι απαραίτητο.

Τι είναι πραγματικά καλύτερο Στην SQL Server 2005 και ηλικιωμένων;

Έχω δει ασυνεπείς ισχυρισμούς για το τι είναι ταχύτερος για την περικοπή του χρόνου από την ημερομηνία του SQL Server, και μερικοί άνθρωποι, ακόμη και είπαν ότι έκαναν δοκιμές, αλλά η εμπειρία μου ήταν διαφορετική. Ας κάνουμε λίγο περισσότερο αυστηρές δοκιμές και ο καθένας ας έχουν το σενάριο, ώστε αν κάνω κάποιο λάθος οι άνθρωποι μπορούν να με διορθώσει.

Μετατροπές Float δεν είναι ακριβή

Κατ 'αρχάς, θα ήθελα να μείνει μακριά από τη μετατροπή datetimeσε float, γιατί δεν μετατρέπει σωστά. Μπορεί να ξεφύγει με κάνει το πράγμα του χρόνου απομάκρυνσης με ακρίβεια, αλλά νομίζω ότι είναι μια κακή ιδέα να το χρησιμοποιήσει, διότι επικοινωνεί σιωπηρά για τους προγραμματιστές ότι πρόκειται για την ασφαλή λειτουργία και δεν είναι . Ρίξε μια ματιά:

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

Αυτό δεν είναι κάτι που θα πρέπει να διδάξει τους ανθρώπους στον κώδικά μας ή στα παραδείγματά μας στο διαδίκτυο.

Επίσης, δεν είναι καν ο πιο γρήγορος τρόπος!

Απόδειξη - Δοκιμασία απόδοσης

Αν θέλετε να κάνετε κάποιες εξετάσεις στον εαυτό σας για να δείτε πώς οι διαφορετικές μέθοδοι πραγματικά συσσωρεύονται, τότε θα πρέπει αυτό το σενάριο εγκατάστασης για την εκτέλεση των δοκιμών πιο κάτω:

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

Παρακαλείστε να σημειώσετε ότι αυτό δημιουργεί έναν πίνακα 427.57 MB στη βάση δεδομένων σας και θα πάρετε κάτι σαν 15-30 λεπτά για να τρέξει. Εάν η βάση δεδομένων σας είναι μικρό και οριστεί σε αύξηση 10% ότι θα διαρκέσει περισσότερο από ό, τι αν ταξινομούν αρκετά μεγάλη για πρώτη φορά.

Τώρα για το πραγματικό σενάριο έλεγχο της απόδοσης. Παρακαλώ σημειώστε ότι είναι σκόπιμη για να μην επιστρέψει σειρές πίσω στον πελάτη και αυτό είναι τρελό ακριβά σε 26 εκατομμύρια γραμμές και θα κρύψουν τις διαφορές απόδοσης μεταξύ των μεθόδων.

Αποτελέσματα Απόδοσης

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;

Μερικά Rambling Ανάλυση

Μερικές σημειώσεις σχετικά με αυτό. Πρώτα απ 'όλα, αν απλά εκτελεί μια ομάδα από ή σύγκριση, δεν υπάρχει καμία ανάγκη να επαναφέρουν τον datetime. Έτσι μπορείτε να εξοικονομήσετε CPU με την αποφυγή ότι, εάν δεν χρειάζεστε την τελική τιμή για σκοπούς προβολής. Μπορείτε ακόμη ομάδα από τις μη μετατραπείσα αξία και να θέσει τη μετατροπή μόνο στο 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)

Επίσης, δείτε πώς οι αριθμητικές μετατροπές πάρει μόνο λίγο περισσότερο χρόνο για να επαναφέρουν τον datetime, αλλά η varcharμετατροπή σχεδόν διπλασιάζει; Αυτό αποκαλύπτει το τμήμα της CPU που είναι αφιερωμένο στην υπολογισμός σήμερα στα ερωτήματα. Υπάρχουν μέρη της χρήση της CPU που δεν αφορούν τον υπολογισμό ημερομηνίας, και αυτό φαίνεται να είναι κάτι κοντά σε 19875 ms στα παραπάνω ερωτήματα. Στη συνέχεια, η μετατροπή χρειάζεται κάποιο πρόσθετο ποσό, οπότε αν υπάρχουν δύο μετατροπές, το ποσό αυτό χρησιμοποιείται μέχρι περίπου δύο φορές.

Περισσότερα εξέταση αποκαλύπτει ότι σε σύγκριση με Convert(, 112)το Convert(, 101)ερώτημα έχει κάποια πρόσθετη δαπάνη CPU (δεδομένου ότι χρησιμοποιεί μια πιο varchar;), επειδή η δεύτερη μετατροπή πίσω dateδεν κοστίζει όσο η αρχική μετατροπή σε varchar, αλλά με Convert(, 112)αυτό είναι πιο κοντά στο ίδιο 20000 ms CPU βασικό κόστος.

Εδώ είναι οι υπολογισμοί για το χρόνο της CPU που θα χρησιμοποιηθεί για την ανάλυση που προηγήθηκε:

     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
  • γύρο είναι ο χρόνος της CPU για ένα ταξίδι μετ 'επιστροφής πίσω datetime.

  • μονό είναι χρόνος CPU για ένα ενιαίο μετατροπή προς το εναλλακτικό τύπο δεδομένων (εκείνο που έχει την παρενέργεια της απομάκρυνσης του τμήματος χρόνου).

  • βάσης είναι ο υπολογισμός της αφαιρέσεως από singleτη διαφορά μεταξύ των δύο επικλήσεις: single - (round - single). Είναι μια φιγούρα εξέδρα που αναλαμβάνει την μετατροπή προς και από αυτόν τον τύπο δεδομένων και datetimeείναι περίπου η ίδια σε κάθε κατεύθυνση. Φαίνεται αυτή η υπόθεση δεν είναι τέλειο, αλλά είναι κοντά, διότι οι τιμές είναι όλες κοντά στο 20000 ms με μία μόνο εξαίρεση.

Ένα πιο ενδιαφέρον πράγμα είναι ότι το κόστος βάσης είναι σχεδόν ίση με την ενιαία Convert(date)μέθοδο (η οποία πρέπει να είναι σχεδόν 0 κόστους, όπως ο διακομιστής μπορεί εσωτερικά εξαγάγετε το τμήμα ημέρα ακέραιος δεξιά από τα τέσσερα πρώτα bytes του datetimeτύπου δεδομένων).

συμπέρασμα

Έτσι, αυτό που μοιάζει είναι ότι η ενιαία κατεύθυνση varcharμέθοδος μετατροπής διαρκεί περίπου 1,8 μs και η ενιαία κατεύθυνση DateDiffμέθοδος διαρκεί περίπου 0,18 μικροδευτερόλεπτα. Είμαι βασίζοντας αυτό σχετικά με την πιο συντηρητική «βάση CPU» χρόνο στις δοκιμές μου, του συνόλου των 18458 ms για 25.920.000 σειρές, έτσι ώστε 23218 ms / 25.920.000 = 0,18 μικροδευτερόλεπτα. Η φαινομενική 10x βελτίωση φαίνεται σαν μια παρτίδα, αλλά είναι ειλικρινά πολύ μικρός, μέχρι να κάνουμε με εκατοντάδες χιλιάδες σειρές (617k σειρές = 1 δευτερόλεπτο αποταμίευση).

Ακόμη και αν αυτή η μικρή απόλυτη βελτίωση, κατά τη γνώμη μου, η DateAddμέθοδος κερδίζει γιατί είναι ο καλύτερος συνδυασμός απόδοσης και σαφήνεια. Η απάντηση που απαιτεί ένα «μαγικό αριθμό» των 0.50000004πρόκειται να δαγκώσει κάποιον κάποια μέρα (πέντε μηδενικά ή έξι ???), καθώς είναι πιο δύσκολο να κατανοήσουμε.

επιπρόσθετες σημειώσεις

Όταν έχω κάποιο χρόνο Πάω να αλλάξει 0.50000004για να '12:00:00.003'και να δούμε πώς το κάνει. Είναι μετατρέπεται με την ίδια datetimeτιμή και θεωρώ ότι είναι πολύ πιο εύκολο να θυμόμαστε.

Για όσους ενδιαφέρονται, οι παραπάνω δοκιμές έγιναν σε ένα διακομιστή όπου @@ Έκδοση επιστρέφει τα εξής:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) ένατης, Ιουλίου 2008 14:43:34 Copyright (c) 1988 - 2008 Microsoft Corporation Standard Edition για τα Windows NT 5.2 (Build 3790: Service Pack 2)

Απαντήθηκε 12/09/2010 στις 23:57
πηγή χρήστη

ψήφοι
27

SQL Server 2008 έχει μια νέα ημερομηνία τύπο δεδομένων και αυτό απλοποιεί το πρόβλημα αυτό:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
Απαντήθηκε 06/08/2008 στις 07:44
πηγή χρήστη

ψήφοι
16

Itzik Ben-Gan στο Υπολογισμοί DATETIME, Μέρος 1 (διακομιστή περιοδικό SQL, Φεβρουάριος 2007) παρουσιάζει τρεις μεθόδους που εκτελεί μια τέτοια μετατροπή ( πιο αργή στην ταχύτερη ? Η διαφορά μεταξύ της δεύτερης και της τρίτης μεθόδου είναι μικρή):

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)

Την τεχνική σας (casting για να επιπλέουν ) προτείνεται από έναν αναγνώστη στο τεύχος Απριλίου του περιοδικού. Σύμφωνα με τον ίδιο, έχει απόδοση ανάλογη με εκείνη της δεύτερη τεχνική που παρουσιάστηκε παραπάνω.

Απαντήθηκε 06/08/2008 στις 09:06
πηγή χρήστη

ψήφοι
11

Σας CAST- FLOOR- CASTφαίνεται ήδη να είναι ο βέλτιστος τρόπος, τουλάχιστον για το MS SQL Server 2005.

Κάποιες άλλες λύσεις που έχω δει έχουν μια σειρά μετατροπής, όπως Select Convert(varchar(11), getdate(),101)σε αυτές, η οποία είναι πιο αργή από έναν παράγοντα 10.

Απαντήθηκε 05/08/2008 στις 21:12
πηγή χρήστη

ψήφοι
3

Παρακαλώ προσπάθησε:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
Απαντήθηκε 29/06/2013 στις 10:49
πηγή χρήστη

ψήφοι
0

SQL2005: Θα ήθελα να συστήσω ρίχνει αντί dateadd. Για παράδειγμα,

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

κατά μέσο όρο περίπου 10% πιο γρήγορα στο σύνολο δεδομένων μου, από ό, τι

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

(Και χύτευση σε smalldatetime ήταν ταχύτερη ακόμα)

Απαντήθηκε 05/11/2014 στις 04:26
πηγή χρήστη

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more