Ngày giờ trong SQLite: strftime, date(), datetime() và modifier

SQLite không thật sự có kiểu ngày

Điều này làm khó mọi người chuyển từ Postgres hay MySQL sang. SQLite có năm lớp lưu trữ là NULL, INTEGER, REAL, TEXT, BLOB, và chỉ vậy thôi. Không có DATE, không có DATETIME, không có TIMESTAMP. Bạn có thể viết created_at DATETIME trong CREATE TABLE, và SQLite sẽ chấp nhận, nhưng nó lưu giá trị dưới dạng chuỗi thường hoặc số.

Thứ SQLite thực sự cung cấp là một bộ hàm hiểu được ba định dạng quy ước:

• Chuỗi ISO 8601: '2026-04-23', '2026-04-23 10:15:00', '2026-04-23T10:15:00.123Z'.
• Unix timestamp: số giây tính từ 1970-01-01 UTC, lưu dưới dạng số nguyên.
• Số ngày Julian: số ngày (có phần lẻ) tính từ năm 4714 trước Công nguyên, lưu dưới dạng số thực.

Chọn một và dùng nhất quán. Chuỗi ISO 8601 dễ đọc nhất và sắp xếp đúng khi so sánh như chuỗi, vì thế nó là định dạng mặc định.

SELECT date('now'),
       datetime('now'),
       strftime('%s', 'now'),
       julianday('now');

Bốn cách hỏi "ngay bây giờ": ngày dạng chuỗi, ngày giờ dạng chuỗi, số giây Unix, số ngày Julian. Tất cả đều mô tả cùng một thời điểm.

Năm hàm ngày giờ

SQLite có sẵn năm hàm (function) đáp ứng gần như mọi nhu cầu:

• date(time, ...): trả về YYYY-MM-DD.
• time(time, ...): trả về HH:MM:SS.
• datetime(time, ...): trả về YYYY-MM-DD HH:MM:SS.
• julianday(time, ...): trả về một số thực (rất tiện để tính khoảng cách).
• strftime(format, time, ...): trả về một chuỗi theo định dạng tùy chỉnh.

Mỗi hàm nhận một giá trị thời gian làm đối số đầu tiên, sau đó là bao nhiêu chuỗi modifier tùy ý.

SELECT date('2026-04-23 10:15:00')          AS just_date,
       time('2026-04-23 10:15:00')          AS just_time,
       datetime(1745402100, 'unixepoch')    AS from_unix,
       julianday('2026-04-23')              AS julian;

Để ý 'unixepoch': đó là cách bạn báo cho các hàm ngày giờ biết số nguyên đầu vào là Unix timestamp chứ không phải số ngày Julian. Không có nó, SQLite coi con số là ngày Julian.

strftime: định dạng ngày tùy chỉnh

strftime là hàm làm việc chăm chỉ nhất. Vẫn là các mã % quen thuộc mà bạn biết từ C hay Python:

SELECT strftime('%Y-%m-%d',           'now') AS iso_date,
       strftime('%d/%m/%Y',           'now') AS european,
       strftime('%H:%M',              'now') AS hours_minutes,
       strftime('%Y-%m-%dT%H:%M:%fZ', 'now') AS iso_with_ms,
       strftime('%w',                 'now') AS day_of_week,
       strftime('%j',                 'now') AS day_of_year;

Những mã bạn sẽ dùng nhiều nhất:

• %Y: năm bốn chữ số.
• %m: tháng (01 đến 12).
• %d: ngày trong tháng (01 đến 31).
• %H, %M, %S: giờ, phút, giây.
• %w: thứ trong tuần (0 là Chủ Nhật).
• %j: ngày trong năm (001 đến 366).
• %s: Unix timestamp.
• %f: giây có phần lẻ (SS.SSS).

strftime cũng là cách để tách từng phần của một ngày, vì không có hàm EXTRACT hay YEAR() riêng. Bạn chỉ cần định dạng nó xuống đúng phần mình cần, rồi ép kiểu nếu cần một con số:

SELECT strftime('%Y', '2026-04-23') AS year_text,
       CAST(strftime('%Y', '2026-04-23') AS INTEGER) AS year_num,
       CAST(strftime('%m', '2026-04-23') AS INTEGER) AS month_num;

strftime luôn trả về chuỗi, vì vậy hãy bọc nó trong CAST(... AS INTEGER) khi bạn muốn tính toán hoặc so sánh theo giá trị số.

Modifier: tính toán ngày tháng mà không cần toán tử

Đây là tính năng khiến việc xử lý ngày giờ trong SQLite trở nên dễ chịu. Sau đối số thời gian, bạn có thể truyền bao nhiêu chuỗi modifier tùy ý, và chúng được áp dụng lần lượt:

SELECT date('2026-04-23', '+7 days')              AS week_later,
       date('2026-04-23', '-1 month')             AS month_earlier,
       datetime('now', '+2 hours', '+30 minutes') AS in_two_thirty,
       date('now', 'start of month')              AS first_of_month,
       date('now', 'start of month', '+1 month', '-1 day') AS last_of_month,
       date('now', 'weekday 0')                   AS next_sunday;

Những modifier bạn sẽ dùng liên tục:

• '+N days', '-N days', và tương tự cho hours, minutes, seconds, months, years.
• 'start of day', 'start of month', 'start of year': cắt về mốc đầu ngày, đầu tháng, đầu năm.
• 'weekday N': tiến tới thứ được chỉ định tiếp theo trong tuần (0 là Chủ Nhật).
• 'localtime' và 'utc': chuyển đổi giữa các múi giờ.

Mẹo "ngày cuối tháng", tức đầu tháng, cộng một tháng, trừ một ngày, rất đáng ghi nhớ. SQLite không có hàm LAST_DAY, nhưng nối các modifier cho bạn kết quả tương tự.

Giờ UTC và giờ địa phương

'now' luôn trả về giờ UTC. Nếu muốn giờ địa phương, bạn phải yêu cầu rõ:

SELECT datetime('now')              AS utc_now,
       datetime('now', 'localtime') AS local_now,
       datetime('2026-04-23 10:00:00', 'utc') AS treated_as_local_then_utc;

Modifier 'localtime' chuyển một giá trị UTC sang múi giờ địa phương của hệ thống. Modifier 'utc' làm ngược lại: coi đầu vào là giờ địa phương và chuyển sang UTC.

Thói quen an toàn: lưu mọi thứ theo UTC, chỉ chuyển sang giờ địa phương khi hiển thị. Trộn nhiều múi giờ khi lưu sẽ dẫn đến những bug xuất hiện hai lần mỗi năm khi chuyển giờ mùa hè.

So sánh ngày và lọc theo khoảng

Nếu bạn lưu ngày dưới dạng chuỗi ISO 8601, phép so sánh và BETWEEN tự khắc chạy đúng: ISO 8601 khi sắp theo thứ tự chữ cái cũng chính là thứ tự thời gian. Đó là toàn bộ lý do nó là định dạng mặc định.

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL  -- ISO 8601
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   '2026-04-01 09:30:00'),
    ('Boris', '2026-04-15 14:00:00'),
    ('Carla', '2026-04-23 11:45:00'),
    ('Dmitri','2026-05-02 08:15:00');

SELECT customer, placed_at
FROM orders
WHERE placed_at >= '2026-04-01'
  AND placed_at <  '2026-05-01'
ORDER BY placed_at;

Khoảng nửa mở (>= start, < end) là một thói quen hữu ích: nó loại bỏ hẳn câu hỏi "nửa đêm ngày 30 có được tính vào hay không?".

Với "7 ngày gần nhất", hãy để SQLite tự tính mốc:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   datetime('now', '-2 days')),
    ('Boris', datetime('now', '-10 days')),
    ('Carla', datetime('now', '-1 day')),
    ('Dmitri',datetime('now', '-30 days'));

SELECT customer, placed_at
FROM orders
WHERE placed_at >= datetime('now', '-7 days')
ORDER BY placed_at DESC;

Khoảng cách giữa hai ngày

