Menulis tanggal/waktu presisi tinggi di SQLite: sqlean-time

 (antonz.org)
2 poin oleh GN⁺ 2024-08-16 | 1 komentar | Bagikan ke WhatsApp
  • Jika fungsi tanggal bawaan SQLite terasa kurang, sqlean-time menambahkan tipe Time·Duration dan fungsi tanggal/waktu sebagai ekstensi dengan presisi nanodetik
  • Nilai Time terdiri dari detik sejak 0001-01-01 00:00:00 UTC dan nanodetik di dalam detik saat ini, dan saat disimpan sebagai BLOB 13 byte dapat menangani rentang puluhan miliar tahun ke masa lalu maupun masa depan
  • Penyimpanan sebagai NUMBER 64-bit berbasis Unix epoch juga didukung, tetapi semakin kecil unitnya semakin sempit rentangnya, sehingga unit nanodetik hanya bisa merepresentasikan dari tahun 1678 hingga 2262
  • API mencakup pembuatan, ekstraksi field, konversi Unix time, perbandingan, aritmetika, truncation·rounding, serta formatting·parsing ISO 8601, dan nilainya selalu disimpan serta dihitung dalam UTC
  • Perhitungan kalender mengasumsikan kalender Gregorian dan tidak menangani leap second, jadi untuk perhitungan hari·bulan·tahun sebaiknya gunakan time_add_date() alih-alih time_add() berbasis Duration

Model waktu sqlean-time

  • sqlean-time adalah ekstensi yang menambahkan pemrosesan tanggal/waktu presisi tinggi ke SQLite
  • Ekstensi SQLite bisa ditambahkan dengan mengunduh file lalu menjalankan satu perintah database
  • Ekstensi ini bekerja terutama dengan dua jenis nilai
    • Time: titik waktu tertentu
    • Duration: durasi

Representasi Time dan rentang penyimpanan

  • Time terdiri dari pasangan (seconds, nanoseconds)
    • seconds: integer 64-bit yang menyatakan detik sejak zero time 0001-01-01 00:00:00 UTC
    • nanoseconds: nilai nanodetik dalam detik saat ini, dengan rentang 0-999999999
  • Jika membutuhkan fleksibilitas maksimum, nilai Time bisa disimpan sebagai BLOB 13 byte dalam representasi internalnya
    • Cara ini merepresentasikan tanggal puluhan miliar tahun ke masa lalu dan masa depan dengan presisi nanodetik
  • Juga didukung penyimpanan sebagai NUMBER integer 64-bit untuk detik, milidetik, mikrodetik, dan nanodetik sejak Unix epoch 1970-01-01 00:00:00 UTC
    • Detik: merepresentasikan puluhan miliar tahun ke masa lalu dan masa depan dengan presisi detik
    • Milidetik: merepresentasikan 292 juta tahun di sekitar 1970 dengan presisi milidetik
    • Mikrodetik: dapat merepresentasikan dari tahun -290307 hingga 294246
    • Nanodetik: dapat merepresentasikan dari tahun 1678 hingga 2262
  • Time selalu disimpan dan dihitung dalam UTC
    • Konversi ke offset zona waktu tertentu tetap dimungkinkan
  • Perhitungan kalender selalu mengasumsikan kalender Gregorian
    • Leap second tidak digunakan

Duration dan pembuatan nilai

  • Duration adalah integer 64-bit dalam satuan nanodetik
    • Dapat merepresentasikan durasi hingga sekitar 290 tahun
    • Bisa disimpan sebagai NUMBER
  • Waktu saat ini dapat dibuat dengan time_now()
    • Contoh: time_fmt_iso(time_now()) mengembalikan string ISO seperti 2024-08-06T21:22:15.431295000Z
  • Tanggal/waktu tertentu dibuat dengan time_date()
    • Jika hanya tanggal yang diberikan, hasilnya adalah tengah malam UTC
    • Jam, menit, detik, dan nanodetik bisa ditentukan bersama
    • Jika offset zona waktu diberikan, nilainya dikonversi ke waktu UTC

Ekstraksi field tanggal/waktu

  • Fungsi ekstraksi field individual mengembalikan tahun, bulan, hari, jam, menit, detik, nanodetik, hari dalam pekan, hari ke berapa dalam tahun, tahun ISO, dan pekan ISO
    • Contoh: time_get_year(), time_get_month(), time_get_day(), time_get_hour(), time_get_minute(), time_get_second(), time_get_nano()
  • Fungsi umum time_get() mengekstrak nilai berdasarkan string nama field
    • Contoh yang didukung: millennium, century, decade, year, quarter, month, day
    • Contoh satuan waktu: hour, minute, second, milli, micro, nano
    • Contoh terkait ISO·kalender: isoyear, isoweek, isodow, yearday, weekday
    • Nilai Unix epoch bisa diambil dengan epoch

