SQL Interview Prep · บทเรียน

เขตเวลาและประทับเวลา

จัดเก็บเวลาเป็น UTC แปลงเขตเวลา และทำความเข้าใจประเด็นเกี่ยวกับประทับเวลาที่ผู้สัมภาษณ์มักถาม

บทเรียน 4 จาก 413 ขั้นตอน

เขตเวลาและประทับเวลา เป็นบทเรียน SQL Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

เหตุใดเขตเวลาจึงทำให้ผู้สมัครสับสน

เขตเวลาเป็นเรื่องที่ทำให้ผู้สมัครที่ดูมั่นใจพลาดได้ ผู้สัมภาษณ์จึงมักถามเพื่อดูว่าผู้สมัครเข้าใจลึกเพียงใด คำถามหลักคือ "คุณจะจัดเก็บและเปรียบเทียบการประทับเวลาจากคนละภูมิภาคอย่างไร"

คำตอบแบบมืออาชีพคือหลักปฏิบัติ ไม่ใช่ฟังก์ชัน: จัดเก็บทุกอย่างเป็น UTC และแปลงเฉพาะที่ขอบเขตการแสดงผล การวางรูปแบบการจัดเก็บให้ถูกต้องจะทำให้คำสั่งสอบถามส่วนใหญ่กลายเป็นเรื่องง่าย

timestamp เทียบกับ timestamptz
การแปลงระหว่างเขตเวลา
UTC ในฐานะแหล่งข้อมูลหลักที่เชื่อถือได้

การประทับเวลาเทียบกับการประทับเวลาพร้อมเขตเวลา

PostgreSQL มีชนิดข้อมูลการประทับเวลาสองชนิด และการสับสนระหว่างชนิดเหล่านี้เป็นข้อผิดพลาดยอดนิยมในการสัมภาษณ์

timestamp (ไม่มีเขตเวลา): ค่าตามนาฬิกาที่ไม่มีเขตเวลาผูกอยู่ โดยจะจัดเก็บค่าตามที่ป้อนเข้าไปทุกประการ
timestamptz (มีเขตเวลา): จัดเก็บภายในเป็น UTC เมื่อรับข้อมูลเข้าจะแปลงจากเขตเวลาของเซสชัน และเมื่อส่งออกจะแปลงกลับ

แม้ชื่อจะเป็นเช่นนั้น แต่ timestamptz ไม่ได้จัดเก็บเขตเวลา แต่จัดเก็บจุดเวลาที่แน่นอนใน UTC รายละเอียดนี้สร้างความประทับใจให้ผู้สัมภาษณ์ได้

CREATE TABLE events (
  id          bigint,
  occurred_at timestamptz   -- recommended: an absolute instant
);

จัดเก็บเป็น UTC และแปลงเมื่อแสดงผล

กฎสำคัญที่สุด ให้จัดเก็บจุดเวลาเป็น UTC (ใช้ timestamptz) และแปลงเป็นเขตเวลาท้องถิ่นเฉพาะเมื่อนำเสนอให้ผู้ใช้ วิธีนี้ช่วยหลีกเลี่ยงความกำกวมเกี่ยวกับเวลาออมแสง และทำให้การเรียงลำดับตามเวลาถูกต้องในทุกพื้นที่

หากถูกถามว่า "ทำไมต้องใช้ UTC" ให้ตอบว่า UTC ไม่มีการเลื่อนเวลาออมแสง ดังนั้นค่าเวลาเดียวกันตามนาฬิกาท้องถิ่นจะไม่เกิดซ้ำหรือหายไปเหมือนเวลาในท้องถิ่น

-- Display a UTC instant in a user's zone (Postgres)
SELECT occurred_at AT TIME ZONE 'America/New_York' AS local_time
FROM events;

ความหมายสองแบบของ AT TIME ZONE

AT TIME ZONE เป็นความสามารถที่มีลูกเล่นและมักเป็นจุดที่ทำให้เข้าใจผิด เพราะทำงานตรงข้ามกันสองแบบโดยขึ้นอยู่กับชนิดของข้อมูล:

เมื่อนำไปใช้กับ timestamptz จะเปลี่ยนจุดเวลาสัมบูรณ์ ไปเป็น เขตเวลานั้น และคืนค่า timestamp แบบธรรมดา (เวลาตามนาฬิกาท้องถิ่นของเขตเวลานั้น)
เมื่อนำไปใช้กับ timestamp แบบธรรมดา จะ ตีความ เวลาตามนาฬิกานั้นว่าอยู่ในเขตเวลานั้น และคืนค่าเป็น timestamptz

เคล็ดลับทั้งหมดอยู่ที่การรู้ว่าการทำงานเป็นไปในทิศทางใด

