Dùng ngày/giờ độ chính xác cao trong SQLite: sqlean-time

 (antonz.org)
2 điểm bởi GN⁺ 2024-08-16 | 1 bình luận | Chia sẻ qua WhatsApp
  • Khi các hàm ngày mặc định của SQLite không đủ, sqlean-time bổ sung dưới dạng extension các kiểu Time·Duration và hàm ngày·giờ với độ chính xác nanosecond
  • Giá trị Time gồm số giây kể từ 0001-01-01 00:00:00 UTC và số nanosecond trong giây hiện tại; khi lưu dưới dạng BLOB 13 byte, có thể xử lý phạm vi hàng tỷ năm trong quá khứ và tương lai
  • Cũng có thể lưu dưới dạng NUMBER 64 bit theo Unix epoch, nhưng đơn vị càng nhỏ thì phạm vi càng thu hẹp; với đơn vị nanosecond chỉ biểu diễn được từ năm 1678 đến năm 2262
  • API bao gồm tạo giá trị, trích xuất trường, chuyển đổi Unix time, so sánh, tính toán, cắt bớt·làm tròn, định dạng·phân tích ISO 8601; các giá trị luôn được lưu và tính toán theo UTC
  • Tính toán lịch giả định dùng lịch Gregory và không xử lý giây nhuận, vì vậy khi tính ngày·tháng·năm nên dùng time_add_date() thay vì time_add() dựa trên Duration

Mô hình thời gian của sqlean-time

  • sqlean-time là extension bổ sung khả năng xử lý ngày·giờ độ chính xác cao cho SQLite
  • Có thể thêm extension SQLite bằng cách tải tệp xuống và chạy một lệnh cơ sở dữ liệu
  • Extension hoạt động xoay quanh hai loại giá trị
    • Time: một thời điểm cụ thể
    • Duration: khoảng thời gian

Biểu diễn Time và phạm vi lưu trữ

  • Time gồm cặp (seconds, nanoseconds)
    • seconds: số nguyên 64 bit biểu thị số giây kể từ zero time là 0001-01-01 00:00:00 UTC
    • nanoseconds: giá trị nanosecond trong giây hiện tại, phạm vi 0-999999999
  • Nếu cần độ linh hoạt tối đa, có thể lưu giá trị Time theo biểu diễn nội bộ là BLOB 13 byte
    • Cách này biểu diễn ngày tháng trong quá khứ và tương lai hàng tỷ năm với độ chính xác nanosecond
  • Cũng hỗ trợ cách lưu số giây, millisecond, microsecond, nanosecond kể từ Unix epoch 1970-01-01 00:00:00 UTC dưới dạng số nguyên NUMBER 64 bit
    • Giây: biểu diễn hàng tỷ năm trong quá khứ·tương lai với độ chính xác giây
    • Millisecond: biểu diễn 292 triệu năm trước và sau 1970 với độ chính xác millisecond
    • Microsecond: biểu diễn từ năm -290307 đến năm 294246
    • Nanosecond: biểu diễn từ năm 1678 đến năm 2262
  • Time luôn được lưu và tính toán theo UTC
    • Có thể chuyển đổi với offset múi giờ cụ thể
  • Tính toán lịch luôn giả định dùng lịch Gregory
    • Không dùng giây nhuận

Duration và tạo giá trị

  • Duration là số nguyên 64 bit theo đơn vị nanosecond
    • Có thể biểu diễn khoảng thời gian đến khoảng 290 năm
    • Có thể lưu dưới dạng NUMBER
  • Có thể tạo thời điểm hiện tại bằng time_now()
    • Ví dụ: time_fmt_iso(time_now()) trả về chuỗi ISO như 2024-08-06T21:22:15.431295000Z
  • Tạo ngày·giờ cụ thể bằng time_date()
    • Nếu chỉ chỉ định ngày thì sẽ là nửa đêm UTC
    • Có thể chỉ định kèm giờ·phút·giây và nanosecond
    • Nếu truyền offset múi giờ, giá trị sẽ được chuyển đổi thành thời điểm UTC

