SQLite में उच्च-precision date/time का उपयोग: sqlean-time

 (antonz.org)
2 पॉइंट द्वारा GN⁺ 2024-08-16 | 1 टिप्पणियां | WhatsApp पर शेयर करें
  • अगर SQLite के default date functions पर्याप्त नहीं हैं, तो sqlean-time एक extension के रूप में nanosecond precision वाले Time·Duration types और date·time functions जोड़ता है
  • Time value 0001-01-01 00:00:00 UTC के बाद के seconds और मौजूदा second के भीतर nanoseconds से बनी होती है, और 13-byte BLOB के रूप में store करने पर past और future के अरबों वर्षों की range संभाल सकती है
  • Unix epoch के आधार पर 64-bit NUMBER storage भी संभव है, लेकिन unit जितनी छोटी होती है range उतनी घटती है; nanosecond unit में केवल 1678 से 2262 तक represent किया जा सकता है
  • API में creation, field extraction, Unix time conversion, comparison, arithmetic, truncation·rounding, ISO 8601 formatting·parsing शामिल हैं, और values हमेशा UTC में store व operate की जाती हैं
  • Calendar calculation Gregorian calendar को मानकर चलते हैं और leap seconds handle नहीं करते, इसलिए day·month·year calculations के लिए Duration-based time_add() के बजाय time_add_date() का उपयोग करना चाहिए

sqlean-time का time model

  • sqlean-time SQLite में high-precision date·time processing जोड़ने वाला extension है
  • SQLite extension को file download करके और database command चलाकर add किया जा सकता है
  • Extension दो तरह की values के इर्द-गिर्द काम करता है
    • Time: कोई खास instant
    • Duration: अवधि

Time representation और storage range

  • Time (seconds, nanoseconds) pair से बना होता है
    • seconds: zero time यानी 0001-01-01 00:00:00 UTC के बाद के seconds दिखाने वाला 64-bit integer
    • nanoseconds: मौजूदा second के भीतर nanosecond value, जिसकी range 0-999999999 है
  • अगर maximum flexibility चाहिए, तो Time value को internal representation यानी 13-byte BLOB के रूप में store किया जा सकता है
    • यह तरीका past और future के अरबों वर्षों की dates को nanosecond precision के साथ represent करता है
  • Unix epoch यानी 1970-01-01 00:00:00 UTC के बाद के seconds, milliseconds, microseconds, nanoseconds को 64-bit integer NUMBER के रूप में store करने का तरीका भी supported है
    • seconds: past·future के अरबों वर्षों को second precision में represent करता है
    • milliseconds: 1970 के आगे-पीछे 292 million years को millisecond precision में represent करता है
    • microseconds: -290307 वर्ष से 294246 वर्ष तक represent करता है
    • nanoseconds: 1678 वर्ष से 2262 वर्ष तक represent करता है
  • Time हमेशा UTC में store और operate किया जाता है
    • किसी specific timezone offset के साथ conversion संभव है
  • Calendar calculations हमेशा Gregorian calendar मानते हैं
    • leap seconds का उपयोग नहीं किया जाता

Duration और value creation

  • Duration nanosecond unit वाला 64-bit integer है
    • यह लगभग 290 वर्षों तक की अवधि represent कर सकता है
    • इसे NUMBER के रूप में store किया जा सकता है
  • Current instant time_now() से बनाया जा सकता है
    • उदाहरण: time_fmt_iso(time_now()) 2024-08-06T21:22:15.431295000Z जैसी ISO string return करता है
  • Specific date·time time_date() से create किया जाता है
    • सिर्फ date specify करने पर midnight UTC बनता है
    • hour·minute·second और nanosecond साथ में specify किए जा सकते हैं
    • timezone offset pass करने पर UTC instant में convert हो जाता है

Date·time fields extraction

  • Individual field extraction functions year, month, day, hour, minute, second, nanosecond, weekday, day of year, ISO year, ISO week return करते हैं
    • उदाहरण: time_get_year(), time_get_month(), time_get_day(), time_get_hour(), time_get_minute(), time_get_second(), time_get_nano()
  • General-purpose function time_get() field name string से value extract करता है
    • Supported उदाहरण: millennium, century, decade, year, quarter, month, day
    • Time unit उदाहरण: hour, minute, second, milli, micro, nano
    • ISO·calendar related उदाहरण: isoyear, isoweek, isodow, yearday, weekday
    • Unix epoch value epoch से लाई जा सकती है

