Kiểu Date/Time và bảng STRICT

Dữ liệu sẽ dùng — perf_sales

Ngày tháng được lưu dưới dạng chuỗi hoặc số, và bạn đọc hoặc tính toán chúng bằng các hàm như date() / strftime().

Nửa đầu, bạn sẽ dùng cột sale_date của bảng bán hàng perf_sales để kiểm tra ba định dạng lưu trữ và cách trích xuất giá trị; nửa sau, bạn sẽ tự tạo một bảng STRICT để xem điều gì xảy ra khi bạn chèn một giá trị không khớp với kiểu khai báo.

Trước khi vào bài tập, hãy xem định nghĩa cột và mẫu dữ liệu của bảng perf_sales dùng trong bài này. (Khi bạn chạy đúng, phần giải thích sẽ hiện ra.)

① Chạy PRAGMA table_info(perf_sales); để kiểm tra định nghĩa cột.

② Chạy SELECT * FROM perf_sales LIMIT 5; để xem trước 5 dòng đầu. perf_sales có nhiều dòng, nên luôn thêm LIMIT.

Ba định dạng lưu trữ ngày — chuỗi ISO / julianday / unixepoch

Ngày và giờ có thể được lưu ở ba định dạng chính.

Dù bạn lưu ở định dạng nào, bạn có thể đọc lại dưới dạng 'YYYY-MM-DD' bằng date() hoặc 'YYYY-MM-DD HH:MM:SS' bằng datetime().

Khóa học này dùng chuỗi ISO mặc định vì nó người đọc được và dễ so sánh, và chỉ chuyển sang julianday / unixepoch khi cần thiết.

-- Cùng một ngày trong 3 định dạng (ví dụ chỉ đọc)
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;

-- Chuyển từng định dạng trong 3 định dạng về dạng ngày đọc được
SELECT date(julianday('2024-03-15'))            AS from_jd,
       datetime(1710460800,'unixepoch')         AS from_unix,
       date('2024-03-15')                       AS from_iso;

-- Với chuỗi ISO, so sánh phạm vi hoạt động đúng như so sánh chuỗi thông thường
SELECT '2024-03-15' BETWEEN '2024-01-01' AND '2024-12-31' AS in_2024;  -- 1

Giả sử yêu cầu là: "Với một lần bán trong perf_sales, hiển thị ngày bán của nó song song ở các định dạng chuỗi ISO, julianday và unixepoch."

① Thu hẹp perf_sales về một dòng duy nhất với sale_id = 12345 (vì bảng lớn, luôn giới hạn về một dòng).

② Đọc sale_date của dòng đó nguyên trạng với bí danh iso.

③ Bên cạnh đó, xuất julianday() áp dụng lên cùng sale_date với bí danh jd, và strftime('%s', ...) với bí danh unixep.

Thao tác và tổng hợp ngày với date() và strftime()

• date(value, modifier...): trả về phần ngày dưới dạng 'YYYY-MM-DD', và cho phép bạn tính toán ngày với modifier như '+1 month' / '-7 days' / 'start of month'
• strftime(format, value): trích xuất một phần của ngày dưới dạng chuỗi bằng cách dùng định dạng như %Y (năm), %m (tháng), %d (ngày), %w (thứ trong tuần)
• Kết hợp strftime('%Y', sale_date) với GROUP BY để tổng hợp theo năm
• Vì sale_date được lưu dưới dạng chuỗi ISO, các điều kiện phạm vi như `WHERE sale_date >= '2024-01-01'` hoạt động đúng như so sánh chuỗi thông thường

-- Thao tác ngày (ví dụ chỉ đọc)
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

-- Đếm theo năm (chuỗi ISO, nên rút năm bằng strftime và GROUP BY)
SELECT strftime('%Y', sale_date) AS yr, COUNT(*) AS cnt
FROM perf_sales
GROUP BY yr
ORDER BY yr;

Giả sử yêu cầu là: "Nhóm các lần bán trong perf_sales theo năm và hiển thị số lượng và tổng số tiền cho từng năm." Vì sale_date là chuỗi ISO, dùng strftime để rút năm rồi tổng hợp.

① Rút năm từ sale_date bằng strftime('%Y', ...) và đặt bí danh là yr.

② Xuất số lượng của từng năm với bí danh cnt, và tổng của amount với bí danh total.