Trích xuất trường ngày·giờ

  • Các hàm trích xuất từng trường trả về năm, tháng, ngày, giờ, phút, giây, nanosecond, thứ trong tuần, ngày trong năm, năm ISO, tuần ISO
    • Ví dụ: time_get_year(), time_get_month(), time_get_day(), time_get_hour(), time_get_minute(), time_get_second(), time_get_nano()
  • Hàm tổng quát time_get() trích xuất giá trị bằng chuỗi tên trường
    • Ví dụ được hỗ trợ: millennium, century, decade, year, quarter, month, day
    • Ví dụ đơn vị thời gian: hour, minute, second, milli, micro, nano
    • Ví dụ liên quan đến ISO·lịch: isoyear, isoweek, isodow, yearday, weekday
    • Có thể lấy giá trị Unix epoch bằng epoch

Chuyển đổi Unix time

  • Có các hàm tạo giá trị Time từ Unix time
    • time_unix(seconds)
    • time_unix(seconds, nanoseconds)
    • time_milli(milliseconds)
    • time_micro(microseconds)
    • time_nano(nanoseconds)
  • Cũng có các hàm chuyển giá trị Time ngược về Unix time
    • time_to_unix()
    • time_to_milli()
    • time_to_micro()
    • time_to_nano()
  • Các hệ điều hành họ Unix thường ghi thời gian bằng giá trị giây 32 bit, nhưng time_to_unix() trả về giá trị 64 bit
    • Hợp lệ trong phạm vi hàng tỷ năm ở quá khứ·tương lai
    • time_to_milli() biểu diễn đến 292 triệu năm trước và sau 1970
    • time_to_micro() biểu diễn từ năm -290307 đến năm 294246
    • time_to_nano() biểu diễn từ năm 1678 đến năm 2262

So sánh và tính toán

  • Các hàm so sánh thời gian xác định thứ tự của hai giá trị Time
    • time_after(): trả về liệu thời điểm thứ nhất có sau thời điểm thứ hai hay không
    • time_before(): trả về liệu thời điểm thứ nhất có trước thời điểm thứ hai hay không
    • time_compare(): trả về 1 nếu sau, -1 nếu trước, 0 nếu bằng nhau
    • time_equal(): trả về liệu hai giá trị có biểu thị cùng một thời điểm hay không
  • time_add() cộng Duration vào giá trị Time
    • Có thể dùng Duration âm để trừ
    • Có thể dùng cùng các hằng Duration như dur_us(), dur_ms(), dur_s(), dur_m(), dur_h()
  • Khi cộng ngày·tháng·năm, phải dùng time_add_date() chứ không phải time_add()
    • time_add_date() cộng năm, tháng, ngày và có thể trừ bằng giá trị âm
  • time_sub() trả về khoảng thời gian giữa hai giá trị Time theo nanosecond
  • time_since() trả về thời gian đã trôi qua kể từ thời điểm chỉ định, theo nanosecond
  • time_until() trả về khoảng thời gian còn lại đến thời điểm chỉ định, theo nanosecond

Cắt bớt và làm tròn

  • time_trunc() làm tròn xuống giá trị Time tới độ chính xác trường được chỉ định
    • Ví dụ được hỗ trợ: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, milli, micro
    • Ví dụ, cắt bớt 2011-11-18T15:56:35.666777888Z theo hour sẽ thành 2011-11-18T15:00:00Z
  • Cũng có thể làm tròn xuống theo bội số của Duration được chỉ định
    • Ví dụ: 12*dur_h(), dur_h(), 30*dur_m(), dur_m(), 30*dur_s(), dur_s()
  • time_round() làm tròn tới bội số gần nhất của Duration được chỉ định
    • Ví dụ: làm tròn 2011-11-18T15:56:35.666777888Z theo dur_h() sẽ thành 2011-11-18T16:00:00Z
    • Làm tròn cùng giá trị đó theo dur_s() sẽ thành 2011-11-18T15:56:36Z

