HN Dibuka: Fitur tanggal/waktu presisi tinggi di SQLite

Ekstensi tanggal/waktu presisi tinggi

SQLite menyediakan fungsi tanggal dasar, tetapi karena dibutuhkan lebih banyak kemampuan, dibuatlah ekstensi tanggal/waktu presisi tinggi bernama sqlean-time. Ekstensi ini menyediakan API yang terstruktur dan beragam fitur.

Catatan. Menambahkan ekstensi ke SQLite sangatlah sederhana. Cukup unduh file dan jalankan satu perintah basis data.

Konsep

Ekstensi ini menggunakan dua jenis nilai: waktu (Time) dan durasi (Duration).

• Waktu (Time): pasangan yang terdiri dari detik dan nanodetik, yang merepresentasikan detik setelah waktu nol (0001-01-01 00:00:00 UTC) dan nanodetik dalam detik saat ini.

  • Waktu dapat disimpan dalam representasi internal ini, yang memungkinkan tanggal miliaran tahun ke masa lalu maupun masa depan direpresentasikan dengan presisi nanodetik.
  • Waktu juga dapat disimpan sebagai detik sejak Unix epoch (1970-01-01 00:00:00 UTC), termasuk milidetik, mikrodetik, dan nanodetik.
  • Waktu selalu disimpan dan diproses dalam UTC, tetapi dapat dikonversi ke offset zona waktu tertentu.

• Durasi (Duration): angka 64-bit dalam satuan nanodetik, yang dapat merepresentasikan nilai hingga sekitar 290 tahun.

Membuat nilai waktu

• Waktu saat ini:

  select time_fmt_iso(time_now());  -- 2024-08-06T21:22:15.431295000Z
  

• Tanggal/waktu tertentu:

  select time_fmt_iso(time_date(2011, 11, 18));  -- 2011-11-18T00:00:00Z
  select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35));  -- 2011-11-18T15:56:35Z
  

Mengekstrak field waktu

Tersedia fungsi-fungsi untuk mengekstrak berbagai field tanggal/waktu:

select 'year  = ' || time_get_year(time_now());
select 'month  = ' || time_get_month(time_now());
select 'day   = ' || time_get_day(time_now());

Waktu Unix

Fungsi untuk membuat nilai waktu dari waktu Unix (waktu setelah 1970-01-01 UTC):

select time_fmt_iso(time_unix(1321631795));  -- 2011-11-18T15:56:35Z

Fungsi untuk mengubah nilai waktu menjadi waktu Unix:

select time_to_unix(time_now());  -- 1722979335

Membandingkan waktu

Fungsi untuk membandingkan nilai waktu:

select time_after(time_now(), time_date(2011, 11, 18));  -- 1
select time_before(time_now(), time_date(2011, 11, 18));  -- 0

Operasi waktu

Fungsi untuk menambahkan durasi ke nilai waktu:

select time_fmt_iso(time_add(time_now(), 24*dur_h()));  -- 2024-08-07T21:22:15.431295000Z

Konstanta durasi:

• dur_us() - 1 mikrodetik
• dur_ms() - 1 milidetik
• dur_s() - 1 detik
• dur_m() - 1 menit
• dur_h() - 1 jam

Pembulatan

Fungsi untuk membulatkan nilai waktu ke presisi field yang ditentukan:

select 'original  = ' || time_fmt_iso(t.v) from t union all
select 'millennium = ' || time_fmt_iso(time_trunc(t.v, 'millennium')) from t;

Pemformatan

Fungsi yang mengembalikan string waktu ISO 8601:

select time_fmt_iso(time_date(2011, 11, 18, 15, 56, 35, 666777888), 3*3600);  -- 2011-11-18T18:56:35.666777888+03:00

Konstanta durasi

Fungsi yang mengembalikan durasi umum dalam nanodetik:

select dur_ns();  -- 1
select dur_us();  -- 1000

Ucapan terima kasih

Ekstensi ini diimplementasikan dalam C, dan dirancang serta diimplementasikan berdasarkan paket waktu pustaka standar Go (BSD 3-Clause License).

Instalasi dan cara penggunaan

1. Unduh rilis terbaru
2. Gunakan di antarmuka baris perintah SQLite:

   sqlite> .load ./time
   sqlite> select time_now();
   

Ringkasan GN⁺

• Ekstensi sqlean-time menambahkan fitur tanggal/waktu presisi tinggi ke SQLite sehingga berbagai operasi waktu dapat dilakukan.
• Waktu dan durasi dapat diproses dalam satuan nanodetik, sehingga perhitungan waktu yang sangat presisi menjadi mungkin.
• Menyediakan beragam fitur pemformatan dan perbandingan waktu sehingga mudah digunakan oleh pengembang.
• Menawarkan jauh lebih banyak kemampuan dibanding fungsi tanggal bawaan SQLite, sehingga berguna untuk proyek yang memerlukan operasi waktu kompleks.

[GN⁺]

Komentar Hacker News

• Pertanyaan apakah ini menangani kasus khusus perubahan zona waktu dan diskontinuitas waktu lokal yang didokumentasikan oleh Jon Skeet

  • Tautan terkait: Stack Overflow
  • Tautan ke video penjelasan 10 menit dari Computerphile: YouTube

• Sebaiknya tidak membangun sendiri library tanggal/waktu maupun kriptografi

  • Edge case yang tak ada habisnya bisa menimbulkan masalah
  • Alasan bersikap skeptis saat menemukan library baru

• Tiga representasi/skala waktu yang berbeda terasa menarik

  • Penasaran dengan use case yang membutuhkan presisi nanodetik selama rentang miliaran tahun
  • Membingungkan bahwa presisi nanodetik hanya memberikan rentang ±290 tahun

• Penting untuk memperjelas apakah menggunakan integer bertanda

  • Jika membaca dokumentasinya, bisa jadi integer bertanda atau bukan
  • Jika integer bertanda, bisa ada beberapa string bit yang merepresentasikan tanggal dan waktu yang sama

• Akan bagus jika SQLite3 memiliki sistem tipe yang dapat diperluas

• Menyebut ini sangat keren sambil menyoroti fitur penting SQLite yang masih kurang

• Berpendapat bahwa database seharusnya melacak satuan

  • Misalnya, harus bisa menyatakan bahwa kolom waktu direpresentasikan sebagai detik float64
  • Database seharusnya bisa mengonversi "2h" menjadi 7200.0 detik dan membandingkannya saat table scan
  • Pernah menulis database SQL khusus yang menangani satuan seperti ini, tetapi belum pernah melihatnya lagi sejak itu
  • Seharusnya bisa menangani semua satuan, bukan hanya waktu, tetapi juga massa, volume, informasi, temperatur, dan lain-lain
  • Database bisa diajari menolak operasi yang tidak bermakna secara matematis agar kesalahan matematika bisa ditangkap lebih awal

• Pertanyaan apakah representasi nanodetik atau tahun di luar rentang nano yang lebih berguna

  • Karena tidak melakukan sains "presisi", nilai nanodetik terasa terbatas
  • Rasanya lebih sering perlu dapat merepresentasikan tanggal historis

• Mengusulkan timestamp Unix gaya golang dalam satuan nanodetik sebagai int64 bertanda

  • Mungkin tidak mencakup jutaan tahun dengan presisi nanodetik, tetapi diragukan apakah itu benar-benar perlu

• Berpendapat bahwa frasa "detik sejak epoch" tidak boleh dipakai kecuali benar-benar bermakna tepat seperti itu

  • Contoh kueri: select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18));