SQL सर्वर में डेटटाइम बनाम डेटटाइम




sql-server tsql (12)

कौनसा:

SQL सर्वर 2008+ में दिनांक और समय को संग्रहीत करने का अनुशंसित तरीका है?

मुझे परिशुद्धता (और भंडारण स्थान शायद) में मतभेदों से अवगत है, लेकिन अब उनको अनदेखा कर रहा है, क्या इसका उपयोग करने के लिए सबसे अच्छा अभ्यास दस्तावेज है, या शायद हमें केवल datetime2 उपयोग करना चाहिए?


Select ValidUntil + 1
from Documents

उपर्युक्त एसक्यूएल डेटटाइम 2 फ़ील्ड के साथ काम नहीं करेगा। यह रिटर्न और त्रुटि "ऑपरेंड प्रकार टकराव: डेटाटाइम 2 int के साथ असंगत है"

अगले दिन पाने के लिए 1 जोड़ना कुछ डेवलपर्स वर्षों से तिथियों के साथ कर रहा है। अब माइक्रोसॉफ्ट के पास एक सुपर नया डेटाटाइम 2 फ़ील्ड है जो इस सरल कार्यक्षमता को संभाल नहीं सकता है।

"आइए इस नए प्रकार का उपयोग करें जो पुराने से भी बदतर है", मुझे ऐसा नहीं लगता!


पुराना प्रश्न ... लेकिन मैं कुछ भी जोड़ना चाहता हूं जो पहले से किसी ने भी नहीं बताया है ... (नोट: यह मेरा स्वयं का अवलोकन है, इसलिए किसी संदर्भ के लिए मत पूछें)

फ़िल्टर मानदंडों में उपयोग किए जाने पर डेटाटाइम 2 तेज होता है।

TLDR:

एसक्यूएल 2016 में मेरे पास सौ हजार पंक्तियों और एक डेटाटाइम कॉलम ENTRY_TIME के ​​साथ एक टेबल थी क्योंकि इसे सटीक समय सेकेंड तक स्टोर करना आवश्यक था। कई जॉइन और एक उप क्वेरी के साथ एक जटिल क्वेरी निष्पादित करते समय, जब मैंने इस खंड का उपयोग किया था:

WHERE ENTRY_TIME >= '2017-01-01 00:00:00' AND ENTRY_TIME < '2018-01-01 00:00:00'

क्वेरी शुरू में ठीक थी जब सैकड़ों पंक्तियां थीं, लेकिन जब पंक्तियों की संख्या में वृद्धि हुई, तो क्वेरी ने यह त्रुटि देना शुरू कर दिया:

Execution Timeout Expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.

मैंने जहां खंड हटा दिया, और अप्रत्याशित रूप से, क्वेरी 1 सेकंड में चलाया गया था, हालांकि अब सभी तिथियों के लिए सभी पंक्तियां प्राप्त की गई थीं। मैं खंड के साथ आंतरिक क्वेरी चलाता हूं, और इसमें 85 सेकंड लगते हैं, और बिना किसी खंड के 0.01 सेकेंड लेते हैं।

डेटाटाइम फ़िल्टरिंग प्रदर्शन के रूप में मैं इस मुद्दे के लिए यहां कई धागे में आया था

मैंने थोड़ी सी क्वेरी अनुकूलित की। लेकिन मुझे मिली वास्तविक गति डेटाटाइम कॉलम को डेटाटाइम 2 में बदलकर थी।

अब वही क्वेरी जो पहले की गई थी, एक सेकंड से भी कम समय लेती है।

चियर्स


जबकि datetime2 साथ परिशुद्धता बढ़ी है, कुछ क्लाइंट दिनांक , समय या डेटाटाइम 2 का समर्थन नहीं करते हैं और आपको एक स्ट्रिंग अक्षर में कनवर्ट करने के लिए मजबूर करते हैं। विशेष रूप से माइक्रोसॉफ्ट इन डेटा प्रकारों के साथ "डाउन लेवल" ओडीबीसी, ओएलई डीबी, जेडीबीसी, और एसक्ल क्लाइंट मुद्दों का उल्लेख करता है और एक datetime2 दिखाता है कि प्रत्येक प्रकार किस प्रकार मैप कर सकता है।