Định dạng và phân tích

  • time_fmt_iso() trả về giá trị Time dưới dạng chuỗi ISO 8601
    • Có thể nhận tùy chọn offset múi giờ để chuyển đổi sang offset đó rồi định dạng
    • Giá trị có nanosecond được biểu diễn như 2011-11-18T15:56:35.666777888Z
    • Nếu chỉ định offset, sẽ biểu diễn như 2011-11-18T18:56:35.666777888+03:00
  • time_fmt_datetime(), time_fmt_date(), time_fmt_time() lần lượt trả về chuỗi datetime, date, time
    • Có thể nhận tùy chọn offset múi giờ
  • time_parse() phân tích chuỗi đã định dạng thành giá trị Time
    • Chuỗi ISO 8601 có nanosecond và múi giờ
    • Chuỗi ISO 8601 có nanosecond và UTC Z
    • Chuỗi ISO 8601 có múi giờ
    • Chuỗi ISO 8601 UTC
    • Ngày·giờ UTC dạng YYYY-MM-DD HH:MM:SS
    • Ngày UTC dạng YYYY-MM-DD
    • Giờ UTC dạng HH:MM:SS
  • Các layout mà time_parse() hỗ trợ là một tập hợp giới hạn

Hằng Duration

  • Có các hàm trả về Duration phổ biến theo nanosecond
    • dur_ns() → 1
    • dur_us() → 1000
    • dur_ms() → 1000000
    • dur_s() → 1000000000
    • dur_m() → 60000000000
    • dur_h() → 3600000000000

Nền tảng triển khai và cài đặt

  • Extension được triển khai bằng C, nhưng thiết kế và triển khai chủ yếu dựa trên package time của thư viện chuẩn Go
    • Package đó dùng BSD 3-Clause License
  • Cài đặt bằng cách tải latest release rồi load extension trong SQLite CLI
    • Ví dụ: .load ./time
    • Sau khi load, có thể dùng các truy vấn như select time_now();

Bài viết liên quan