-- timestamptz -> local wall clock (returns timestamp)
SELECT TIMESTAMPTZ '2024-03-01 12:00:00+00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 21:00:00

-- plain timestamp interpreted in a zone (returns timestamptz)
SELECT TIMESTAMP '2024-03-01 12:00:00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 03:00:00+00

การหาจุดเวลาปัจจุบัน

ควรเข้าใจฟังก์ชันสำหรับเวลาปัจจุบันของคุณ NOW() และ CURRENT_TIMESTAMP คืนค่าเป็น timestamptz ในโพสต์เกรส โดยคืนเวลาของ จุดเริ่มต้นธุรกรรม ไม่ใช่เวลาของคำสั่ง ซึ่งมีความสำคัญเมื่อธุรกรรมใช้เวลานาน

หากต้องการ UTC โดยเฉพาะ ให้แปลงด้วย NOW() AT TIME ZONE 'UTC' ใน MySQL ฟังก์ชัน UTC_TIMESTAMP() จะให้ค่า UTC โดยตรง

SELECT
  NOW()                       AS tx_start_tz,
  NOW() AT TIME ZONE 'UTC'    AS utc_walltime;

เวลาออมแสงคือศัตรูตัวจริง

ผู้สัมภาษณ์ชอบถามกรณีขอบของ DST เมื่อนาฬิกาเดินไปข้างหน้า ชั่วโมงหนึ่งตามเวลาท้องถิ่น ไม่มีอยู่จริง และเมื่อเดินย้อนกลับ ชั่วโมงนั้นจะ เกิดซ้ำ การจัดเก็บเวลาท้องถิ่นจึงทำให้ค่าเหล่านี้กำกวมหรือไม่ถูกต้อง

การจัดเก็บ UTC ช่วยหลีกเลี่ยงปัญหานี้ได้ทั้งหมด เพราะทุกจุดเวลามีเอกลักษณ์และเรียงเพิ่มขึ้นอย่างต่อเนื่อง การระบุชื่อภูมิภาค เช่น 'America/New_York' (แทนค่าชดเชยตายตัวอย่าง -05:00) ทำให้ฐานข้อมูลใช้กฎ DST ได้อย่างถูกต้องกับทุกวันที่

-- Region name applies DST automatically for the given date
SELECT TIMESTAMPTZ '2024-07-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS summer, -- EDT (-04)
       TIMESTAMPTZ '2024-01-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS winter; -- EST (-05)

การจัดกลุ่มตามวันท้องถิ่นข้ามเขตเวลา

ปัญหาที่พบได้จริงคือ "ผู้ใช้ที่ใช้งานอยู่รายวันตามเวลาท้องถิ่นของผู้ใช้แต่ละคน" หากตัดทอนค่าการประทับเวลา UTC โดยตรง ขอบเขตเที่ยงคืนจะไม่ถูกต้องสำหรับผู้ใช้ในเขตเวลาอื่นที่ไม่ใช่ UTC

ให้แปลงเป็นเขตเวลาของผู้ใช้ก่อน แล้วจึงตัดทอนเป็นวัน การแปลงจะเลื่อนเวลาตามนาฬิกาท้องถิ่น ทำให้ขอบเขตวันสอดคล้องกับเวลาท้องถิ่น

SELECT
  DATE_TRUNC('day', occurred_at AT TIME ZONE u.tz) AS local_day,
  COUNT(DISTINCT e.user_id)                         AS dau
FROM events e
JOIN users u ON u.id = e.user_id
GROUP BY 1
ORDER BY 1;

การเปรียบเทียบค่าการประทับเวลาอย่างปลอดภัย

เมื่อกรองคอลัมน์ timestamptz ให้เปรียบเทียบกับจุดเวลาที่ระบุอย่างชัดเจน โดยควรใช้ค่าคงที่ UTC หรือค่า timestamptz ที่มีค่าชดเชย การเปรียบเทียบกับข้อความที่ไม่มีการระบุเขตเวลาอาจถูกตีความตามเขตเวลาของเซสชันอย่างคาดเดาไม่ได้

วิธีนี้ทำให้การเปรียบเทียบไม่กำกวม ไม่ว่าใครจะเป็นผู้เรียกใช้แบบสอบถาม

SELECT *
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2024-03-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2024-04-01 00:00:00+00';

ค่า Epoch และการประทับเวลาแบบยูนิกซ์

หลายระบบจัดเก็บเวลาเป็น Unix epoch (จำนวนวินาทีนับตั้งแต่ 1970-01-01 UTC) ผู้สัมภาษณ์อาจให้คอลัมน์จำนวนเต็มมาแล้วขอให้คุณตีความค่าในคอลัมน์นั้น