SQLite không có DATEDIFF. Hai mẫu sau đáp ứng mọi trường hợp:

SELECT julianday('2026-04-23') - julianday('2026-04-01')           AS days_between,
       (julianday('2026-04-23 18:00') - julianday('2026-04-23 09:00')) * 24 AS hours_between,
       strftime('%s', '2026-04-23 18:00') - strftime('%s', '2026-04-23 09:00') AS seconds_between;

Hiệu của julianday() tính bằng ngày (có độ chính xác phần lẻ), nên nhân với 24 cho ra giờ, nhân với 1440 cho ra phút. Hiệu của strftime('%s', ...) tính bằng giây, tiện khi bạn muốn một số nguyên.

CAST(... AS INTEGER) cắt bỏ phần lẻ của ngày nếu bạn muốn đếm số ngày tròn:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   datetime('now', '-2 days')),
    ('Boris', datetime('now', '-10 days')),
    ('Carla', datetime('now', '-1 day')),
    ('Dmitri',datetime('now', '-30 days'));

SELECT customer,
       CAST(julianday('now') - julianday(placed_at) AS INTEGER) AS days_ago
FROM orders
ORDER BY days_ago;

Lưu ngày tháng: chọn một định dạng và giữ nguyên

Ba lựa chọn hợp lý, xếp theo mức độ bạn nên chọn:

1. Chuỗi ISO 8601 (TEXT). Dễ đọc khi xuất dữ liệu, sắp xếp đúng, hợp với mọi hàm ngày giờ. Lựa chọn mặc định.
2. Số giây Unix (INTEGER). Gọn, so sánh nhanh, không mơ hồ về múi giờ. Phù hợp khi bạn có hàng triệu hàng. Bạn sẽ cần datetime(col, 'unixepoch') để đọc lại.
3. Số ngày Julian (REAL). Hiếm khi đáng dùng, trừ khi bạn tính toán ngày tháng nhiều và muốn độ chính xác dưới một giây trong một cột.

Điều bạn không nên làm là trộn nhiều định dạng trong cùng một cột. Các hàm ngày giờ sẽ lặng lẽ chấp nhận cả hai, nhưng chỉ mục, sắp xếp và phép so sánh sẽ cho ra kết quả vô nghĩa.

-- Defaults that auto-fill on insert:
CREATE TABLE events (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    happened_at TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO events (name) VALUES ('signup'), ('login');

SELECT name, happened_at FROM events;

DEFAULT (datetime('now')) là cách viết tương đương DEFAULT CURRENT_TIMESTAMP trong SQLite: nó đóng dấu giờ UTC hiện tại lên mọi hàng mới mà không cần code ứng dụng can thiệp.

Nhóm theo khoảng thời gian

strftime phát huy sức mạnh khi bạn muốn gom các hàng theo tháng, tuần hoặc giờ:

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    customer   TEXT,
    placed_at  TEXT NOT NULL
);

INSERT INTO orders (customer, placed_at) VALUES
    ('Ada',   '2026-04-01 09:30:00'),
    ('Boris', '2026-04-15 14:00:00'),
    ('Carla', '2026-04-23 11:45:00'),
    ('Dmitri','2026-05-02 08:15:00'),
    ('Ezra',  '2026-05-20 17:00:00');

SELECT strftime('%Y-%m', placed_at) AS month,
       COUNT(*)                     AS order_count
FROM orders
GROUP BY month
ORDER BY month;

Cùng ý tưởng cho "đơn hàng theo giờ trong ngày", "lượt đăng ký theo thứ trong tuần" hay "sự kiện theo phút": chọn một chuỗi định dạng chỉ giữ lại đúng mức chi tiết bạn cần, nhóm theo nó và tổng hợp.

Tiếp theo: hàm tổng hợp

Nhân nói về nhóm dữ liệu, COUNT(*) kia là hàm tổng hợp đơn giản nhất của SQLite. Tiếp theo chúng ta sẽ xem đủ bộ: SUM, AVG, MIN, MAX, và cách chúng gộp nhiều hàng thành một giá trị tóm tắt.