Konversi Unix time

  • Disediakan fungsi untuk membuat nilai Time dari Unix time
    • time_unix(seconds)
    • time_unix(seconds, nanoseconds)
    • time_milli(milliseconds)
    • time_micro(microseconds)
    • time_nano(nanoseconds)
  • Ada juga fungsi untuk mengubah nilai Time kembali ke Unix time
    • time_to_unix()
    • time_to_milli()
    • time_to_micro()
    • time_to_nano()
  • Sistem operasi keluarga Unix sering mencatat waktu sebagai nilai detik 32-bit, tetapi time_to_unix() mengembalikan nilai 64-bit
    • Berlaku untuk rentang puluhan miliar tahun ke masa lalu dan masa depan
    • time_to_milli() merepresentasikan hingga 292 juta tahun di sekitar 1970
    • time_to_micro() merepresentasikan dari tahun -290307 hingga 294246
    • time_to_nano() merepresentasikan dari tahun 1678 hingga 2262

Perbandingan dan aritmetika

  • Fungsi perbandingan waktu menentukan urutan dua nilai Time
    • time_after(): mengembalikan apakah waktu pertama setelah waktu kedua
    • time_before(): mengembalikan apakah waktu pertama sebelum waktu kedua
    • time_compare(): mengembalikan 1 jika setelah, -1 jika sebelum, dan 0 jika sama
    • time_equal(): mengembalikan apakah dua nilai merepresentasikan waktu yang sama
  • time_add() menambahkan Duration ke nilai Time
    • Duration negatif bisa digunakan untuk pengurangan
    • Dapat digunakan bersama konstanta Duration seperti dur_us(), dur_ms(), dur_s(), dur_m(), dur_h()
  • Untuk menambahkan hari·bulan·tahun, gunakan time_add_date() alih-alih time_add()
    • time_add_date() menambahkan tahun, bulan, dan hari, serta bisa mengurangi dengan nilai negatif
  • time_sub() mengembalikan durasi antara dua nilai Time dalam nanodetik
  • time_since() mengembalikan waktu yang telah berlalu sejak waktu yang ditentukan dalam nanodetik
  • time_until() mengembalikan durasi tersisa hingga waktu yang ditentukan dalam nanodetik

Truncation dan rounding

  • time_trunc() membulatkan ke bawah nilai Time hingga presisi field yang ditentukan
    • Contoh yang didukung: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, milli, micro
    • Misalnya, memotong 2011-11-18T15:56:35.666777888Z ke hour menghasilkan 2011-11-18T15:00:00Z
  • Nilai juga bisa dibulatkan ke bawah ke kelipatan Duration tertentu
    • Contoh: 12*dur_h(), dur_h(), 30*dur_m(), dur_m(), 30*dur_s(), dur_s()
  • time_round() membulatkan ke kelipatan terdekat dari Duration yang ditentukan
    • Contoh: membulatkan 2011-11-18T15:56:35.666777888Z ke dur_h() menghasilkan 2011-11-18T16:00:00Z
    • Membulatkan nilai yang sama ke dur_s() menghasilkan 2011-11-18T15:56:36Z

Formatting dan parsing

  • time_fmt_iso() mengembalikan nilai Time sebagai string ISO 8601
    • Dapat menerima offset zona waktu secara opsional untuk dikonversi ke offset tersebut sebelum diformat
    • Nilai dengan nanodetik direpresentasikan seperti 2011-11-18T15:56:35.666777888Z
    • Jika offset ditentukan, nilainya direpresentasikan seperti 2011-11-18T18:56:35.666777888+03:00
  • time_fmt_datetime(), time_fmt_date(), time_fmt_time() masing-masing mengembalikan string datetime, date, dan time
    • Dapat menerima offset zona waktu secara opsional
  • time_parse() mem-parsing string terformat menjadi nilai Time
    • String ISO 8601 dengan nanodetik dan zona waktu
    • String ISO 8601 dengan nanodetik dan UTC Z
    • String ISO 8601 dengan zona waktu
    • String ISO 8601 UTC
    • Tanggal/waktu UTC dalam format YYYY-MM-DD HH:MM:SS
    • Tanggal UTC dalam format YYYY-MM-DD
    • Waktu UTC dalam format HH:MM:SS
  • Layout yang didukung time_parse() adalah sekumpulan terbatas

Konstanta Duration

  • Disediakan fungsi yang mengembalikan Duration umum dalam nanodetik
    • dur_ns() → 1
    • dur_us() → 1000
    • dur_ms() → 1000000
    • dur_s() → 1000000000
    • dur_m() → 60000000000
    • dur_h() → 3600000000000

