डेटाटाइम वैल्यू (एसक्यूएल सर्वर) के समय हिस्से को कैसे हटाएं?

यहां मैं जो उपयोग करता हूं वह यहां है:

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

मैं सोच रहा हूं कि एक बेहतर और अधिक सुरुचिपूर्ण तरीका हो सकता है।

आवश्यकताएँ:

  • इसे यथासंभव तेज़ होना चाहिए (कम कास्टिंग, बेहतर)।
  • अंतिम परिणाम एक डेटाटाइम प्रकार होना चाहिए, स्ट्रिंग नहीं।
0
जोड़ा संपादित
विचारों: 4

5 उत्तर

आपका CAST - फ़्लोर - CAST कम से कम एमएस एसक्यूएल सर्वर 2005 पर इष्टतम तरीका प्रतीत होता है।

मैंने देखा है कि कुछ अन्य समाधानों में एक स्ट्रिंग-रूपांतरण है, जैसे कन्वर्ट का चयन करें (वर्कर (11), getdate (), 101) उनमें, जो 10 के कारक से धीमा है।

0
जोड़ा
हम अपने उत्पादों में से एक में माइकल स्टम द्वारा सुझाई गई विधि का उपयोग करते हैं और यह एक आकर्षण की तरह काम करता है।
जोड़ा लेखक Chris Roberts, स्रोत
यह काफी हद तक इष्टतम तरीका नहीं है। कृपया
जोड़ा लेखक ErikE, स्रोत

एसक्यूएल सर्वर 2008 में एक नया दिनांक डेटा प्रकार और यह इस समस्या को सरल बनाता है:

SELECT CAST(CAST(GETDATE() AS date) AS datetime)
0
जोड़ा

SQL सर्वर 2008 और ऊपर

SQL सर्वर 2008 और ऊपर, निश्चित रूप से सबसे तेज़ तरीका कनवर्ट करें (दिनांक, @डेट) । यदि आवश्यक हो तो इसे डेटाटाइम या डेटाटाइम 2 पर वापस डाला जा सकता है।

SQL सर्वर 2005 और पुराने में वास्तव में सर्वश्रेष्ठ क्या है?

मैंने एसक्यूएल सर्वर में किसी तारीख से समय को कम करने के लिए सबसे तेज़ क्या है, इसके बारे में असंगत दावों को देखा है, और कुछ लोगों ने यह भी कहा कि उन्होंने परीक्षण किया है, लेकिन मेरा अनुभव अलग-अलग रहा है। तो आइए कुछ और कड़े परीक्षण करें और सभी को स्क्रिप्ट दें ताकि अगर मैं कोई गलती करता हूं तो लोग मुझे सही कर सकते हैं।

फ़्लोट रूपांतरण सटीक नहीं हैं

सबसे पहले, मैं डेटाटाइम को 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

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.

अब वास्तविक प्रदर्शन परीक्षण स्क्रिप्ट के लिए। कृपया ध्यान दें कि ग्राहकों को पंक्तियों को वापस नहीं करना उद्देश्यपूर्ण है क्योंकि यह 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;

कुछ रैंपलिंग विश्लेषण

इसके बारे में कुछ नोट्स। सबसे पहले, अगर केवल ग्रुप बाय या तुलना कर रहे हैं, तो डेटाटाइम पर वापस जाने की आवश्यकता नहीं है। तो आप इसे से बचकर कुछ सीपीयू को बचा सकते हैं, जब तक आपको प्रदर्शन उद्देश्यों के लिए अंतिम मूल्य की आवश्यकता न हो। आप अनवरोधित मूल्य से ग्रुप भी कर सकते हैं और केवल चयन खंड में रूपांतरण डाल सकते हैं:

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.

सीपीयू समय पर उन गणनाएं हैं जिन्हें मैंने उपर्युक्त विश्लेषण के लिए उपयोग किया था:

     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.

एक और दिलचस्प बात यह है कि आधार लागत लगभग <�कोड> कनवर्ट (दिनांक) विधि के बराबर होती है (जो लगभग 0 लागत होनी चाहिए, क्योंकि सर्वर आंतरिक रूप से पूर्णांक दिन भाग को आंतरिक रूप से निकाल सकता है डेटाटाइम डेटा प्रकार के पहले चार बाइट्स)।

निष्कर्ष

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).

इस छोटे पूर्ण सुधार को भी देखते हुए, मेरी राय में, DateAdd विधि जीत जाती है क्योंकि यह प्रदर्शन और स्पष्टता का सबसे अच्छा संयोजन है। जिस जवाब के लिए 0.50000004 का "जादू संख्या" आवश्यक है, किसी दिन किसी दिन (पांच शून्य या छः ???) काटने जा रहा है, साथ ही इसे समझना मुश्किल है।