โพสต์เกรส: TO_TIMESTAMP(epoch_seconds) คืนค่าเป็น timestamptz
แปลงกลับเป็น epoch: EXTRACT(EPOCH FROM occurred_at)
MySQL: FROM_UNIXTIME() และ UNIX_TIMESTAMP()

ค่า Epoch เป็น UTC โดยธรรมชาติ ซึ่งเป็นเหตุผลหนึ่งที่ทำให้เหมาะกับการจัดเก็บและได้รับความนิยม

SELECT
  TO_TIMESTAMP(1709294400)               AS as_ts,   -- from epoch
  EXTRACT(EPOCH FROM NOW())::bigint        AS as_epoch; -- to epoch

ข้อสังเกตเรื่องเขตเวลาระหว่างระบบ

แผนผังอย่างรวดเร็วเพื่อให้คุณอธิบายได้อย่างคล่องแคล่วในทุกสภาพแวดล้อม:

โพสต์เกรส: timestamptz และ AT TIME ZONE ซึ่งรองรับได้ครบถ้วนที่สุด
MySQL: TIMESTAMP จะแปลงค่าโดยอัตโนมัติผ่าน time_zone ของเซสชัน ส่วน CONVERT_TZ(t, from, to) ใช้แปลงอย่างชัดเจน ขณะที่ DATETIME ไม่รับรู้เขตเวลา
เซิร์ฟเวอร์ SQL: datetimeoffset จัดเก็บค่าชดเชย และ AT TIME ZONE 'name' ใช้ชื่อเขตเวลาของ Windows ในการแปลง

-- MySQL explicit conversion
SELECT CONVERT_TZ(event_dt, 'UTC', 'Europe/Istanbul') AS local_dt
FROM events;

ตัวอย่างเชิงลึก: เซสชันที่ข้ามเที่ยงคืน

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

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

SELECT
  DATE_TRUNC('day', started_at AT TIME ZONE 'Europe/Istanbul') AS local_day,
  COUNT(*) AS sessions
FROM sessions
GROUP BY 1
ORDER BY 1;

ตรวจสอบความเข้าใจ

ยืนยันแนวทางการจัดเก็บที่แนะนำและเหตุผล

ตอบคำถามในบทเรียนแบบโต้ตอบ

สรุป: เขตเวลาและค่าการประทับเวลา

หลักการสำคัญที่ควรนำไปใช้:

จัดเก็บ UTC เป็น timestamptz และแปลงเป็นเขตเวลาที่ระบุชื่อเฉพาะตอนแสดงผล
timestamptz จัดเก็บจุดเวลา UTC ไม่ใช่เขตเวลา แม้ชื่อจะทำให้เข้าใจเช่นนั้น
AT TIME ZONE ทำงานได้ทั้งสองทิศทางตามชนิดของข้อมูลนำเข้า โดยแปลงค่า timestamptz เป็นเวลาตามนาฬิกาท้องถิ่น หรือตีความ timestamp แบบธรรมดาว่าอยู่ในเขตเวลาใดเขตเวลาหนึ่ง
ใช้ชื่อภูมิภาค เช่น 'America/New_York' เพื่อให้ใช้ DST โดยอัตโนมัติ และหลีกเลี่ยงค่าชดเชยตายตัว
แปลงเป็นเวลาท้องถิ่นก่อนตัดทอนเป็นวัน และเปรียบเทียบคอลัมน์กับจุดเวลา UTC ที่ระบุอย่างชัดเจน

เริ่มต้นได้ฟรี

เรียนรู้ SQL ด้วย AI tutor — ฟรี

เขียนและเรียกใช้โค้ดจริงในเบราว์เซอร์ของคุณ รับความช่วยเหลือทันทีจาก AI tutor 24/7 และเรียนรู้ต่อจากที่คุณหยุดบนเว็บหรือในแอป

คอร์ส: 30
บทเรียน: 120

เริ่มเรียนฟรี ดูหลักสูตรเต็ม

คำถามที่พบบ่อย

บทเรียน “เขตเวลาและประทับเวลา” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “เขตเวลาและประทับเวลา” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “เขตเวลาและประทับเวลา”

จัดเก็บเวลาเป็น UTC แปลงเขตเวลา และทำความเข้าใจประเด็นเกี่ยวกับประทับเวลาที่ผู้สัมภาษณ์มักถาม คุณปฏิบัติ SQL Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Interview Prep หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน

บทเรียน “เขตเวลาและประทับเวลา” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Interview Prep นี้ได้ไหม

ได้ บทเรียน SQL Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

← กลับไปที่ SQL Interview Prep