③ Nhóm theo năm (yr) và sắp kết quả theo thứ tự năm tăng dần (luôn tổng hợp — bảng có nhiều dòng).

Giả sử yêu cầu là: "Chỉ cho Q1 2024 (1/1 đến 31/3), hiển thị số lượng và số tiền trung bình." Vì sale_date là chuỗi ISO, các điều kiện phạm vi hoạt động đúng như so sánh chuỗi thông thường.

① Từ perf_sales, chỉ giữ các dòng có sale_date giữa 2024-01-01 và 2024-03-31 (bao gồm).

② Xuất số lượng của các dòng đó với bí danh cnt và trung bình của amount với bí danh avg_amount.

③ Đừng nhóm — trả về một dòng tổng hợp duy nhất bao trùm cả thời kỳ.

Bảng STRICT — áp đặt kiểu nghiêm ngặt

Type affinity bạn đã thấy đến giờ chuyển đổi linh hoạt các chuỗi trông giống số.

Ngược lại, khi bạn thêm STRICT ở cuối định nghĩa bảng, bảng đó từ chối lưu các giá trị có kiểu không khớp với kiểu khai báo.

Nếu bạn thử chèn một chuỗi không phải số vào cột số, giá trị không được chuyển đổi ngầm — bạn nhận được lỗi.

Dùng cái này cho các bảng mà bạn muốn bắt nhầm kiểu sớm.

Trong bảng STRICT, các kiểu cột được phép giới hạn ở INTEGER / REAL / TEXT / BLOB / ANY — không cho phép INT và các tên kiểu tự đặt.

Ở bài tập tiếp theo, bạn sẽ tự tạo bảng typed_strict với STRICT, và ở các bài tập sau, bạn sẽ chèn cả giá trị khớp và không khớp để thấy sự khác biệt.

-- Khai báo và dùng bảng STRICT (ví dụ chỉ đọc)
CREATE TABLE IF NOT EXISTS typed_strict(a INTEGER, b TEXT, c REAL) STRICT;

-- Các dòng có kiểu khớp được chèn bình thường
INSERT INTO typed_strict VALUES (1, '2024-03-15', 1.5);

-- Chèn một chuỗi không phải số vào cột a INTEGER
-- không được chuyển đổi ngầm -- INSERT báo lỗi:
--   cannot store TEXT value in INTEGER column typed_strict.a
INSERT INTO typed_strict VALUES ('not-a-number', 'x', 1.0);

Giả sử yêu cầu là: "Tự định nghĩa một bảng với STRICT để tôi có thể kiểm tra hành vi kiểm tra kiểu ở các bài tập sau." Trước hai bài tập kế tiếp (nơi bạn sẽ chèn các dòng khớp và không khớp), hãy tạo bản thân bảng ở đây.

① Đầu tiên chạy DROP TABLE IF EXISTS typed_strict; để dọn dẹp phiên bản trước, sau đó CREATE bảng typed_strict với ba cột — a (INTEGER) / b (TEXT) / c (REAL) — thêm STRICT ở cuối.

② Sau đó chạy PRAGMA table_info(typed_strict); để xác nhận ba cột được định nghĩa như đã khai báo.

Giả sử yêu cầu là: "Thêm một dòng vào bảng typed_strict từ Bài tập 4 với các giá trị khớp với kiểu khai báo của từng cột, và xác nhận nó được lưu."

① Chèn một dòng vào typed_strict (a INTEGER / b TEXT / c REAL): một số nguyên ở a, một chuỗi ngày định dạng ISO ở b, và một số thập phân ở c. Bạn có thể chọn giá trị tùy ý (ví dụ dùng chuỗi giống ngày ở b).

② Sau đó chạy SELECT * FROM typed_strict; để xác nhận dòng đã chèn ở đó.

Hãy kiểm tra điều gì xảy ra khi bạn thử chèn một chuỗi không phải số vào cột a (INTEGER) của typed_strict. Bảng STRICT không chuyển đổi các giá trị sai khớp — nó từ chối, nên INSERT này sẽ báo lỗi. Đây là bài tập cuối cùng của bài viết, và điểm chính là trải nghiệm bản thân lỗi.

① Chạy một INSERT thử đưa một chuỗi không phải số như 'not-a-number' vào cột a của typed_strict.

② Xác nhận thông báo lỗi xuất hiện (trên console này, nhận được lỗi nghĩa là bạn đã làm đúng).