การใช้วันที่/เวลาแบบความละเอียดสูงใน SQLite: sqlean-time

 (antonz.org)
2 คะแนน โดย GN⁺ 2024-08-16 | 1 ความคิดเห็น | แชร์ทาง WhatsApp
  • หากฟังก์ชันวันที่พื้นฐานของ SQLite ยังไม่เพียงพอ sqlean-time จะเพิ่มชนิดข้อมูล Time·Duration และฟังก์ชันวันที่/เวลาในรูปแบบส่วนขยาย พร้อม ความละเอียดระดับนาโนวินาที
  • ค่า Time ประกอบด้วยจำนวนวินาทีหลัง 0001-01-01 00:00:00 UTC และค่านาโนวินาทีภายในวินาทีปัจจุบัน โดยเมื่อเก็บเป็น BLOB ขนาด 13 ไบต์ จะรองรับช่วงเวลาย้อนหลังและล่วงหน้าได้หลายพันล้านปี
  • สามารถเก็บเป็น NUMBER 64 บิต ตาม Unix epoch ได้เช่นกัน แต่ยิ่งใช้หน่วยเล็กลง ช่วงที่รองรับก็จะยิ่งแคบลง โดยหน่วยนาโนวินาทีจะแทนค่าได้เพียงตั้งแต่ปี 1678 ถึง 2262
  • API ครอบคลุมการสร้างค่า, การดึงฟิลด์, การแปลง Unix time, การเปรียบเทียบ, เลขคณิต, การตัดเศษ·ปัดเศษ, และการฟอร์แมต·พาร์ส ISO 8601 โดยค่าทั้งหมดจะถูกเก็บและคำนวณเป็น UTC เสมอ
  • การคำนวณตามปฏิทินถือว่าทำงานบน ปฏิทินเกรกอเรียน และไม่รองรับ leap second ดังนั้นการบวกวัน·เดือน·ปีควรใช้ time_add_date() แทน time_add() ที่อิง Duration

โมเดลเวลาของ sqlean-time

  • sqlean-time เป็นส่วนขยายที่เพิ่มความสามารถด้าน การจัดการวันที่·เวลาแบบความละเอียดสูง ให้กับ SQLite
  • ส่วนขยายของ SQLite สามารถเพิ่มได้ด้วยการดาวน์โหลดไฟล์และรันคำสั่งฐานข้อมูลเพียงคำสั่งเดียว
  • ส่วนขยายนี้ทำงานโดยยึดค่าหลักอยู่สองชนิด
    • Time: ช่วงเวลาหนึ่งจุดในเวลา
    • Duration: ช่วงระยะเวลา

การแทนค่า Time และช่วงการจัดเก็บ

  • Time ประกอบด้วยคู่ค่า (seconds, nanoseconds)
    • seconds: จำนวนวินาทีหลัง zero time ที่ 0001-01-01 00:00:00 UTC ในรูป จำนวนเต็ม 64 บิต
    • nanoseconds: ค่านาโนวินาทีภายในวินาทีปัจจุบัน อยู่ในช่วง 0-999999999
  • หากต้องการความยืดหยุ่นสูงสุด สามารถเก็บค่า Time เป็น BLOB ขนาด 13 ไบต์ ตามรูปแบบภายในได้
    • วิธีนี้สามารถแทนวันที่ย้อนหลังและล่วงหน้าได้หลายพันล้านปีด้วยความละเอียดระดับนาโนวินาที
  • ยังรองรับการเก็บเป็น NUMBER จำนวนเต็ม 64 บิต ของจำนวนวินาที, มิลลิวินาที, ไมโครวินาที, หรือนาโนวินาที หลัง Unix epoch ที่ 1970-01-01 00:00:00 UTC
    • วินาที: แทนช่วงย้อนหลัง·ล่วงหน้าได้หลายพันล้านปีด้วยความละเอียดระดับวินาที
    • มิลลิวินาที: แทนได้ 292 ล้านปีก่อนและหลังปี 1970 ด้วยความละเอียดระดับมิลลิวินาที
    • ไมโครวินาที: แทนได้ตั้งแต่ปี -290307 ถึง 294246
    • นาโนวินาที: แทนได้ตั้งแต่ปี 1678 ถึง 2262
  • Time จะถูกเก็บและคำนวณเป็น UTC เสมอ
    • แต่สามารถแปลงกับ offset ของเขตเวลาเฉพาะได้
  • การคำนวณตามปฏิทินจะถือว่าใช้ ปฏิทินเกรกอเรียน เสมอ
    • ไม่ใช้ leap second

