Tipe Tanggal/Waktu dan Tabel STRICT

Data yang akan kita pakai — perf_sales

Tanggal disimpan baik sebagai string maupun sebagai angka, dan kamu membaca atau menghitungnya dengan fungsi seperti date() / strftime().

Di paruh pertama, kamu akan memakai kolom sale_date dari tabel penjualan perf_sales untuk memeriksa tiga format penyimpanan dan cara mengekstrak nilai; di paruh kedua, kamu akan membuat sendiri tabel STRICT untuk melihat apa yang terjadi ketika kamu memasukkan nilai yang tidak cocok dengan tipe yang dideklarasikan.

Sebelum latihan, lihat definisi kolom dan sampel data dari tabel perf_sales yang dipakai di artikel ini. (Jika kamu menjalankannya dengan benar, penjelasan akan muncul.)

① Jalankan PRAGMA table_info(perf_sales); untuk memeriksa definisi kolom.

② Jalankan SELECT * FROM perf_sales LIMIT 5; untuk mengintip 5 baris pertama. perf_sales memiliki banyak baris, jadi selalu tambahkan LIMIT.

Tiga format penyimpanan tanggal — string ISO / julianday / unixepoch

Tanggal dan waktu bisa disimpan dalam tiga format utama.

Format apa pun yang kamu simpan, kamu bisa membacanya kembali sebagai 'YYYY-MM-DD' dengan date() atau sebagai 'YYYY-MM-DD HH:MM:SS' dengan datetime().

Kursus ini memakai string ISO secara default karena mudah dibaca manusia dan mudah dibandingkan, dan hanya mengonversi ke julianday / unixepoch saat diperlukan.

-- Tanggal yang sama dalam 3 format (contoh read-only)
SELECT '2024-03-15'                       AS iso_text,
       julianday('2024-03-15')            AS as_julianday,
       strftime('%s','2024-03-15 00:00:00') AS as_unixepoch;

-- Konversi masing-masing dari 3 format kembali ke tanggal yang bisa dibaca
SELECT date(julianday('2024-03-15'))            AS from_jd,
       datetime(1710460800,'unixepoch')         AS from_unix,
       date('2024-03-15')                       AS from_iso;

-- Dengan string ISO, pembandingan rentang bekerja dengan benar sebagai pembandingan string biasa
SELECT '2024-03-15' BETWEEN '2024-01-01' AND '2024-12-31' AS in_2024;  -- 1

Misalkan kebutuhannya adalah: "Untuk satu penjualan di perf_sales, tampilkan tanggal penjualannya bersebelahan dalam format string ISO, julianday, dan unixepoch."

① Sempitkan perf_sales ke satu baris dengan sale_id = 12345 (karena tabelnya besar, selalu batasi ke satu baris).

② Baca sale_date dari baris itu apa adanya dengan alias iso.

③ Di sebelahnya, keluarkan julianday() yang diterapkan pada sale_date yang sama sebagai jd, dan strftime('%s', ...) sebagai unixep.

Manipulasi dan agregasi tanggal dengan date() dan strftime()

• date(nilai, modifier...): mengembalikan bagian tanggal sebagai 'YYYY-MM-DD', dan membuat kamu bisa melakukan perhitungan tanggal dengan modifier seperti '+1 month' / '-7 days' / 'start of month'
• strftime(format, nilai): mengekstrak sebagian tanggal sebagai string memakai format specifier seperti %Y (tahun), %m (bulan), %d (hari), %w (hari dalam minggu)
• Kombinasikan strftime('%Y', sale_date) dengan GROUP BY untuk mengagregasi berdasarkan tahun
• Karena sale_date disimpan sebagai string ISO, kondisi rentang seperti `WHERE sale_date >= '2024-01-01'` bekerja dengan benar sebagai pembandingan string biasa

-- Manipulasi tanggal (contoh read-only)
SELECT date('2024-03-15','+1 month')      AS plus_1_month,   -- 2024-04-15
       date('2024-03-15','start of month') AS month_start,    -- 2024-03-01
       strftime('%Y',   '2024-03-15')      AS year_part,      -- 2024
       strftime('%Y-%m','2024-03-15')      AS year_month;     -- 2024-03

-- Hitung berdasarkan tahun (string ISO, jadi ekstrak tahun dengan strftime dan GROUP BY)
SELECT strftime('%Y', sale_date) AS yr, COUNT(*) AS cnt
FROM perf_sales
GROUP BY yr
ORDER BY yr;

Misalkan kebutuhannya adalah: "Kelompokkan penjualan di perf_sales berdasarkan tahun dan tampilkan hitungan dan total amount untuk setiap tahun." Karena sale_date adalah string ISO, pakai strftime untuk mengekstrak tahun lalu agregasi.