Basis implementasi dan instalasi

  • Ekstensi ini diimplementasikan dalam C, tetapi desain dan implementasinya sangat banyak mengacu pada paket time di standard library Go
    • Paket tersebut berlisensi BSD 3-Clause License
  • Instalasi dilakukan dengan mengunduh latest release lalu memuat ekstensi di SQLite CLI
    • Contoh: .load ./time
    • Setelah dimuat, kueri seperti select time_now(); dapat digunakan

Bacaan terkait

1 komentar

 
GN⁺ 2024-08-16
Komentar Hacker News

  • Saya penasaran apakah ini juga menangani kasus-kasus khusus seperti perubahan zona waktu dan diskontinuitas waktu lokal yang pernah dirangkum dengan terkenal oleh Jon Skeet
    https://stackoverflow.com/questions/6841333/why-is-subtracti...
    Computerphile juga menjelaskannya dengan sangat baik dalam video 10 menit
    https://www.youtube.com/watch?v=-5wpm-gesOY
    Sejak lama saya belajar untuk tidak membuat sendiri library tanggal/waktu atau kriptografi. Ada begitu banyak edge case yang bisa menggigit fatal, jadi setiap melihat library baru seperti ini saya cenderung skeptis

    • Library ini sama sekali tidak menangani konsep waktu lokal. Semuanya berbasis waktu UTC, dan pengguna memang bisa memberikan offset zona waktu, tetapi bagian sulit untuk menghitung offset zona waktu harus dilakukan oleh pemanggil
      Menurut saya dokumentasinya bisa dibuat sedikit lebih jelas. Penulis menyebut “time zones”, tetapi library ini sebenarnya hanya menangani offset zona waktu. Zona waktu itu seperti America/New_York, sedangkan offset zona waktu adalah selisih dari UTC. New York hari ini -14400 detik, tetapi beberapa bulan lagi menjadi -18000 detik karena perubahan daylight saving time

  • Tiga representasi/ukuran waktu yang berbeda ini menarik. Misalnya, saya tidak tahu use case apa yang membutuhkan presisi nanodetik dalam rentang miliaran tahun
    Yang lebih membingungkan, granularitas waktunya sangat ekstrem halus, tetapi presisi nanodetik pada duration hanya memiliki rentang ±290 tahun

    • Begitu memutuskan memakai presisi nanodetik, representasi 64-bit hanya bisa menampung 584 tahun, dan itu tidak cukup. Setidaknya perlu 2 bit tambahan untuk merepresentasikan tahun 2024
      Namun kalau sudah menambahkan 2 bit, tidak ada alasan untuk tidak menambahkan 16 atau 32 bit. Dengan begitu, semua orang bisa tercakup, mulai dari yang menghitung waktu yang ditempuh cahaya untuk bergerak 30 cm sampai yang menghitung usia alam semesta
      Saya membayangkan keputusan desainnya mungkin mengalir seperti itu :)
      Tentu saja sulit memberikan akurasi sub-detik tanpa dukungan leap second, dan apa arti dukungan leap second sebelum peradaban manusia juga agak kabur
    • Cara ini sangat cocok untuk saya dan ribuan developer Go lainnya. Karena itu saya memilih pendekatan ini

  • Sedikit menyimpang tetapi masih terkait: database seharusnya melacak satuan. Jika ada kolom waktu, misalnya, kita seharusnya bisa mendeklarasikannya sebagai duration dalam satuan detik float64
    Lalu bisa menulis SELECT * FROM my_table WHERE duration_s >= 2h, dan database otomatis mengubah “2h” menjadi 7200.0 detik serta membandingkan satuan yang sama saat table scan
    Beberapa tahun lalu saya pernah membuat database SQL tujuan khusus yang punya penanganan satuan native seperti ini, tetapi sebelum maupun sesudahnya saya belum pernah melihatnya, dan ini terasa seperti celah dalam ekosistem UI
    Ini juga tidak perlu terbatas pada waktu. Seharusnya bisa menangani seluruh daftar satuan seperti massa, volume, jumlah informasi, suhu, dan sebagainya. Database juga bisa menolak ekspresi yang tidak masuk akal secara matematis seperti SELECT 2h + 15kg -- type error!
    Ini akan sangat membantu menangkap kesalahan analisis sejak awal

  • Menurut saya penting untuk menyatakan apakah memakai integer bertanda atau tidak. Dari membaca dokumentasinya, kelihatannya mungkin bertanda, tetapi bisa juga tidak
    Jika integer bertanda, bisa ada beberapa bit string yang merepresentasikan tanggal dan waktu yang sama, dan itu tidak bagus

    • Jelas bertanda. Tertulis “untuk mengurangi, gunakan duration negatif”
      Namun pola bit adalah urusan internal library. Kalau bisa menemukan bug di kode, tentu saja tunjukkan, dan kalau bisa usulkan juga perbaikannya
    • Bagaimana mungkin ada “beberapa bit string yang merepresentasikan tanggal dan waktu yang sama”?

  • Akan sangat bagus kalau SQLite3 punya sistem tipe yang dapat diperluas

    • Sebagai orang yang pernah sedikit berkontribusi ke PostgreSQL: jangan, ini tidak boleh dilakukan!!!!
      Sistem tipe yang dapat diperluas sangat buruk bagi performa pengguna akhir database. Dengan begitu, tidak ada apa pun dalam parsing dan optimisasi query yang bisa diproses lewat jalan pintas. Harus terus melakukan lookup katalog sistem query untuk memeriksa tipe semua operand, mencari implementasi operator yang benar, mencari family/class operator indeks yang benar, dan seterusnya
      Input/output nilai juga melewati fungsi yang disimpan di katalog sistem. Bahkan select 1 tidak bisa dijawab tanpa melihat katalog sistem
      Seharusnya ada sekumpulan tipe bawaan yang tepat dan cara komposisi seperti struct/JSON. Sebagian besar database selain PostgreSQL bekerja seperti ini, dan saya sangat yakin ini arah yang benar

  • Ini pertanyaan ala Ask HN yang agak malas, tetapi dari pengalaman, mana yang lebih berguna atau bernilai? Representasi nanodetik, atau representasi tahun di luar rentang nanodetik seperti 1678–2200?
    Saya tidak melakukan pekerjaan ilmiah serius, jadi nilai nanodetik tampaknya terbatas pada eksperimen yang sangat cerdas atau pelacakan transaksi finansial dengan rentang yang lebih sempit
    Sebaliknya, kemampuan merepresentasikan tanggal historis sepertinya akan lebih sering dibutuhkan. Bagaimana menurut kalian?

    • Tanggal historis jelas lebih penting
      Menurunkan presisi hanya menjadi 10 nanodetik saja sudah memberi rentang yang cukup untuk praktik nyata
    • Ini mirip bertanya mana yang lebih berguna antara palu dan obeng. Tergantung pekerjaannya

  • Saya penasaran kenapa tidak memakai gaya Go, yaitu Unix timestamp sebagai signed int64 dalam satuan nanodetik. Memang tidak akan mencakup jutaan tahun dengan presisi nanodetik, tetapi apakah itu benar-benar diperlukan?

    • Dengan presisi dan ukuran itu, hanya bisa mencakup tahun 1678 sampai 2262, sehingga kemampuan merepresentasikan tanggal dan waktu historis sangat terbatas
    • Menyimpan Unix timestamp dalam nanodetik bukan gaya Go, tetapi ekstensi ini memang bisa melakukannya
      select time_to_nano(time_now());
      -- 1722979335431295000

  • Saya berharap istilah seperti “detik sejak epoch” hanya dipakai ketika memang persis berarti itu
    Saya penasaran apa yang akan dikembalikan oleh select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18));

    • Mengapa kamu berharap begitu?
      Ada beberapa alasan yang masuk akal yang terpikir, tetapi yang benar-benar penting hanyalah “epoch yang mana?” Pada sistem berbasis UNIX atau sistem yang mencoba meniru perilakunya, itu terdefinisi dengan baik. Namun karena kamu tidak mengatakan apa keluhannya, sulit untuk membantah atau membenarkan mengapa keadaannya seperti sekarang
      time_date(1311, 11, 18) tidak terdefinisi pada epoch yang digunakan sebagian besar sistem komputer, jadi hasilnya bisa apa saja. Bisa MAX_INT, MIN_INT, 0, nilai yang tampak masuk akal tetapi tidak mencerminkan reformasi kalender, nilai yang dikonversi ke epoch lain untuk menghitung jumlah detik yang akurat, dan sebagainya. Sebelum GMT/UTC, semuanya adalah waktu lokal, jadi bisa juga berargumen bahwa tidak ada epoch yang valid
      Tentu saja, apakah nilai negatif harus didukung bisa diperdebatkan dari kedua sisi. Kita bisa berharap tepat 24 jam sebelum 1970-1-1 0:00:00 UTC adalah -86400, tetapi kata “since” sangat mengisyaratkan hanya nilai positif
      Orang lain mungkin punya epoch yang sama sekali berbeda karena alasan lain, dan selama semua orang dalam domain penggunaannya sepakat, itu juga tidak masalah
      Atau ada alasan keberatan lain?
    • Tertulis “Jika hasilnya melebihi nilai maksimum yang bisa disimpan dalam Duration, duration maksimum akan dikembalikan”