Unix time conversion

  • Unix time से Time value बनाने के functions उपलब्ध हैं
    • time_unix(seconds)
    • time_unix(seconds, nanoseconds)
    • time_milli(milliseconds)
    • time_micro(microseconds)
    • time_nano(nanoseconds)
  • Time value को वापस Unix time में बदलने वाले functions भी हैं
    • time_to_unix()
    • time_to_milli()
    • time_to_micro()
    • time_to_nano()
  • Unix-like operating systems अक्सर time को 32-bit seconds value के रूप में record करते हैं, लेकिन time_to_unix() 64-bit value return करता है
    • past·future के अरबों वर्षों की range में valid है
    • time_to_milli() 1970 के आगे-पीछे 292 million years तक represent करता है
    • time_to_micro() -290307 वर्ष से 294246 वर्ष तक represent करता है
    • time_to_nano() 1678 वर्ष से 2262 वर्ष तक represent करता है

Comparison और arithmetic

  • Time comparison functions दो Time values का order determine करते हैं
    • time_after(): return करता है कि पहला time दूसरे time के बाद है या नहीं
    • time_before(): return करता है कि पहला time दूसरे time से पहले है या नहीं
    • time_compare(): बाद में हो तो 1, पहले हो तो -1, समान हो तो 0 return करता है
    • time_equal(): return करता है कि दोनों values एक ही instant को represent करती हैं या नहीं
  • time_add() Time value में Duration जोड़ता है
    • Negative Duration का उपयोग करने पर subtract किया जा सकता है
    • dur_us(), dur_ms(), dur_s(), dur_m(), dur_h() जैसे Duration constants साथ में use किए जा सकते हैं
  • day·month·year जोड़ते समय time_add() नहीं, बल्कि time_add_date() का उपयोग करना चाहिए
    • time_add_date() year, month, day जोड़ता है और negative values से subtract किया जा सकता है
  • time_sub() दो Time values के बीच की अवधि nanoseconds में return करता है
  • time_since() specified instant के बाद elapsed time nanoseconds में return करता है
  • time_until() specified instant तक remaining duration nanoseconds में return करता है

Truncation और rounding

  • time_trunc() Time value को specified field precision तक round down करता है
    • Supported उदाहरण: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, milli, micro
    • उदाहरण के लिए 2011-11-18T15:56:35.666777888Z को hour से truncate करने पर 2011-11-18T15:00:00Z बनता है
  • Specified Duration के multiple तक भी round down किया जा सकता है
    • उदाहरण: 12*dur_h(), dur_h(), 30*dur_m(), dur_m(), 30*dur_s(), dur_s()
  • time_round() specified Duration के nearest multiple तक round करता है
    • उदाहरण: 2011-11-18T15:56:35.666777888Z को dur_h() से round करने पर 2011-11-18T16:00:00Z बनता है
    • वही value dur_s() से round करने पर 2011-11-18T15:56:36Z बनती है

Formatting और parsing

  • time_fmt_iso() Time value को ISO 8601 string के रूप में return करता है
    • Timezone offset को optional रूप से लेकर उस offset में convert करने के बाद format कर सकता है
    • Nanoseconds वाली value 2011-11-18T15:56:35.666777888Z की तरह represent होती है
    • Offset specify करने पर 2011-11-18T18:56:35.666777888+03:00 की तरह represent होती है
  • time_fmt_datetime(), time_fmt_date(), time_fmt_time() क्रमशः datetime, date, time strings return करते हैं
    • Timezone offset optional रूप से लिया जा सकता है
  • time_parse() formatted string को Time value में parse करता है
    • ISO 8601 nanoseconds और timezone वाली string
    • ISO 8601 nanoseconds और UTC Z string
    • ISO 8601 timezone वाली string
    • ISO 8601 UTC string
    • YYYY-MM-DD HH:MM:SS format की UTC date·time
    • YYYY-MM-DD format की UTC date
    • HH:MM:SS format का UTC time
  • time_parse() द्वारा supported layouts limited set हैं

Duration constants

  • Common Duration को nanoseconds में return करने वाले functions उपलब्ध हैं
    • dur_ns() → 1
    • dur_us() → 1000
    • dur_ms() → 1000000
    • dur_s() → 1000000000
    • dur_m() → 60000000000
    • dur_h() → 3600000000000

Implementation base और installation

  • Extension C में implement किया गया है, लेकिन design और implementation Go standard library के time package पर काफी हद तक based हैं
    • वह package BSD 3-Clause License के तहत है
  • Installation latest release download करके SQLite CLI में extension load करने के तरीके से की जाती है
    • उदाहरण: .load ./time
    • Load करने के बाद select time_now(); जैसी queries use की जा सकती हैं

संबंधित पढ़ाई