Duration และการสร้างค่า

  • Duration คือ จำนวนเต็ม 64 บิตในหน่วยนาโนวินาที
    • สามารถแทนช่วงเวลาได้ประมาณ 290 ปี
    • เก็บเป็น NUMBER ได้
  • เวลาปัจจุบันสร้างได้ด้วย time_now()
    • ตัวอย่าง: time_fmt_iso(time_now()) จะคืนสตริง ISO แบบ 2024-08-06T21:22:15.431295000Z
  • วันที่·เวลาที่ระบุเองสร้างได้ด้วย time_date()
    • หากระบุเฉพาะวันที่ จะได้ค่าเป็นเวลาเที่ยงคืน UTC
    • สามารถระบุชั่วโมง·นาที·วินาทีและนาโนวินาทีร่วมกันได้
    • หากส่ง offset ของเขตเวลาเข้าไป ระบบจะแปลงเป็นเวลา UTC

การดึงฟิลด์วันที่·เวลา

  • ฟังก์ชันดึงฟิลด์แต่ละตัวสามารถคืนค่าปี, เดือน, วัน, ชั่วโมง, นาที, วินาที, นาโนวินาที, วันในสัปดาห์, วันของปี, ISO year และ ISO week
    • ตัวอย่าง: time_get_year(), time_get_month(), time_get_day(), time_get_hour(), time_get_minute(), time_get_second(), time_get_nano()
  • ฟังก์ชันทั่วไป time_get() ใช้ดึงค่าด้วยสตริงชื่อฟิลด์
    • ตัวอย่างค่าที่รองรับ: millennium, century, decade, year, quarter, month, day
    • ตัวอย่างหน่วยเวลา: hour, minute, second, milli, micro, nano
    • ตัวอย่างเกี่ยวกับ ISO·ปฏิทิน: isoyear, isoweek, isodow, yearday, weekday
    • ค่า Unix epoch ดึงได้ด้วย epoch

การแปลง Unix time

  • มีฟังก์ชันสำหรับสร้างค่า Time จาก Unix time
    • time_unix(seconds)
    • time_unix(seconds, nanoseconds)
    • time_milli(milliseconds)
    • time_micro(microseconds)
    • time_nano(nanoseconds)
  • และมีฟังก์ชันสำหรับแปลงค่า Time กลับเป็น Unix time
    • time_to_unix()
    • time_to_milli()
    • time_to_micro()
    • time_to_nano()
  • ระบบปฏิบัติการตระกูล Unix มักบันทึกเวลาเป็นค่าวินาทีแบบ 32 บิต แต่ time_to_unix() จะคืนค่าเป็น 64 บิต
    • ใช้ได้กับช่วงย้อนหลัง·ล่วงหน้าหลายพันล้านปี
    • time_to_milli() แทนค่าได้ถึง 292 ล้านปีก่อนและหลังปี 1970
    • time_to_micro() แทนค่าได้ตั้งแต่ปี -290307 ถึง 294246
    • time_to_nano() แทนค่าได้ตั้งแต่ปี 1678 ถึง 2262

การเปรียบเทียบและเลขคณิต

  • ฟังก์ชันเปรียบเทียบเวลาจะใช้ตัดสินลำดับของค่า Time สองค่า
    • time_after(): คืนค่าว่าเวลาแรกอยู่หลังเวลาที่สองหรือไม่
    • time_before(): คืนค่าว่าเวลาแรกอยู่ก่อนเวลาที่สองหรือไม่
    • time_compare(): ถ้าหลังจะคืน 1, ถ้าก่อนจะคืน -1, ถ้าเท่ากันจะคืน 0
    • time_equal(): คืนค่าว่าสองค่านี้แทนช่วงเวลาเดียวกันหรือไม่
  • time_add() ใช้บวก Duration เข้ากับค่า Time
    • หากใช้ Duration ติดลบก็สามารถใช้เพื่อลบได้
    • สามารถใช้ร่วมกับค่าคงที่ Duration เช่น dur_us(), dur_ms(), dur_s(), dur_m(), dur_h()
  • เมื่อจะบวก วัน·เดือน·ปี ต้องใช้ time_add_date() แทน time_add()
    • time_add_date() ใช้บวกปี, เดือน, วัน และใช้ค่าติดลบเพื่อลบได้
  • time_sub() จะคืนช่วงเวลาระหว่างค่า Time สองค่าเป็น นาโนวินาที
  • time_since() จะคืนเวลาที่ผ่านไปหลังจากเวลาที่กำหนดเป็นนาโนวินาที
  • time_until() จะคืนช่วงเวลาที่เหลือจนถึงเวลาที่กำหนดเป็นนาโนวินาที

