HN công khai: Chức năng ngày/giờ độ chính xác cao trong SQLite

Tiện ích mở rộng ngày/giờ độ chính xác cao

SQLite cung cấp các hàm ngày tháng cơ bản, nhưng vì cần nhiều tính năng hơn nên đã tạo ra tiện ích mở rộng ngày/giờ độ chính xác cao có tên sqlean-time. Tiện ích này cung cấp API có cấu trúc và nhiều tính năng đa dạng.

Lưu ý. Việc thêm tiện ích mở rộng vào SQLite rất đơn giản. Chỉ cần tải tệp xuống và chạy một lệnh cơ sở dữ liệu.

Khái niệm

Tiện ích mở rộng này sử dụng hai kiểu giá trị: thời điểm (Time) và khoảng thời gian (Duration).

• Thời điểm (Time): một cặp gồm giây và nano giây, biểu thị số giây kể từ mốc thời gian 0 (0001-01-01 00:00:00 UTC) và số nano giây trong giây hiện tại.

  • Có thể lưu thời điểm bằng biểu diễn nội bộ này, cho phép biểu diễn các ngày trước và sau hàng tỷ năm với độ chính xác nano giây.
  • Cũng có thể lưu thời điểm bằng số giây kể từ Unix epoch (1970-01-01 00:00:00 UTC), bao gồm mili giây, micro giây và nano giây.
  • Thời điểm luôn được lưu trữ và xử lý ở UTC, nhưng có thể chuyển đổi sang độ lệch múi giờ cụ thể.

• Khoảng thời gian (Duration): số 64-bit theo đơn vị nano giây, có thể biểu diễn giá trị lên tới khoảng 290 năm.

Tạo giá trị thời gian

• Thời điểm hiện tại:

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

• Ngày/giờ cụ thể:

  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
  

Trích xuất trường thời gian

Có các hàm để trích xuất nhiều trường ngày/giờ khác nhau:

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

Unix time

Các hàm tạo giá trị thời gian từ Unix time (thời gian kể từ 1970-01-01 UTC):

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

Các hàm chuyển giá trị thời gian sang Unix time:

select time_to_unix(time_now());  -- 1722979335

So sánh thời gian

Các hàm so sánh giá trị thời gian:

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

Phép toán thời gian

Các hàm cộng khoảng thời gian vào giá trị thời gian:

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

Các hằng số khoảng thời gian:

• dur_us() - 1 micro giây
• dur_ms() - 1 mili giây
• dur_s() - 1 giây
• dur_m() - 1 phút
• dur_h() - 1 giờ

Làm tròn

Các hàm làm tròn giá trị thời gian theo độ chính xác của trường được chỉ định:

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

Định dạng

Các hàm trả về chuỗi thời gian 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

Hằng số khoảng thời gian

Các hàm trả về các khoảng thời gian phổ biến dưới dạng nano giây:

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

Lời cảm ơn

Tiện ích mở rộng này được triển khai bằng C, và được thiết kế cũng như hiện thực dựa trên gói thời gian của thư viện chuẩn Go (BSD 3-Clause License).

Cài đặt và cách dùng

1. Tải bản phát hành mới nhất
2. Sử dụng trong giao diện dòng lệnh SQLite:

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

Tóm tắt của GN⁺

• Tiện ích mở rộng sqlean-time bổ sung chức năng ngày/giờ độ chính xác cao cho SQLite, cho phép thực hiện nhiều phép toán thời gian khác nhau.
• Có thể xử lý thời điểm và khoảng thời gian ở đơn vị nano giây, nhờ đó thực hiện các phép tính thời gian với độ chính xác rất cao.
• Cung cấp nhiều chức năng định dạng và so sánh thời gian, giúp các nhà phát triển dễ sử dụng.
• Cung cấp nhiều tính năng hơn hẳn các hàm ngày tháng mặc định của SQLite, nên hữu ích cho các dự án cần các phép toán thời gian phức tạp.

[GN⁺]

Ý kiến trên Hacker News

• Câu hỏi liệu có xử lý các trường hợp đặc biệt về thay đổi múi giờ và tính không liên tục của giờ địa phương mà Jon Skeet đã tài liệu hóa hay không

  • Liên kết liên quan: Stack Overflow
  • Liên kết đến video giải thích dài 10 phút của Computerphile: YouTube

• Tốt hơn là không nên tự xây dựng thư viện ngày/giờ và mã hóa

  • Các edge case bất tận có thể gây ra vấn đề
  • Đây là lý do để hoài nghi khi gặp một thư viện mới

• Ba cách biểu diễn/kích thước thời gian khác nhau khá thú vị

  • Tò mò về các trường hợp sử dụng cần độ chính xác nano giây trong quãng thời gian kéo dài hàng tỷ năm
  • Việc chỉ cung cấp phạm vi ±290 năm với độ chính xác nano giây khiến người ta bối rối

• Điều quan trọng là phải làm rõ có dùng số nguyên có dấu hay không

  • Đọc tài liệu thì có thể là số nguyên có dấu hoặc cũng có thể không
  • Nếu là số nguyên có dấu, có thể tồn tại nhiều chuỗi bit biểu diễn cùng một ngày và giờ

• Mong là SQLite3 có một hệ thống kiểu dữ liệu có thể mở rộng

• Nhắc đến những tính năng quan trọng còn thiếu của SQLite và đánh giá nó là rất tuyệt

• Lập luận rằng cơ sở dữ liệu nên theo dõi đơn vị

  • Ví dụ, cần có cách chỉ rõ rằng một cột thời gian được biểu diễn bằng giây kiểu float64
  • Cơ sở dữ liệu nên có khả năng chuyển đổi "2h" thành 7200.0 giây và so sánh trong khi quét bảng
  • Trước đây đã từng viết một cơ sở dữ liệu SQL chuyên dụng xử lý đơn vị như vậy, nhưng từ đó đến nay chưa thấy lại
  • Không chỉ thời gian mà còn phải xử lý mọi đơn vị như khối lượng, thể tích, thông tin, nhiệt độ, v.v.
  • Có thể dạy cơ sở dữ liệu từ chối các phép toán vô nghĩa về mặt toán học để bắt lỗi sớm

• Câu hỏi về việc giữa biểu diễn nano giây và các năm nằm ngoài phạm vi nano, cái nào hữu ích hơn

  • Vì không làm khoa học "chính xác", nên giá trị của nano giây là khá hạn chế
  • Có vẻ như việc biểu diễn được các ngày tháng lịch sử sẽ thường cần thiết hơn

• Đề xuất dùng kiểu dấu thời gian Unix theo phong cách golang ở đơn vị nano giây, với int64 có dấu

  • Có thể không bao phủ được hàng triệu năm với độ chính xác nano giây, nhưng cũng đặt câu hỏi liệu điều đó có thật sự cần thiết không

• Lập luận rằng không nên dùng cách diễn đạt "số giây kể từ epoch" trừ khi nó thực sự mang nghĩa chính xác đó

  • Ví dụ truy vấn: select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18));