1 bình luận

 
GN⁺ 2024-08-16
Các ý kiến trên Hacker News

  • Tôi tò mò liệu thư viện này có xử lý các trường hợp đặc biệt như thay đổi múi giờ và sự gián đoạn của giờ địa phương, những thứ Jon Skeet từng tổng hợp rất nổi tiếng, hay không
    https://stackoverflow.com/questions/6841333/why-is-subtracti...
    Computerphile cũng giải thích rất hay trong một video 10 phút
    https://www.youtube.com/watch?v=-5wpm-gesOY
    Từ lâu tôi đã học được rằng không nên tự viết thư viện ngày/giờ hay mã hóa. Có vô số trường hợp biên có thể khiến bạn bị “cắn” rất đau, nên khi thấy những thư viện mới như thế này tôi thường nhìn với sự hoài nghi

    • Thư viện này hoàn toàn không xử lý khái niệm giờ địa phương. Tất cả đều là thời điểm dựa trên UTC; người dùng có thể cung cấp offset múi giờ, nhưng phần khó là tính offset múi giờ thì caller phải tự làm
      Tôi nghĩ tài liệu có thể rõ ràng hơn một chút. Tác giả nói “time zones”, nhưng thực tế thư viện chỉ xử lý offset múi giờ. Múi giờ là những thứ như America/New_York, còn offset múi giờ là độ lệch so với UTC. New York hôm nay là -14400 giây, nhưng vài tháng nữa sẽ là -18000 giây do thay đổi giờ mùa hè

  • Ba cách biểu diễn/kích thước thời gian khác nhau khá thú vị. Ví dụ, tôi không rõ use case nào cần độ chính xác nano giây trên phạm vi hàng tỷ năm
    Điều còn gây rối hơn là độ phân giải thời gian thì cực kỳ mịn, nhưng với duration thì độ chính xác nano giây chỉ có phạm vi ±290 năm

    • Một khi đã quyết định dùng độ chính xác nano giây, biểu diễn 64-bit chỉ chứa được 584 năm, và như vậy là không đủ. Ít nhất phải thêm 2 bit nữa mới biểu diễn được năm 2024
      Nhưng nếu đã thêm 2 bit thì cũng chẳng có lý do gì để không thêm 16 bit hoặc 32 bit. Như vậy có thể bao quát từ người tính thời gian ánh sáng đi qua 30cm cho đến người tính tuổi của vũ trụ
      Tôi tưởng tượng quyết định thiết kế có lẽ đã diễn ra theo kiểu như vậy :)
      Dĩ nhiên, khó mà cung cấp độ chính xác dưới giây nếu không hỗ trợ giây nhuận, và việc hỗ trợ giây nhuận trước nền văn minh nhân loại thì rốt cuộc có ý nghĩa gì cũng không rõ
    • Cách này đã rất phù hợp với tôi và hàng nghìn lập trình viên Go khác. Vì vậy tôi đã chọn hướng tiếp cận này

  • Hơi lạc đề nhưng có liên quan: cơ sở dữ liệu nên theo dõi đơn vị. Nếu có một cột thời gian, chẳng hạn, ta nên có thể khai báo đó là duration tính bằng giây kiểu float64
    Khi đó có thể viết SELECT * FROM my_table WHERE duration_s >= 2h, và cơ sở dữ liệu sẽ tự đổi “2h” thành 7200.0 giây rồi so sánh các giá trị cùng đơn vị trong lúc quét bảng
    Vài năm trước tôi từng làm một cơ sở dữ liệu SQL chuyên dụng có xử lý đơn vị native như vậy, nhưng trước sau đó chưa thấy ở đâu khác, và nó có vẻ là một khoảng trống trong hệ sinh thái UI
    Không nhất thiết chỉ giới hạn ở thời gian. Nên xử lý được toàn bộ danh sách đơn vị như khối lượng, thể tích, lượng thông tin, nhiệt độ, v.v. Cơ sở dữ liệu cũng có thể từ chối các biểu thức vô nghĩa về mặt toán học như SELECT 2h + 15kg -- type error!
    Điều này sẽ giúp rất nhiều trong việc bắt lỗi phân tích từ sớm

  • Tôi nghĩ việc nói rõ có dùng số nguyên có dấu hay không là quan trọng. Đọc tài liệu thì có vẻ là có dấu, nhưng cũng có thể không phải
    Nếu là số nguyên có dấu, có thể có nhiều chuỗi bit biểu diễn cùng một ngày giờ, và điều đó không tốt

    • Chắc chắn là có dấu. Tài liệu có nói “muốn trừ thì dùng duration âm”
      Tuy nhiên mẫu bit là chuyện nội bộ của thư viện. Nếu bạn tìm được lỗi trong code thì tất nhiên nên chỉ ra, và nếu có thể thì đề xuất sửa luôn
    • Làm sao lại có thể có “nhiều chuỗi bit biểu diễn cùng một ngày giờ”?

  • Giá mà SQLite3 có hệ thống kiểu có thể mở rộng thì thật tuyệt

    • Với tư cách người từng đóng góp một chút cho PostgreSQL, tôi nói thế này: không, không nên làm vậy!!!!
      Hệ thống kiểu có thể mở rộng cực kỳ tệ cho hiệu năng của người dùng cuối cơ sở dữ liệu. Khi đó trong quá trình phân tích cú pháp và tối ưu hóa truy vấn, không thể đi đường tắt ở bất kỳ chỗ nào. Phải kiểm tra kiểu của mọi toán hạng, tìm implementation toán tử đúng, tìm đúng họ/lớp toán tử chỉ mục, v.v., liên tục tra cứu catalog hệ thống truy vấn
      Việc nhập/xuất giá trị cũng đi qua các hàm được lưu trong catalog hệ thống. Ngay cả select 1 cũng không thể trả lời nếu không nhìn vào catalog hệ thống
      Nên có một tập kiểu built-in phù hợp cùng các cách kết hợp như struct/JSON. Hầu hết cơ sở dữ liệu ngoài PostgreSQL đều làm theo kiểu này, và tôi tin mạnh mẽ rằng đó là hướng đúng

  • Một câu hỏi kiểu Ask HN hơi lười: theo kinh nghiệm thì cái nào hữu ích hoặc có giá trị hơn? biểu diễn nano giây, hay biểu diễn các năm nằm ngoài phạm vi nano giây như 1678~2200?
    Tôi không làm công việc khoa học nghiêm túc, nên giá trị của nano giây có vẻ chỉ giới hạn ở các thí nghiệm rất khéo léo hoặc việc theo dõi giao dịch tài chính với phạm vi hẹp hơn
    Trong khi đó, khả năng biểu diễn ngày lịch sử có vẻ sẽ cần thường xuyên hơn. Mọi người nghĩ sao?

    • Ngày lịch sử chắc chắn quan trọng hơn
      Chỉ cần hạ độ chính xác xuống 10 nano giây thôi là đã có được phạm vi đủ dùng trong thực tế
    • Giống như hỏi búa và tua vít cái nào hữu ích hơn. Tùy việc cần làm

  • Tôi tò mò vì sao không dùng kiểu Go: Unix timestamp dưới dạng signed int64 tính bằng nano giây. Dĩ nhiên sẽ không bao quát được hàng triệu năm với độ chính xác nano giây, nhưng có thật sự cần thế không?

    • Với độ chính xác và kích thước đó, nó chỉ bao quát được từ năm 1678 đến năm 2262, nên khả năng biểu diễn ngày giờ lịch sử bị hạn chế đáng kể
    • Lưu Unix timestamp bằng nano giây không phải là phong cách Go, nhưng extension này có thể làm như vậy
      select time_to_nano(time_now());
      -- 1722979335431295000

  • Tôi mong các biểu thức như “số giây kể từ epoch” chỉ được dùng khi đúng nghĩa chính xác như vậy
    Tôi tò mò select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18)); sẽ trả về gì

    • Tại sao bạn lại mong như vậy?
      Tôi có thể nghĩ ra vài lý do hợp lý, nhưng điều thực sự quan trọng chỉ là “epoch nào?”. Với các hệ thống dựa trên UNIX hoặc các hệ thống muốn bắt chước hành vi đó, nó được định nghĩa rõ. Nhưng bạn không nói rõ bất mãn là gì, nên khó phản biện hoặc biện minh vì sao hiện tại nó như vậy
      time_date(1311, 11, 18) không được định nghĩa trong epoch mà hầu hết hệ thống máy tính dùng, nên kết quả nào cũng có thể xảy ra. Có thể là MAX_INT, MIN_INT, 0, một giá trị trông hợp lý nhưng không phản ánh các cuộc cải cách lịch, một giá trị được chuyển sang epoch khác để tính đúng số giây, hay bất cứ thứ gì. Trước GMT/UTC thì tất cả đều là giờ địa phương, nên cũng có thể lập luận rằng không có epoch hợp lệ
      Dĩ nhiên, việc có nên hỗ trợ giá trị âm hay không thì có thể tranh luận theo cả hai hướng. Ta có thể kỳ vọng đúng 24 giờ trước 1970-1-1 0:00:00 UTC là -86400, nhưng “since” lại hàm ý khá mạnh rằng chỉ là số dương
      Những người khác cũng có thể có epoch hoàn toàn khác vì lý do khác, và nếu mọi người trong phạm vi sử dụng đều đồng ý thì cũng ổn
      Hay bạn có lý do phản đối nào khác?
    • Có ghi rằng “nếu kết quả vượt quá giá trị tối đa có thể lưu trong Duration, duration tối đa sẽ được trả về”