การตัดเศษและการปัดเศษ

  • time_trunc() จะปัดค่า Time ลงตามความละเอียดของฟิลด์ที่กำหนด
    • ตัวอย่างที่รองรับ: millennium, century, decade, year, quarter, month, week, day, hour, minute, second, milli, micro
    • ตัวอย่างเช่น เมื่อตัด 2011-11-18T15:56:35.666777888Z ที่ระดับ hour จะได้ 2011-11-18T15:00:00Z
  • ยังสามารถปัดลงเป็นจำนวนเท่าของ Duration ที่กำหนดได้
    • ตัวอย่าง: 12*dur_h(), dur_h(), 30*dur_m(), dur_m(), 30*dur_s(), dur_s()
  • time_round() จะปัดไปยังจำนวนเท่าที่ใกล้ที่สุดของ Duration ที่กำหนด
    • ตัวอย่าง: หากปัด 2011-11-18T15:56:35.666777888Z ด้วย dur_h() จะได้ 2011-11-18T16:00:00Z
    • หากปัดค่าเดียวกันด้วย dur_s() จะได้ 2011-11-18T15:56:36Z

การฟอร์แมตและการพาร์ส

  • time_fmt_iso() จะคืนค่า Time เป็น สตริง ISO 8601
    • สามารถรับ offset ของเขตเวลาแบบเลือกส่งได้ เพื่อแปลงเป็น offset นั้นก่อนฟอร์แมต
    • ค่าที่มีนาโนวินาทีจะแสดงเป็นรูปแบบเช่น 2011-11-18T15:56:35.666777888Z
    • หากกำหนด offset จะได้รูปแบบเช่น 2011-11-18T18:56:35.666777888+03:00
  • time_fmt_datetime(), time_fmt_date(), time_fmt_time() จะคืนสตริงแบบ datetime, date, time ตามลำดับ
    • สามารถรับ offset ของเขตเวลาแบบเลือกส่งได้
  • time_parse() ใช้พาร์สสตริงที่ฟอร์แมตแล้วให้เป็นค่า Time
    • สตริง ISO 8601 ที่มีนาโนวินาทีและเขตเวลา
    • สตริง ISO 8601 ที่มีนาโนวินาทีและ UTC Z
    • สตริง ISO 8601 ที่มีเขตเวลา
    • สตริง ISO 8601 แบบ UTC
    • วันที่·เวลา UTC รูปแบบ YYYY-MM-DD HH:MM:SS
    • วันที่ UTC รูปแบบ YYYY-MM-DD
    • เวลา UTC รูปแบบ HH:MM:SS
  • เลย์เอาต์ที่ time_parse() รองรับมี ชุดที่จำกัด

ค่าคงที่ Duration

  • มีฟังก์ชันสำหรับคืนค่า Duration ทั่วไปเป็นนาโนวินาที
    • dur_ns() → 1
    • dur_us() → 1000
    • dur_ms() → 1000000
    • dur_s() → 1000000000
    • dur_m() → 60000000000
    • dur_h() → 3600000000000

พื้นฐานการติดตั้งและการติดตั้งใช้งาน

  • ส่วนขยายนี้พัฒนาด้วย C แต่การออกแบบและการพัฒนานั้นอ้างอิงจากแพ็กเกจ time ใน Go standard library อย่างมาก
    • แพ็กเกจดังกล่าวใช้ BSD 3-Clause License
  • การติดตั้งทำได้โดยดาวน์โหลด latest release แล้วโหลดส่วนขยายใน SQLite CLI
    • ตัวอย่าง: .load ./time
    • หลังจากโหลดแล้วสามารถใช้คิวรีอย่าง select time_now(); ได้