① Ekstrak tahun dari sale_date dengan strftime('%Y', ...) dan beri alias yr.

② Keluarkan hitungan setiap tahun sebagai cnt, dan sum dari amount sebagai total.

③ Kelompokkan berdasarkan tahun (yr) dan urutkan hasilnya secara menaik berdasarkan tahun (selalu agregasi — tabelnya punya banyak baris).

Misalkan kebutuhannya adalah: "Untuk Q1 2024 (1 Januari sampai 31 Maret) saja, tampilkan hitungan dan rata-rata amount." Karena sale_date adalah string ISO, kondisi rentang bekerja dengan benar sebagai pembandingan string biasa.

① Dari perf_sales, simpan hanya baris di mana sale_date berada di antara 2024-01-01 dan 2024-03-31 (inklusif).

② Keluarkan hitungan baris tersebut sebagai cnt dan rata-rata dari amount sebagai avg_amount.

③ Jangan kelompokkan — kembalikan satu baris agregat yang meliputi seluruh periode.

Tabel STRICT — memberlakukan tipe ketat

Type affinity yang kita lihat sejauh ini mengonversi string yang terlihat seperti angka secara fleksibel.

Sebaliknya, ketika kamu menambahkan STRICT di akhir definisi tabel, tabel itu menolak menyimpan nilai yang tipenya tidak cocok dengan yang dideklarasikan.

Kalau kamu mencoba memasukkan string non-angka ke kolom numerik, nilainya tidak dikonversi secara implisit — kamu mendapat error.

Pakai ini untuk tabel di mana kamu ingin menangkap kekeliruan tipe sedini mungkin.

Di tabel STRICT, tipe kolom yang diizinkan terbatas pada INTEGER / REAL / TEXT / BLOB / ANY — INT dan nama tipe kustom tidak diizinkan.

Di latihan berikutnya kamu akan membuat sendiri tabel typed_strict dengan STRICT, dan di latihan-latihan setelahnya kamu akan memasukkan nilai yang cocok dan tidak cocok untuk melihat perbedaannya.

-- Mendeklarasikan dan memakai tabel STRICT (contoh read-only)
CREATE TABLE IF NOT EXISTS typed_strict(a INTEGER, b TEXT, c REAL) STRICT;

-- Baris dengan tipe yang cocok dimasukkan dengan baik
INSERT INTO typed_strict VALUES (1, '2024-03-15', 1.5);

-- Memasukkan string non-angka ke kolom a INTEGER
-- tidak dikonversi secara implisit -- INSERT-nya error:
--   cannot store TEXT value in INTEGER column typed_strict.a
INSERT INTO typed_strict VALUES ('not-a-number', 'x', 1.0);

Misalkan kebutuhannya adalah: "Mendefinisikan tabel sendiri dengan STRICT supaya bisa memeriksa perilaku pemeriksaan tipe di latihan-latihan selanjutnya." Sebelum dua latihan berikutnya (di mana kamu akan memasukkan baris yang cocok dan tidak cocok), buat tabelnya sendiri di sini.

① Pertama jalankan DROP TABLE IF EXISTS typed_strict; untuk membersihkan versi sebelumnya, lalu CREATE tabel typed_strict dengan tiga kolom — a (INTEGER) / b (TEXT) / c (REAL) — menambahkan STRICT di akhir.

② Lalu jalankan PRAGMA table_info(typed_strict); untuk mengonfirmasi ketiga kolom didefinisikan sesuai deklarasi.

Misalkan kebutuhannya adalah: "Tambahkan satu baris ke tabel typed_strict dari Latihan 4 dengan nilai yang cocok dengan tipe yang dideklarasikan setiap kolom, dan konfirmasi bahwa baris itu tersimpan."

① Masukkan satu baris ke typed_strict (a INTEGER / b TEXT / c REAL): integer di a, string tanggal format ISO di b, dan desimal di c. Kamu bisa memilih nilai sesukamu (misalnya pakai string seperti tanggal di b).

② Lalu jalankan SELECT * FROM typed_strict; untuk mengonfirmasi baris yang dimasukkan ada di sana.

Mari cek apa yang terjadi ketika kamu mencoba memasukkan string non-angka ke kolom a (INTEGER) milik typed_strict. Tabel STRICT tidak mengonversi nilai yang tidak cocok — ia menolaknya, jadi INSERT ini akan error. Ini adalah latihan terakhir di artikel, dan intinya adalah merasakan error itu sendiri.

① Jalankan INSERT yang mencoba memasukkan string non-angka seperti 'not-a-number' ke kolom a milik typed_strict.

② Konfirmasi bahwa pesan error muncul (di konsol ini, mendapatkan error berarti kamu menjawab dengan benar).