SQLite में डेट फ़ंक्शंस की हैकिंग

SQL के Postgres इम्प्लीमेंटेशंस (जैसे Amazon Redshift) में सबसे उपयोगी फ़ंक्शंस में से कुछ हैं DATE_DIFF और DATE_TRUNC:

• DATE_DIFF दो अलग-अलग तारीखों के बीच बीते समय की मात्रा देता है। उदाहरण के लिए, निम्न कोड date1 और date2 के बीच दिनों की संख्या देगा:

DATE_DIFF('day', date1, date2)
संचालित द्वारा

DATE_DIFF साइन-अप से कैंसलेशन तक के दिनों की संख्या या साइन-इन से साइन-आउट तक के घंटों की संख्या निकालने के लिए बेहतरीन है।

• DATE_TRUNC किसी तारीख को निकटतम दिन, सप्ताह, माह, या वर्ष तक काट देता है। उदाहरण के लिए, निम्न कोड टाइमस्टैम्प my_timestamp के लिए निकटतम सोमवार देगा:

DATE_TRUNC('week', my_timestamp)
संचालित द्वारा

DATE_TRUNC डेटा को एग्रीगेट करने के लिए बहुत उपयोगी है। उदाहरण के लिए, हम इसे निकटतम माह की शुरुआत की तारीख तक ट्रंक करके Monthly Active Users (MAU) की संख्या खोजने के लिए उपयोग कर सकते हैं।

लेकिन हर SQL इम्प्लीमेंटेशन में ये शानदार फ़ंक्शंस नहीं होते। हमारे Learn SQL from Scratch कोड के लिए, हम SQLite का उपयोग करते हैं, जो SQL का एक हल्का इम्प्लीमेंटेशन है जिसे एकल Docker इंस्टेंस पर चलाया जा सकता है। SQLite वेबसाइट बैकएंड्स और छोटे प्रोजेक्ट्स के लिए बढ़िया है, लेकिन इसमें मेरे दो पसंदीदा फ़ंक्शंस नहीं हैं। सौभाग्य से, इनके उपाय मौजूद हैं।

DATE_DIFF को एमुलेट करने के लिए, हम juliandate नामक एक कम-ज्ञात फ़ंक्शन का उपयोग कर सकते हैं। Wikipedia के अनुसार, "Julian Day Number (JDN) वह पूर्णांक है जो Julian day count में एक पूरे सौर दिवस को आवंटित किया जाता है, जो Universal time के दोपहर से शुरू होता है, और Julian day number 0 सोमवार, 1 जनवरी, 4713 BC की दोपहर से शुरू होने वाले दिन को दिया गया है"। किसी तारीख को floating point संख्या में बदलकर, हम दो टाइमस्टैम्प्स के बीच का अंतर निकालने के लिए घटाव का उपयोग कर सकते हैं।

हम अपने उत्तर को 24 से गुणा करके घंटों में या 24 * 60 से गुणा करके मिनटों में भी बदल सकते हैं।

हम strftime का उपयोग करके DATE_TRUNC की कुछ कार्यक्षमता को एमुलेट कर सकते हैं। यह फ़ंक्शन किसी टाइमस्टैम्प को दिए गए आउटपुट के साथ एक स्ट्रिंग में बदलता है।

%dमाह का दिन: 00 %fभिन्नात्मक सेकंड: SS.SSS %Hघंटा: 00-24 %jवर्ष का दिन: 001-366 %Jजूलियन डे नंबर %mमाह: 01-12 %Mमिनट: 00-59 %s1970-01-01 से सेकंड %Sसेकंड: 00-59 %wसप्ताह का दिन 0-6 जहाँ Sunday==0 %Wवर्ष का सप्ताह: 00-53 %Yवर्ष: 0000-9999

आम तौर पर, हम इसका उपयोग अलग-अलग टाइमस्टैम्प फॉर्मैट्स जैसे YYYY-MM-DD से MM-DD-YYYY में बदलने के लिए कर सकते हैं:

strftime('%M-%D-%Y', mydate)
संचालित द्वारा

लेकिन हम उपयुक्त बिंदु तक ट्रंक करने के लिए अपना फॉर्मैटिंग चालाकी से चुन सकते हैं। उदाहरण के लिए, अगर हम निकटतम माह तक ट्रंक करना चाहें, तो हम ऐसा कर सकते हैं:

strftime('%M/%Y', mydate)
संचालित द्वारा

या हम निम्न का उपयोग करके निकटतम सप्ताह तक ट्रंक कर सकते हैं:

strftime('%Y-%w', mydate)
संचालित द्वारा

इन दो आसान तरकीबों के साथ, आप Amazon Redshift जैसी बेहतरीन विश्लेषणों में से कुछ के लिए SQLite का उपयोग कर सकते हैं!

यदि आप SQL की बुनियाद के बारे में और सीखना चाहते हैं, तो DataCamp का Intro to SQL for Data Science कोर्स लें और हमारा SQL Tutorial for Beginners देखें।