บทความที่เกี่ยวข้อง

1 ความคิดเห็น

 
GN⁺ 2024-08-16
ความคิดเห็นบน Hacker News

  • สงสัยว่ารองรับกรณีพิเศษอย่าง การเปลี่ยนแปลงเขตเวลา และความไม่ต่อเนื่องของเวลาท้องถิ่น ที่ Jon Skeet เคยสรุปไว้อย่างโด่งดังหรือไม่
    https://stackoverflow.com/questions/6841333/why-is-subtracti...
    Computerphile ก็อธิบายเรื่องนี้ได้ดีมากในวิดีโอ 10 นาที
    https://www.youtube.com/watch?v=-5wpm-gesOY
    ผม/ฉันเคยเรียนรู้มานานแล้วว่าจะไม่ทำไลบรารี วันที่/เวลา หรือ การเข้ารหัสลับ เอง เพราะมี edge case ที่อาจกัดได้อย่างร้ายแรงไม่รู้จบ พอเห็นไลบรารีใหม่แบบนี้จึงมักมองด้วยความกังขา

    • ไลบรารีนี้ไม่ได้จัดการแนวคิดเรื่อง เวลาท้องถิ่น เลย ทั้งหมดเป็นเวลาบนฐาน UTC และผู้ใช้สามารถระบุ offset ของเขตเวลาได้ แต่ส่วนยากอย่างการคำนวณ offset ของเขตเวลานั้นผู้เรียกต้องทำเอง
      เอกสารน่าจะชัดเจนกว่านี้ได้ ผู้เขียนพูดว่า “time zones” แต่จริง ๆ ไลบรารีจัดการแค่ offset ของเขตเวลา เท่านั้น เขตเวลาคือสิ่งอย่าง America/New_York ส่วน offset ของเขตเวลาคือส่วนต่างจาก UTC วันนี้นิวยอร์กคือ -14400 วินาที แต่ในอีกไม่กี่เดือนจะเป็น -18000 วินาทีเพราะการเปลี่ยนเวลาออมแสง

  • น่าสนใจที่มี การแทนค่า/ขนาดของเวลา แตกต่างกันสามแบบ เช่น ไม่แน่ใจว่ามี use case อะไรที่ต้องการความละเอียดระดับนาโนวินาทีในช่วงเวลาหลายพันล้านปี
    ที่ชวนสับสนกว่านั้นคือ granularity ของเวลาละเอียดสุดขีด แต่ใน duration ความละเอียดระดับนาโนวินาทีมีช่วงได้แค่ ±290 ปี

    • เมื่อเลือกใช้ ความละเอียดระดับนาโนวินาที แล้ว การแทนด้วย 64 บิตจะเก็บได้แค่ 584 ปี ซึ่งไม่พอ อย่างน้อยต้องมีอีก 2 บิตถึงจะแทนปี 2024 ได้
      แต่ถ้าจะเพิ่ม 2 บิตแล้ว ก็ไม่มีเหตุผลอะไรที่จะไม่เพิ่ม 16 บิตหรือ 32 บิตไปเลย แบบนั้นก็ครอบคลุมตั้งแต่คนที่คำนวณเวลาที่แสงเดินทาง 30 ซม. ไปจนถึงคนที่คำนวณอายุของเอกภพได้
      เดาว่าการตัดสินใจออกแบบคงไหลไปประมาณนี้ :)
      แน่นอนว่าเป็นเรื่องยากที่จะให้ความแม่นยำต่ำกว่าวินาทีโดยไม่รองรับ leap second และก็ยังไม่ชัดว่าการรองรับ leap second ก่อนอารยธรรมมนุษย์จะมีความหมายอะไร
    • วิธีนี้ใช้ได้ดีมากกับผม/ฉันและนักพัฒนา Go อีกหลายพันคน ดังนั้นจึงเลือกแนวทางนี้

  • ออกนอกเรื่องที่เกี่ยวข้องกัน ฐานข้อมูลควรติดตาม หน่วย ได้ ถ้ามีคอลัมน์เวลา ก็ควรประกาศได้ว่าเป็น duration หน่วยวินาทีแบบ float64 เป็นต้น
    จากนั้นควรเขียนได้ว่า SELECT * FROM my_table WHERE duration_s >= 2h แล้วฐานข้อมูลก็แปลง “2h” เป็น 7200.0 วินาทีให้เอง และเปรียบเทียบหน่วยเดียวกันระหว่างสแกนตาราง
    หลายปีก่อนเคยสร้างฐานข้อมูล SQL เฉพาะทางที่มีการจัดการหน่วยแบบ native แบบนี้ แต่ไม่เคยเห็นมาก่อนหรือหลังจากนั้น และดูเหมือนเป็นช่องว่างในระบบนิเวศ UI
    ไม่จำเป็นต้องจำกัดแค่เวลา ควรรองรับรายการหน่วยทั้งหมด เช่น มวล ปริมาตร ปริมาณข้อมูล อุณหภูมิ ฯลฯ ฐานข้อมูลยังควรปฏิเสธนิพจน์ที่ไม่สมเหตุสมผลทางคณิตศาสตร์อย่าง SELECT 2h + 15kg -- type error! ได้ด้วย
    วิธีนี้จะช่วยมากในการจับข้อผิดพลาดเชิงวิเคราะห์ตั้งแต่เนิ่น ๆ

    • หน่วย interval ของ PostgreSQL ทำให้ query ด้วยรูปแบบที่ใกล้เคียงภาษาธรรมชาติได้อยู่แล้ว: https://www.postgresql.org/docs/current/datatype-datetime.ht...
    • แล้ว leap second จะจัดการอย่างไร?

  • คิดว่าสำคัญที่จะระบุว่าใช้ จำนวนเต็มมีเครื่องหมาย หรือไม่ อ่านเอกสารแล้วเหมือนจะมีเครื่องหมาย แต่ก็อาจไม่ใช่
    ถ้าเป็นจำนวนเต็มมีเครื่องหมาย ก็อาจมี bit string หลายแบบที่แทนวันที่และเวลาเดียวกันได้ ซึ่งไม่ดี

    • มีเครื่องหมายแน่นอน มีข้อความว่า “ถ้าจะลบให้ใช้ duration ติดลบ”
      แต่ bit pattern เป็นเรื่องภายในของไลบรารี ถ้าพบ bug ในโค้ดก็ควรชี้ให้เห็น และถ้าเป็นไปได้ก็เสนอการแก้ไขด้วย
    • “bit string หลายแบบที่แทนวันที่และเวลาเดียวกัน” เป็นไปได้อย่างไร?

  • ถ้า SQLite3 มี ระบบชนิดข้อมูลที่ขยายได้ ก็คงดีมาก

    • ในฐานะคนที่เคยมีส่วนร่วมกับ PostgreSQL เล็กน้อย ขอพูดว่า: ไม่ได้ เรื่องนี้ไม่ควรทำ!!!!
      ระบบชนิดข้อมูลที่ขยายได้ แย่มากต่อประสิทธิภาพของผู้ใช้ปลายทางฐานข้อมูล เพราะจะไม่สามารถใช้ทางลัดอะไรได้เลยในการ parse และ optimize query ต้องตรวจชนิดของ operand ทุกตัว หา implementation ของ operator ที่ถูกต้อง หา index operator family/class ที่ถูกต้อง ฯลฯ ต้องคอย lookup system catalog ของระบบ query ตลอด
      การ input/output ของค่าก็ต้องผ่านฟังก์ชันที่เก็บไว้ใน system catalog แม้แต่ select 1 ก็ยังตอบไม่ได้โดยไม่ดู system catalog
      ควรมีชุดชนิดข้อมูล built-in ที่เหมาะสม และวิธีประกอบอย่าง struct/JSON ฐานข้อมูลส่วนใหญ่ที่ไม่ใช่ PostgreSQL ทำแบบนี้ และผม/ฉันเชื่ออย่างแรงว่านี่คือทิศทางที่ถูกต้อง

  • เป็นคำถามแนว Ask HN แบบขี้เกียจนิดหน่อย จากประสบการณ์ อะไรมีประโยชน์หรือมีค่ามากกว่ากัน? การแทนค่าระดับนาโนวินาที หรือการแทนปีที่อยู่นอกช่วงนาโนวินาทีอย่าง 1678~2200?
    ไม่ได้ทำงานวิทยาศาสตร์จริงจัง จึงดูเหมือนคุณค่าของนาโนวินาทีจะจำกัดอยู่แค่การทดลองที่ชาญฉลาดมาก ๆ หรือการติดตามธุรกรรมการเงินที่มีช่วงแคบกว่า
    ในทางกลับกัน ความสามารถในการแทนวันที่ทางประวัติศาสตร์น่าจะจำเป็นบ่อยกว่า คิดอย่างไร?

    • วันที่ทางประวัติศาสตร์สำคัญกว่าแน่นอน
      แค่ลดความละเอียดลงเหลือ 10 นาโนวินาที ก็ได้ช่วงที่เพียงพอในทางปฏิบัติแล้ว
    • คล้ายกับถามว่า ระหว่างค้อนกับไขควง อะไรมีประโยชน์กว่า ขึ้นอยู่กับงาน

  • สงสัยว่าทำไมไม่ใช้ Unix timestamp เป็น signed int64 หน่วยนาโนวินาที แบบสไตล์ Go ถึงจะครอบคลุมหลายล้านปีด้วยความละเอียดระดับนาโนวินาทีไม่ได้ แต่จำเป็นจริงหรือ?

    • ด้วยความละเอียดและขนาดนั้นจะครอบคลุมได้แค่ ปี 1678 ถึง 2262 ทำให้ความสามารถในการแทนวันที่และเวลาเชิงประวัติศาสตร์ถูกจำกัดอย่างมาก
    • การเก็บ Unix timestamp เป็นนาโนวินาทีไม่ใช่สไตล์ Go แต่ extension นี้ทำแบบนั้นได้
      select time_to_nano(time_now());
      -- 1722979335431295000

  • อยากให้ใช้สำนวนอย่าง “วินาทีหลัง epoch” เฉพาะเมื่อหมายความอย่างนั้นจริง ๆ
    สงสัยว่า select time_sub(time_date(2011, 11, 19), time_date(1311, 11, 18)); จะคืนค่าอะไร

    • ทำไมถึงอยากให้เป็นแบบนั้น?
      พอนึกเหตุผลที่ฟังขึ้นได้อยู่บ้าง แต่สิ่งที่สำคัญจริง ๆ คือ “epoch ไหน?” เท่านั้น ในระบบที่อิง UNIX หรือระบบที่พยายามเลียนแบบพฤติกรรมนั้น มันนิยามไว้ชัดเจน แต่เพราะไม่ได้บอกว่าขัดใจตรงไหน จึงยากที่จะโต้แย้งหรืออธิบายว่าทำไมจึงเป็นอย่างตอนนี้
      time_date(1311, 11, 18) ไม่ถูกนิยามสำหรับ epoch ที่ระบบคอมพิวเตอร์ส่วนใหญ่ใช้ ดังนั้นผลลัพธ์ใด ๆ ก็เป็นไปได้ ไม่ว่าจะเป็น MAX_INT, MIN_INT, 0, ค่าที่ดูสมเหตุสมผลแต่ไม่สะท้อนการปฏิรูปปฏิทิน, หรือค่าที่แปลงไปยัง epoch อื่นแล้วคำนวณจำนวนวินาทีอย่างถูกต้อง ฯลฯ ก่อน GMT/UTC ทุกอย่างเป็นเวลาท้องถิ่น จึงอาจอ้างได้ว่าไม่มี epoch ที่ใช้ได้
      แน่นอนว่าการควรรองรับค่าติดลบนั้นถกเถียงได้ทั้งสองทาง เราอาจคาดหวังว่า 24 ชั่วโมงก่อน 1970-1-1 0:00:00 UTC พอดีจะเป็น -86400 แต่คำว่า “since” บอกเป็นนัยค่อนข้างแรงว่าเป็นบวกเท่านั้น
      คนอื่นอาจมี epoch ที่ต่างกันโดยสิ้นเชิงด้วยเหตุผลอื่น และถ้าทุกคนในโดเมนที่ใช้ตกลงกันได้ ก็ไม่เป็นไร
      หรือมีเหตุผลคัดค้านอื่นอยู่?
    • มีเขียนไว้ว่า “ถ้าผลลัพธ์เกินค่าสูงสุดที่เก็บใน Duration ได้ จะคืนค่า duration สูงสุด”