यदि परिशुद्धता पर मूल्य संगतता , datetime उपयोग करें


इस आलेख के मुताबिक, यदि आप डेटटाइम 2 का उपयोग कर डेटटाइम की वही सटीकता चाहते हैं तो आपको बस डेटटाइम 2 (3) का उपयोग करना होगा। यह आपको एक ही परिशुद्धता देना चाहिए, एक कम बाइट लेना चाहिए, और विस्तारित सीमा प्रदान करना चाहिए।


मैं @marc_s और @Adam_Poward के साथ सहमत हूं - डेटटाइम 2 आगे बढ़ने वाली पसंदीदा विधि है। इसमें तिथियों की एक विस्तृत श्रृंखला है, उच्च परिशुद्धता है, और बराबर या कम भंडारण का उपयोग करता है (परिशुद्धता के आधार पर)।

चर्चा एक बात याद आई, हालांकि ...
@Marc_s कहता है: Both types map to System.DateTime in .NET - no difference there । यह सही है, हालांकि, उलटा सच नहीं है ... और दिनांक सीमा की खोज करते समय यह महत्वपूर्ण है (उदाहरण के लिए "मुझे 5/5/2010 को संशोधित सभी रिकॉर्ड मिलें")।

DateTime2 .NET के संस्करण में DateTime2 सीमा 2 की समान सीमा और सटीकता है। पुराने एसक्यूएल DateTime .net Datetime मैप करते DateTime एक अंतर्निहित गोलाकार होता है । पुराना एसक्यूएल DateTime 3 मिलीसेकंड के लिए सटीक है। इसका मतलब है कि 11:59:59.997 जितना करीब है जितना आप दिन के अंत तक पहुंच सकते हैं। कुछ भी ऊपर अगले दिन तक गोलाकार है।

इसे इस्तेमाल करे :

declare @d1 datetime   = '5/5/2010 23:59:59.999'
declare @d2 datetime2  = '5/5/2010 23:59:59.999'
declare @d3 datetime   = '5/5/2010 23:59:59.997'
select @d1 as 'IAmMay6BecauseOfRounding', @d2 'May5', @d3 'StillMay5Because2msEarlier'

इस अंतर्निहित दौर से बचने के लिए डेटटाइम 2 पर जाने का एक महत्वपूर्ण कारण है। तारीखों के लागू दौर स्पष्ट रूप से भ्रम का कारण बनता है:


मुझे लगता है कि दिनांक स्टोर करने का बेहतर तरीका DATETIME2 है, क्योंकि इसमें DATETIME की तुलना में अधिक दक्षता है। SQL सर्वर 2008 में आप DATETIME2 का उपयोग कर सकते हैं, यह दिनांक और समय संग्रहीत करता है, स्टोर करने के लिए 6-8 बाइट लेता है और इसमें 100 नैनोसेकंड की सटीकता होती है। तो जो भी अधिक समय परिशुद्धता की आवश्यकता है वह DATETIME2 चाहता है।


यहां एक उदाहरण दिया गया है जो आपको स्टोरेज आकार (बाइट्स) में अंतर और स्मालडटाइम, डेटाटाइम, डेटाटाइम 2 (0), और डेटाटाइम 2 (7) के बीच सटीकता दिखाएगा:

DECLARE @temp TABLE (
    sdt smalldatetime,
    dt datetime,
    dt20 datetime2(0),
    dt27 datetime2(7)
)

INSERT @temp
SELECT getdate(),getdate(),getdate(),getdate()

SELECT sdt,DATALENGTH(sdt) as sdt_bytes,
    dt,DATALENGTH(dt) as dt_bytes,
    dt20,DATALENGTH(dt20) as dt20_bytes,
    dt27, DATALENGTH(dt27) as dt27_bytes FROM @temp

जो लौटता है

sdt                  sdt_bytes  dt                       dt_bytes  dt20                 dt20_bytes  dt27                         dt27_bytes
2015-09-11 11:26:00  4          2015-09-11 11:25:42.417  8         2015-09-11 11:25:42  6           2015-09-11 11:25:42.4170000  8

इसलिए यदि मैं जानकारी को दूसरे स्थान पर संग्रहीत करना चाहता हूं - लेकिन मिलीसेकंड पर नहीं - यदि मैं डेटाटाइम या डेटाटाइम 2 (7) के बजाय डेटाटाइम 2 (0) का उपयोग करता हूं तो मैं प्रत्येक 2 बाइट्स को सहेज सकता हूं।