अतिरिक्त नोट्स

जब मुझे कुछ समय मिलता है तो मैं 0.50000004 को '12: 00: 00.003 ' में बदलता हूं और देखता हूं कि यह कैसा करता है। इसे उसी डेटाटाइम मान में परिवर्तित किया जाता है और मुझे याद रखना बहुत आसान लगता है।

रुचि रखने वालों के लिए, उपरोक्त परीक्षण सर्वर पर चलाए गए थे जहां @@ संस्करण निम्न देता है:

माइक्रोसॉफ्ट एसक्यूएल सर्वर 2008 (आरटीएम) - 10.0.1600.22 (इंटेल एक्स 86) जुलाई 9 2008 14:43:34 कॉपीराइट (सी) 1 9 88-2008 विंडोज एनटी 5.2 पर माइक्रोसॉफ्ट कॉर्पोरेशन स्टैंडर्ड संस्करण (बिल्ड 37 9 0: सर्विस पैक 2)

0
जोड़ा
@Gabe धन्यवाद, तय है। चार वर्चर के समान बिल्कुल प्रतीत होता है।
जोड़ा लेखक ErikE, स्रोत
@Roman यदि आप SQL Server 2008 और ऊपर के साथ काम कर रहे हैं, तो हाँ, ऊपर दिए गए मेरे परीक्षणों में दिखाए गए अनुसार date डेटा प्रकार में कनवर्ट करना सबसे तेज़ है।
जोड़ा लेखक ErikE, स्रोत
डाउनवॉटर कृपया टिप्पणी करें? हसना!
जोड़ा लेखक ErikE, स्रोत
@ user3341592 कृपया एक नया प्रश्न पूछें। एक लिंक के साथ टिप्पणी करने के लिए स्वतंत्र महसूस करें।
जोड़ा लेखक ErikE, स्रोत
इसके विपरीत जानना दिलचस्प होगा: केवल समय का हिस्सा रखने का सबसे अच्छा तरीका क्या है?
जोड़ा लेखक user3341592, स्रोत
"एचएच: एमएम: एसएस" और "एचएच: एमएम" रूपों के रूप में समय भाग, यदि यह एक diff बनाता है?
जोड़ा लेखक user3341592, स्रोत
@ErikE, किया। stackoverflow.com/questions/40193719/…
जोड़ा लेखक user3341592, स्रोत
ऐसा लगता है कि आपकी तालिका में एकल और round पीछे है। साथ ही, यदि आप var के बजाय char का उपयोग करते हैं तो समय में कोई अंतर है?
जोड़ा लेखक Gabe, स्रोत
ओरेकल में @ डेनिस, सरल ट्रंक() फ़ंक्शन है। यदि आप दौर का उपयोग करते हैं, तो मुझे लगता है कि आपको उस तारीख को 12 बजे के बाद अगले दिन की बजाय अगले दिन गोलाकार किया जाएगा।
जोड़ा लेखक Rick, स्रोत
+1 बहुत अच्छा जवाब है, लेकिन डेटेडिफ़ की तुलना में तारीख के काम में तेजी से परिवर्तित नहीं होता है?
जोड़ा लेखक Roman Pekar, स्रोत
+1 SQL सर्वर का कौन सा संस्करण आपने इस तरह से परीक्षण किया था?
जोड़ा लेखक Martin Smith, स्रोत
+1 मुझे इस बिंदु को पसंद आया कि फ़्लोट रूपांतरण सटीक नहीं हैं
जोड़ा लेखक A-K, स्रोत
ओरेकल में दोहरी से राउंड (sysdate) का चयन करें और हमें निश्चित रूप से एसक्यूएल सर्वर में इसकी आवश्यकता है।
जोड़ा लेखक Denis Valeev, स्रोत
@ रिक, धन्यवाद! असल में, आपने दिनांक-समय के संदर्भ में इस कीवर्ड के अर्थ के रूप में अपनी आंखें खोलीं।
जोड़ा लेखक Denis Valeev, स्रोत

प्रयास करें:

SELECT CONVERT(VARCHAR(10),[YOUR COLUMN NAME],105) [YOURTABLENAME]
0
जोड़ा

SQL2005: मैं dateadd की बजाय कास्ट की सलाह देते हैं। उदाहरण के लिए,

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

मेरे डेटासेट पर लगभग 10% तेज़ औसत से औसत है

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

(और smalldatetime में कास्टिंग अभी भी तेज था)

0
जोड़ा