1 टिप्पणियां

 
GN⁺ 2024-08-16
Hacker News पर राय

  • सोच रहा हूँ कि क्या यह Jon Skeet द्वारा मशहूर तरीके से समझाए गए time zone बदलावों और local time discontinuity जैसे edge cases को भी संभालता है
    https://stackoverflow.com/questions/6841333/why-is-subtracti...
    Computerphile ने भी इसे 10 मिनट के वीडियो में बहुत अच्छे से समझाया है
    https://www.youtube.com/watch?v=-5wpm-gesOY
    बहुत पहले मैंने सीख लिया था कि date/time या cryptography libraries खुद नहीं बनानी चाहिए। ऐसे edge cases अंतहीन हैं जहाँ चीज़ें बुरी तरह काट सकती हैं, इसलिए ऐसी नई library देखकर मैं थोड़ा संशय में रहता हूँ

    • यह library local time की अवधारणा से बिल्कुल नहीं निपटती। सब कुछ UTC-आधारित time है, और user time zone offset दे सकता है, लेकिन time zone offset calculate करने वाला कठिन हिस्सा caller को ही करना पड़ता है
      मेरे हिसाब से docs थोड़े और साफ़ हो सकते हैं। लेखक “time zones” कहते हैं, लेकिन library असल में सिर्फ़ time zone offsets से निपटती है। time zone America/New_York जैसी चीज़ है, और time zone offset UTC से अंतर है। New York आज -14400 seconds है, लेकिन daylight saving time बदलाव के कारण कुछ महीनों बाद -18000 seconds हो जाएगा

  • तीन अलग-अलग time representations/sizes दिलचस्प हैं। उदाहरण के लिए, अरबों साल की range में nanosecond precision की ज़रूरत किस use case में पड़ेगी, समझ नहीं आता
    और भी confusing बात यह है कि time granularity बेहद fine है, लेकिन time duration में nanosecond precision की range सिर्फ़ ±290 साल तक सीमित है

    • एक बार अगर nanosecond precision इस्तेमाल करने का फैसला कर लिया, तो 64-bit representation में सिर्फ़ 584 साल ही आ सकते हैं, और वह पर्याप्त नहीं है। 2024 को represent करने के लिए कम से कम 2 bits और चाहिए
      लेकिन अगर 2 bits जोड़ ही रहे हैं, तो 16 या 32 bits जोड़ने से रोकता क्या है। फिर 30 cm तक light को travel करने में लगने वाला time calculate करने वाले लोगों से लेकर universe की age calculate करने वालों तक सब cover हो जाते हैं
      मेरा अंदाज़ा है कि design decision शायद कुछ इसी तरह आगे बढ़ा होगा :)
      बेशक leap seconds support के बिना sub-second accuracy देना मुश्किल है, और human civilization से पहले के leap seconds support का मतलब ही क्या होगा, यह भी अस्पष्ट है
    • यह तरीका मेरे और हज़ारों दूसरे Go developers के लिए बहुत अच्छा रहा है। इसलिए मैंने यह approach चुना

  • संबंधित लेकिन अलग बात: databases को units track करनी चाहिए। अगर time column है, तो उदाहरण के लिए उसे float64 seconds में duration के रूप में declare कर पाना चाहिए
    तब SELECT * FROM my_table WHERE duration_s >= 2h जैसा लिख सकें, और database अपने-आप “2h” को 7200.0 seconds में बदलकर table scan के दौरान same units की तुलना करे
    कुछ साल पहले मैंने native unit handling वाला एक special-purpose SQL database बनाया था, लेकिन उसके पहले या बाद में ऐसा नहीं देखा, और यह UI ecosystem में एक gap जैसा लगता है
    यह सिर्फ़ time तक सीमित होने की ज़रूरत नहीं है। mass, volume, information amount, temperature आदि पूरी units list handle कर पाना चाहिए। SELECT 2h + 15kg -- type error! जैसे mathematically nonsensical expressions को भी database reject कर सके
    इससे analysis errors को शुरुआत में पकड़ने में बहुत मदद मिलेगी

  • मेरे हिसाब से यह साफ़ बताना ज़रूरी है कि signed integers इस्तेमाल हो रहे हैं या नहीं। docs पढ़ने पर लगता है कि शायद signed हैं, लेकिन शायद नहीं भी हो सकते
    अगर signed integers हैं, तो same date और time को represent करने वाली multiple bit strings बन सकती हैं, और यह अच्छा नहीं है

    • निश्चित रूप से signed है। इसमें लिखा है कि “subtract करने के लिए negative duration इस्तेमाल करें”
      हालांकि bit patterns library की internal समस्या हैं। अगर code में bug मिल सके तो ज़रूर point out करें, और संभव हो तो fix भी suggest करें
    • “same date और time को represent करने वाली multiple bit strings” कैसे संभव है?

  • काश SQLite3 में extensible type system होता

    • PostgreSQL में थोड़ा contribute कर चुके व्यक्ति के रूप में कहूँ तो: नहीं, यह नहीं करना चाहिए!!!!
      extensible type system database end-user performance के लिए बेहद खराब है। तब query parsing और optimization में किसी भी चीज़ को shortcut से handle नहीं किया जा सकता। हर operand का type check करना, सही operator implementation ढूँढना, सही index operator family/class ढूँढना वगैरह के लिए query system catalog को बार-बार lookup करना पड़ता है
      values का input/output भी system catalog में stored functions से गुजरता है। select 1 तक का जवाब system catalog देखे बिना नहीं दिया जा सकता
      उचित built-in types का set और structs/JSON जैसे composition तरीके होने चाहिए। PostgreSQL को छोड़कर ज़्यादातर databases इसी तरह हैं, और मैं दृढ़ता से मानता हूँ कि यही सही दिशा है

  • यह थोड़ा lazy Ask HN-style सवाल है, लेकिन experience में क्या ज़्यादा उपयोगी या valuable है? nanosecond representation, या फिर 1678~2200 जैसी nanosecond range से बाहर के years को represent करना?
    मैं proper scientific work नहीं करता, इसलिए nanoseconds की value बहुत clever experiments या अधिक narrow range वाली financial transaction tracking तक सीमित लगती है
    वहीं historical dates को represent कर पाने की क्षमता ज़्यादा बार ज़रूरी लगती है। आप क्या सोचते हैं?

    • historical dates निश्चित रूप से ज़्यादा महत्वपूर्ण हैं
      precision को सिर्फ़ 10 nanoseconds तक घटाने पर भी practical तौर पर पर्याप्त range मिल जाती है
    • यह पूछने जैसा है कि hammer और screwdriver में से कौन ज़्यादा useful है। काम पर निर्भर करता है

  • समझ नहीं आता कि Go style की तरह Unix timestamp को nanoseconds में signed int64 के रूप में क्यों नहीं इस्तेमाल करते। nanosecond precision के साथ लाखों साल cover नहीं होंगे, लेकिन क्या सच में इसकी ज़रूरत है?

    • उस precision और size पर यह सिर्फ़ 1678 से 2262 तक cover कर सकता है, इसलिए historical dates और times को represent करने की क्षमता काफी सीमित हो जाती है
    • Unix timestamp को nanoseconds में store करना Go style नहीं है, लेकिन इस extension से ऐसा किया जा सकता है
      select time_to_nano(time_now());
      -- 1722979335431295000

  • “seconds since epoch” जैसे expression तभी इस्तेमाल किए जाएँ जब उनका मतलब ठीक वही हो
    सोच रहा हूँ कि select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18)); क्या return करेगा

    • आप ऐसा क्यों चाहते हैं?
      कुछ plausible कारण तो सोच में आते हैं, लेकिन सच में महत्वपूर्ण चीज़ सिर्फ़ “कौन-सा epoch?” है। UNIX-based systems या उनके behavior की नकल करने वाले systems में यह well-defined है। लेकिन आपने अपनी शिकायत क्या है यह नहीं बताया, इसलिए यह बताना मुश्किल है कि अभी जैसा है उसका विरोध या justification कैसे किया जाए
      time_date(1311, 11, 18) ज़्यादातर computer systems द्वारा इस्तेमाल किए जाने वाले epoch में defined नहीं है, इसलिए कोई भी result संभव है। MAX_INT, MIN_INT, 0, कोई plausible value जो calendar reforms reflect नहीं करती, किसी दूसरे epoch में convert करके exact seconds calculate किया गया value—कुछ भी हो सकता है। GMT/UTC से पहले सब local time था, इसलिए यह भी कहा जा सकता है कि कोई valid epoch नहीं है
      बेशक negative values support करनी चाहिए या नहीं, इस पर दोनों तरफ़ argument हो सकते हैं। 1970-1-1 0:00:00 UTC से ठीक 24 घंटे पहले -86400 होने की उम्मीद की जा सकती है, लेकिन “since” strongly positive values का संकेत देता है
      दूसरों के पास अलग कारणों से पूरी तरह अलग epoch हो सकता है, और अगर इस्तेमाल की domain में सभी सहमत हैं तो वह भी ठीक है
      या आपकी कोई और आपत्ति थी?
    • लिखा है, “अगर result Duration में store किए जा सकने वाले maximum value से अधिक हो, तो maximum duration return किया जाता है”