मैं सिर्फ DATETIME2 लिए एक और लाभ में ठोकर खा रहा हूं: यह पाइथन adodbapi मॉड्यूल में एक बग से adodbapi है, जो एक मानक लाइब्रेरी adodbapi मान पास हो जाता है, जिसमें एक DATETIME कॉलम के लिए गैर-शून्य माइक्रोसेकंड होता है लेकिन कॉलम को परिभाषित किया गया है, तो ठीक काम करता है DATETIME2


डेटाटाइम 2 को छोड़कर अधिकांश पहलुओं में जीत (पुराने ऐप्स संगतता)

  1. मूल्यों की बड़ी रेंज
  2. बेहतर शुद्धता
  3. छोटी भंडारण स्थान (यदि वैकल्पिक उपयोगकर्ता-निर्दिष्ट परिशुद्धता निर्दिष्ट है)

कृपया निम्नलिखित बिंदुओं को नोट करें

  • वाक्य - विन्यास
    • डेटाटाइम 2 [(fractional सेकंड सटीक => भंडारण आकार के नीचे देखो)]
  • प्रेसिजन, पैमाने
    • 100ns की सटीकता के साथ 0 से 7 अंक।
    • डिफ़ॉल्ट परिशुद्धता 7 अंक है।
  • भंडारण का आकार
    • 3 से कम परिशुद्धता के लिए 6 बाइट;
    • परिशुद्धता 3 और 4 के लिए 7 बाइट्स।
    • अन्य सभी परिशुद्धता के लिए 8 बाइट की आवश्यकता होती है
  • डेटटाइम 2 (3) में डेटटाइम के समान अंक हैं, लेकिन 8 बाइट के बजाय स्टोरेज के 7 बाइट्स का उपयोग करते हैं ( SQLHINTS- डेटटाइम बनाम डेटटाइम 2 )
  • डेटाटाइम 2 (ट्रांजैक्ट-एसक्यूएल एमएसडीएन आलेख) पर और जानें

छवि स्रोत: एमसीटीएस सेल्फ-पेस्ड ट्रेनिंग किट (परीक्षा 70-432): माइक्रोसॉफ्ट® एसक्यूएल सर्वर® 2008 - कार्यान्वयन और रखरखाव अध्याय 3: सारणी -> पाठ 1: सारणी बनाना -> पृष्ठ 66


datetime लिए एमएसडीएन दस्तावेज datetime का उपयोग करने की सिफारिश करता है। यहां उनकी सिफारिश है:

नए काम के लिए समय, दिनांक, डेटाटाइम 2 और डेटाटाइमसेट डेटा प्रकार का उपयोग करें। इन प्रकारों को एसक्यूएल मानक के साथ संरेखित करें। वे अधिक पोर्टेबल हैं। समय, डेटाटाइम 2 और डेटाटाइमसेट अधिक सेकंड सटीक प्रदान करते हैं। डेटाटाइमसेट वैश्विक स्तर पर तैनात अनुप्रयोगों के लिए समय क्षेत्र समर्थन प्रदान करता है।

डेटाटाइम 2 में बड़ी तिथि सीमा है, एक बड़ा डिफ़ॉल्ट fractional सटीक, और वैकल्पिक उपयोगकर्ता निर्दिष्ट सटीक है। उपयोगकर्ता द्वारा निर्दिष्ट परिशुद्धता के आधार पर यह कम भंडारण का उपयोग कर सकता है।


लगभग सभी उत्तर और टिप्पणियां विपक्ष पर पेशेवरों और प्रकाश पर भारी रही हैं। यहां तक ​​कि सभी पेशेवरों और विपक्षों का एक पुनरावृत्ति यहां तक ​​कि कुछ महत्वपूर्ण विपक्ष (नीचे # 2 में) मैंने केवल एक बार उल्लेख किया है या नहीं।

  1. पेशेवरों:

1.1। अधिक आईएसओ अनुपालन (आईएसओ 8601) (हालांकि मुझे नहीं पता कि यह अभ्यास में कैसे खेलता है)।

1.2। अधिक रेंज (1/1/0001 से 12/31/9999 बनाम 1/1 / 1753-12 / 31/99 99) (हालांकि अतिरिक्त सीमा, साल 1753 से पहले की सभी, पूर्व में छोड़कर उपयोग नहीं की जाएगी। ऐतिहासिक, खगोलीय, भूगर्भीय, आदि ऐप्स में)।

1.3। सटीक रूप से .NET की DateTime प्रकार की रेंज की सीमा से मेल खाता है (हालांकि दोनों विशेष कोडिंग के साथ आगे और पीछे परिवर्तित होते हैं यदि मान लक्ष्य प्रकार की सीमा के भीतर होते हैं और परिशुद्धता के अलावा नीचे # 2.1 को छोड़कर त्रुटि / राउंडिंग होती है)।

1.4। अधिक सटीकता (100 नैनोसेकंद उर्फ ​​0.000,000,1 सेकंड बनाम 3.33 मिलीसेकंद उर्फ ​​0.003,33 सेकंड।) (हालांकि अतिरिक्त परिशुद्धता का उपयोग पूर्व में छोड़कर, इंजीनियरिंग / वैज्ञानिक ऐप्स में नहीं किया जाएगा)।

1.5। जब इसी तरह के लिए कॉन्फ़िगर किया गया है (जैसा कि 1 मिलीसेक "समान" (3.33 मिलीसेक में) के रूप में इमान अबीदी ने दावा किया है) DateTime रूप में सटीकता, कम जगह (7 बनाम 8 बाइट्स) का उपयोग करती है, लेकिन फिर भी, आप हार जाएंगे परिशुद्धता लाभ जो संभावित रूप से अनियंत्रित लाभों के बावजूद दो (दूसरी सीमा) में से एक है।

  1. कान्स:

2.1। एक .NET SqlCommand पैरामीटर को पास करते समय, आपको System.Data.SqlDbType.DateTime2 निर्दिष्ट करना होगा यदि आप SQL सर्वर DateTime की सीमा और / या परिशुद्धता के बाहर कोई मान गुजर रहे हैं, क्योंकि यह System.Data.SqlDbType.DateTime डिफ़ॉल्ट है System.Data.SqlDbType.DateTime

2.2। संख्यात्मक मूल्यों और ऑपरेटरों का उपयोग करते हुए SQL सर्वर अभिव्यक्तियों में निम्न के साथ / इसके साथ निम्नलिखित करने के लिए अंतर्निहित / आसानी से एक फ़्लोटिंग-पॉइंट न्यूमेरिक (मिनट की तारीख से # दिनों के बाद) मूल्य में परिवर्तित नहीं किया जा सकता है:

2.2.1। # दिनों या आंशिक दिनों को जोड़ें या घटाना। नोट: वर्कअराउंड के रूप में DateAdd फ़ंक्शन का उपयोग करना तुच्छ नहीं है जब आपको दिनांक-समय के सभी हिस्सों में एकाधिक पर विचार करने की आवश्यकता नहीं होती है।

2.2.2। "आयु" गणना के प्रयोजनों के लिए दो दिनांक-समय के बीच अंतर लें। नोट: आप इसके बजाय SQL सर्वर के DateDiff फ़ंक्शन का उपयोग नहीं कर सकते हैं, क्योंकि यह age गणना नहीं करता है क्योंकि अधिकांश लोग उसमें अपेक्षा करते हैं कि यदि दो दिनांक-समय एक कैलेंडर / घड़ी को पार करने वाली इकाइयों की दिनांक-सीमा सीमा पार करने के लिए होता है, उस इकाई का छोटा अंश, यह उस इकाई के बनाम 1 के रूप में अंतर को वापस कर देगा। उदाहरण के लिए, Day की तारीख में DateDiff केवल दो मिलीसेकंड के अलावा 1 बनाम 0 (दिन) वापस आ जाएगा दिनांक-समय विभिन्न कैलेंडर दिनों (यानी "1999-12-31 23: 59: 59.9 99 99 99" और "2000-01-01 00: 00: 00.0000000") पर हैं। वही 1 मिलीसेकंद अंतर दिनांक-समय ले जाया जाता है ताकि वे कैलेंडर दिन पार न करें, दिन के दिनों (दिनों) में "डेटडिफ" वापस कर देंगे।

2.2.3। बस "फ़्लोट" में कनवर्ट करके और फिर DateTime फिर से परिवर्तित करके दिनांक-समय (कुल क्वेरी में) का Avg लें।

नोट: DateTime2 को एक संख्यात्मक रूप में परिवर्तित करने के लिए, आपको निम्न सूत्र जैसा कुछ करना है जो अभी भी मानता है कि आपके मान वर्ष 1 9 70 से कम नहीं हैं (जिसका अर्थ है कि आप सभी अतिरिक्त रेंज और अन्य 217 वर्षों को खो रहे हैं। नोट: आप अतिरिक्त सीमा के लिए अनुमति देने के लिए सूत्र को बस समायोजित करने में सक्षम नहीं हो सकता है क्योंकि आप संख्यात्मक ओवरफ़्लो समस्याओं में भाग ले सकते हैं।

25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0 - स्रोत: " https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html "

बेशक, आप पहले DateTime भी जा सकते हैं (और यदि आवश्यक हो तो DateTime2 2 पर फिर से जरूरी हो), लेकिन आप DateTime2 बनाम DateTime लाभ और परिशुद्धता और सीमा (साल 1753 से पहले) खो देंगे जो कि सबसे बड़ी और सबसे बड़ी हैं साथ ही साथ कम से कम 2 कम से कम संभावित आवश्यकता है जो सवाल पूछता है कि जब आप फ्लोटिंग-पॉइंट न्यूमेरिक (दिनों के # दिनों) के लिए अंतर्निहित / आसान रूपांतरण खो देते हैं तो अतिरिक्त / घटाव / "आयु" (बनाम DateDiff ) / Avg कैल्क्स लाभ जो मेरे अनुभव में एक बड़ा है।

बीटीडब्ल्यू, तिथि-समय का Avg एक महत्वपूर्ण उपयोग केस है (या कम से कम होना चाहिए)। ए) औसत अवधि प्राप्त करने में उपयोग के अलावा, दिनांक-समय (एक सामान्य आधार दिनांक-समय के बाद) अवधि (एक सामान्य अभ्यास) का प्रतिनिधित्व करने के लिए उपयोग किया जाता है, बी) यह औसत तिथि पर डैशबोर्ड-प्रकार आंकड़े प्राप्त करने के लिए भी उपयोगी है- समय पंक्तियों के समूह / समूह के दिनांक-समय कॉलम में है। सी) एक मानक (या कम से कम मानक होना चाहिए) विज्ञापन-कॉल क्वेरी कॉलम में मूल्यों की निगरानी / समस्या निवारण करने के लिए क्वेरी जो कभी भी वैध नहीं हो सकती है और / या बहिष्कृत होने की आवश्यकता हो सकती है, प्रत्येक मान के लिए गणना गणना की जाती है और (यदि उपलब्ध हो) उस मूल्य से जुड़े Min , Avg और Max दिनांक-समय टिकटें।


DATETIME2 की "0001/01/01" की तिथि सीमा "99 99/12/31" है, जबकि DATETIME प्रकार केवल 1753- 99 99 का समर्थन करता है।

इसके अलावा, यदि आपको आवश्यकता है, तो DATETIME2 समय के संदर्भ में अधिक सटीक हो सकता है; DATETIME 3 1/3 मिलीसेकंड तक सीमित है, जबकि DATETIME2 100ns तक सटीक हो सकता है।

System.DateTime लिए दोनों प्रकार मानचित्र। .NET में डेटटाइम - कोई अंतर नहीं है।

यदि आपके पास विकल्प है, तो मैं जब भी संभव हो DATETIME2 का उपयोग करने की अनुशंसा करता हूं। मुझे DATETIME का उपयोग करके कोई लाभ नहीं दिख रहा है (पिछड़े संगतता को छोड़कर) - आपको कम परेशानी होगी (तिथियों की सीमा से बाहर होने और उस तरह की परेशानी के साथ)।

इसके अलावा: अगर आपको केवल तारीख (समय के बिना) की आवश्यकता है, तो DATE का उपयोग करें - यह DATETIME2 जितना अच्छा है और आपको भी स्थान बचाता है! :-) वही समय के लिए जाता है - TIME उपयोग करें। यही कारण है कि इन प्रकार के लिए हैं!







datetime2