ผู้เขียนเห็นว่ามีผู้ใช้งาน Microsoft SQL Server จำนวนมากที่เข้ามาอบรมกับผู้เขียน ทำการจัดเก็บข้อมูลเกี่ยวกับวันและเวลาเอาไว้เป็นตัวหนังสือ เพียงเพราะหวังจะสตัฟฟ์มันไว้ คือเห็นตอนกรอกอย่างไร จัดเก็บไว้อย่างนั้น เรียกขึ้นมาดูก็สบายใจเพราะมันก็อยู่ของมันอย่างนั้น
หารู้ไม่ว่าการจัดเก็บลักษณะนั้นส่งผลต่อประสิทธิภาพแค่ไหน เมื่อคุณจัดเก็บลงในคอลัมน์ชนิดตัวหนังสือ หากสร้าง Index ก็จะเป็นประเภทตัวหนังสือ ซึ่ง Index ประเภทนี้ทำงานได้ช้ากว่า Index ชนิดตัวเลข และชนิดวันเวลา มาก ย้ำว่ามาก ๆ
ลองคิดตามนะครับว่า Operator ที่ใช้เปรียบเทียบข้อมูลในประโยค WHERE ของข้อมูลชนิดตัวหนังสือจะเป็นอะไรได้บ้าง
SELECT ... WHERE stringDate ='10/08/2018 9:15'
SELECT ... WHERE stringDate <>'10/08/2018 9:15'
SELECT ... WHERE stringDate LIKE '10/08/2018%'
หากใช้เครื่องหมาย =,<> ยังพอทน แต่ถ้าใช้เครื่องหมาย >,>=,<,<= อันนี้ผลลัพธ์ที่ได้ไม่พึงประสงค์แน่ ๆ เพราะใน Index ประเภทตัวหนังสือ จะเรียงตาม Sort Order ของรหัสภาษาที่เลือกใช้ เช่นภาษาไทย เรียงจาก ก-ฮ , ๐-๙ , 0-9 , A-Z และ a-z เป็นต้น ไม่ได้เรียงตามปฏิทินแต่อย่างใด
หากใช้ LIKE ก็ยังพอไว้ แต่บางครั้งจำเป็นต้อง SUBSTRING ออกมากหลาย ๆ ส่วน แล้วค่อยใช้ LIKE เปรียบเทียบไปทีละส่วน และค่อยใช้ AND เชื่อมเข้าด้วยกันอาทิ
SELECT ...
WHERE SUBSTRING(stringDate,1,2) IN ('18','19','20')
AND stringDate LIKE'__/08/2018%'
ผู้อ่านบางคน ก็อาจนึกเถียงว่า ก็จัดการ CONVERT ข้อมูลตัวหนังสือไปเป็นวันเวลาก่อนสิ แล้วค่อยนำไปเปรียบเทียบ แบบนี้
WHERE CONVERT(date,stringDate) BETWEEN '20180818' AND '20180820'
ผู้เขียนย้ำอีกครั้งว่าคอลัมน์ที่จัดเก็บเป็นชนิดตัวอักษร หากสร้าง Index ก็จะมี Index ชนิดตัวอักษรจัดเก็บไว้ แต่เราดัน CONVERT ข้อมูลไปเป็นชนิดวันเวลา แล้วค่อยนำไปเปรียบเทียบ แบบนี้ Index ที่จัดเก็บไว้จะไม่ถูกเรียกมาใช้งานนะ
ผู้เขียนจึงขอเน้นย้ำว่าหากต้องการบันทึกข้อมูลเกี่ยวกับวันและเวลา ก็ต้องกำหนดบรรดาชนิดข้อมูลแบบวันและเวลาให้กับคอลัมน์ที่ใช้จัดเก็บข้อมูล การเปรียบเทียบจะเป็นไปตามข้อมูลปฏิทิน เมื่อเกิดการเปรียบเทียบบนคอลัมน์ที่มีการนำไปสร้างเป็น Index ไว้ เมื่อเราสืบค้นข้อมูล Index ก็มีโอกาสที่จะถูกนำมาใช้เพิ่มประสิทธิภาพการสืบค้น
รู้จักกับชนิดข้อมูลแบบวันและเวลา
ชนิดข้อมูลแบบวันและเวลาจะมีเรื่องความละเอียดของเวลาที่จะจัดเก็บให้เลือกใช้แตกต่างกันไปดังนี้
ชนิดข้อมูล datetime
คุณลักษณะ ค่า ค่าตั้งต้น 1900-01-01 00:00:00 รองรับวันที่ระหว่าง 1 มกราคม ปี ค.ศ. 1753 ไปจนถึง 31 ธันวาคม ปี ค.ศ.9999 ช่วงเวลาระหว่าง 00:00:00 ไปจนถึง 23:59:59.997 ความแม่นยำ ปัดให้ลงท้ายด้วย .000, .003, หรือ .007 วินาที
ทดสอบค่าตั้งต้น
ผู้เขียนขอทดสอบคุณลักษณะไปที่ละข้อตั้งแต่ค่าตั้งต้น ด้วยสคริปต์ต่อไปนี้
DECLARE
@xDatetime as datetime='Aug 5, 1974'
, @yDatetime as datetime='09:02';
SELECT @xDatetime as xDatetime ,@yDatetime as yDatetime;
ผลลัพธ์ที่ได้
xDatetime yDatetime 1974-08-05 00:00:00.000 1900-01-01 09:02:00.000
ผู้เขียนได้สร้างตัวแปร @xDatetime เป็นตัวแปรชนิด datetime แต่กำหนดแค่วันที่ลงไป ไม่กำหนดเวลาจะเห็นว่าในส่วนของเวลาจะเป็น 00:00:00.000 ซึ่งเป็นค่าตั้งต้นของเวลา และสำหรับตัวแปร @yDatetime เป็นการกำหนดเวลาลงไป ไม่กำหนดวันที่ จะพบว่าวันที่จะใช้เป็น 1900-01-01 งเป็นค่าตั้งต้นของวันที่
ทดสอบวันที่ที่รองรับ
ผู้เขียนทำการทดสอบด้วยสคริปต์ต่อไปนี้
DECLARE @xDatetime as datetime\='Dec 31, 1752';
ผลลัพธ์ที่ได้
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
เมื่อกำหนดวันที่เป็น 31 ธันวาคม ปี ค.ศ. 1752 ซึ่งต่ำกว่าช่วงวันที่ยอมรับให้แก่ตัวแปร @xDatetime ซึ่งเป็นตัวแปรชนิด datetime จะพบว่าไม่สามารถทำได้เพราะออกนอกขอบเขต (การกำหนดค่าให้กับตัวแปรชนิด datetime เราจะใช้ข้อมูลชนิดตัวหนังสือที่สามารถ Implicit Convert ไปเป็นข้อมูลชนิด datetime โดยอัตโนมัติได้)
ทดสอบอีกครั้งด้วยวันที่ที่อยู่ในขอบเขต ด้วยสคริปต์ต่อไปนี้
DECLARE @xDatetime as datetime\='Jan 1, 1753';
SELECT @xDatetime as xDatetime;
ผลลัพธ์ที่ได้
xDatetime 1753-01-01 00:00:00.000
พบว่าเมื่อกำหนดวันที่ให้กับตัวแปรในช่วง 1 มกราคม ปี ค.ศ. 1753 ไปจนถึง 31 ธันวาคม ปี ค.ศ.9999 ก็สามารถกลับมารันสคริปต์ได้ตามปกติ
ทดสอบความแม่นยำของชนิดข้อมูล datetime
การทดสอบนี้จะเทียบกับชนิดข้อมูล datetime2 ซึ่งมีความแม่นยำสูงกว่าแต่สามารถกำหนดจุดทศนิยมหลัง second ออกไปได้ถึง 7 ตำแหน่ง โดยจะกำหนดไว้เพียง 3 ตำแหน่งให้เท่ากับของ ชนิดข้อมูล datetime ปกติ
ทดสอบครั้งที่ 1 ทดสอบค่าใกล้กับ .xx0 ด้วยสคริปต์ต่อไปนี้
DECLARE
@xDatetime as datetime2(3) \= 'Aug 5, 1974 09:02:59.981'
, @yDatetime as datetime \= 'Aug 5, 1974 09:02:59.981';
SELECT @xDatetime as xDatetime, @yDatetime as yDatetime;
ผลลัพธ์ที่ได้
xDatetime yDatetime 1974-08-05 09:02:59.981 1974-08-05 09:02:59.980
พบว่าตัวแปร @xDatetime เป็นชนิดข้อมูล datetime2 ซึ่งมีความแม่นยำสูงถึง 100 นาโนวินาที แสดงผลได้ตรงกับข้อมูลที่ป้อนให้กับตัวแปร แต่สำหรับตัวแปร @yDatetime เป็นชนิดข้อมูล datetime มีการปัดเศษลงไปหา .xx0
ทดสอบครั้งที่ 2 ทดสอบค่าใกล้กับ .xx3 ด้วยสคริปต์ต่อไปนี้
DECLARE
@xDatetime as datetime2(3) \= 'Aug 5, 1974 09:02:59.982'
, @yDatetime as datetime \= 'Aug 5, 1974 09:02:59.982';
SELECT @xDatetime as xDatetime, @yDatetime as yDatetime;
ผลลัพธ์ที่ได้
xDatetime yDatetime 1974-08-05 09:02:59.982 1974-08-05 09:02:59.983
พบว่าตัวแปร @xDatetime เป็นชนิดข้อมูล datetime2 ยังคงแสดงผลได้ตรงกับข้อมูลที่ป้อนให้กับตัวแปร แต่สำหรับตัวแปร @yDatetime เป็นชนิดข้อมูล datetime มีการปัดเศษขึ้นไปหา .xx3
ทดสอบครั้งที่ 3 ทดสอบค่าใกล้กับ .xx7 ด้วยสคริปต์ต่อไปนี้
DECLARE
@xDatetime as datetime2(3) \= 'Aug 5, 1974 09:02:59.988'
, @yDatetime as datetime \= 'Aug 5, 1974 09:02:59.988';
SELECT @xDatetime as xDatetime, @yDatetime as yDatetime;
ผลลัพธ์ที่ได้
xDatetime yDatetime 1974-08-05 09:02:59.988 1974-08-05 09:02:59.987
พบว่าตัวแปร @xDatetime เป็นชนิดข้อมูล datetime2 ยังคงแสดงผลได้ตรงกับข้อมูลที่ป้อนให้กับตัวแปร แต่สำหรับตัวแปร @yDatetime เป็นชนิดข้อมูล datetime มีการปัดเศษขึ้นไปหา .xx7
จะเห็นว่าชนิดข้อมูล datetime จะปัดเศษขึ้นหรือลงให้เท่ากับ .xx0,.xx3 และ .xx7 แต่ถึงอย่างนั้นก็เพียงพอสำหรับระบบงานธุรกิจทั่วไป เพราะจุดทศนิยม 3 ตำแหน่งหลังจากวินาที ก็ละเอียดระดับ millisecond กันแล้ว จึงไม่จำเป็นต้องขยับไปใช้ชนิดข้อมูล datetime2 เลย
ชนิดข้อมูล datetime2
คุณลักษณะ ค่า ค่าตั้งต้น 1900-01-01 00:00:00 รองรับวันที่ระหว่าง 1 มกราคม ปี ค.ศ. 0001 ไปจนถึง 31 ธันวาคม ปี ค.ศ.9999 จุดทศนิยมหลังวินาที ตั้งแต่ 0-7 ตำแหน่ง ค่าตั้งต้นคือ 7 ความแม่นยำ 100 นาโนวินาที
สำหรับชนิดข้อมูล datetime2 ผู้เขียนจะไม่ทำการทดสอบเกี่ยวกับค่าตั้งต้น และขอบเขตวันที่ที่ทำได้ เพราะการทดสอบดังกล่าวคงกระทำแบบเดียวกันกับที่ทำบนชนิดข้อมูล datetime เพียงเปลี่ยนมาใช้วันที่ต่ำกว่า 1 มกราคม ปี ค.ศ. 0001 ก็เท่านั้นเอง
ทดสอบจุดทศนิยมหลังวินาทีและความแม่นยำ
ชนิดข้อมูล datetime2 นั้นสามารถกำหนดจำนวนจุดทศนิยมที่จะจัดเก็บได้ โดยใส่จำนวนตั้งแต่ 0-7 ลงในวงเล็บหลังชื่อชนิดข้อมูล หากไม่กำหนดจะใช้ 7 เป็นค่าตั้งต้น ผู้เขียนทำการทดสอบ ด้วยสคริปต์ต่อไปนี้
DECLARE @fixDatetime as varchar(50) \= 'Aug 5, 1974 09:02:59.123456789';
DECLARE
@Datetime2_0 as datetime2(0) \= @fixDatetime
, @Datetime2_5 as datetime2(5) \= @fixDatetime
, @Datetime2_6 as datetime2(6) \= @fixDatetime
, @Datetime2_7 as datetime2 \= @fixDatetime;
SELECT
@Datetime2_0 as Datetime2_0
, @Datetime2_5 as Datetime2_5
, @Datetime2_6 as Datetime2_6
, @Datetime2_7 as Datetime2_7;
ผลลัพธ์ที่ได้
Datetime2_0 Datetime2_5 Datetime2_6 Datetime2_7 1974-08-05 09:02:59 1974-08-05 09:02:59.12346 1974-08-05 09:02:59.123457 1974-08-05 09:02:59.1234568
จะพบว่าหากกำหนดจำนวนจุดทศนิยมไว้ 5 ตำแหน่ง แต่ค่าที่ป้อนมีจำนวนจุดทศนิยมมากกว่า 5 ตำแหน่งจะพิจารณาปัดขึ้นหรือไม่ โดยดูว่าจุดทศนิยมตำแหน่งที่ 6 มากกว่า 5 หรือไม่ หากมากกว่าก็ปัดขึ้น และทำเช่นเดียวกันนี้กับการกำหนดจำนวนจุดทศนิยมไว้ 6 หรือ 7 ตำแหน่งดังแสดง (เราประกาศตัวแปร @fixDatetime ให้มีจุดทศนิยมหลังวินาทีไว้ 9 ตำแหน่ง)
แต่หากเราเปลี่ยนจุดทศนิยมหลังวินาทีทั้ง 9 ตำแหน่งเป็น .999999999 จะเกิดอะไรขึ้น
DECLARE @fixDatetime as varchar(50) = 'Aug 5, 1974 09:02:59.999999999';
DECLARE @Datetime2_7 as datetime2 = @fixDatetime;
SELECT @Datetime2_7 as Datetime2_7;
ผลลัพธ์ที่ได้
Datetime2_7 1974-08-05 09:03:00.0000000
ผลลัพธ์ที่ได้ก็คือมันจะปัดขึ้น 1 วินาที จากเดิมเราอยู่ที่วินาทีที่ 59 พอดี เลยมีปัดปัดต่อไปที่หน่อยนาที จากเดิม 09:02:59.999999999 ไปเป็น 09:03:00.0000000 นั่นเอง
ชนิดข้อมูลวันและเวลาแบบอื่น ๆ
ชื่อชนิดข้อมูล ค่าข้อมูล ความละเอียด date เก็บเฉพาะวันที่ ตั้งแต่ 1 มกราคม ปี ค.ศ. 0001 ไปจนถึง 31 ธันวาคม ปี ค.ศ.9999 1 วัน time(7) เก็บเฉพาะเวลา กำหนดจุดทศนิยมหลังวินาทีตั้งแต่ 0 ตำแหน่ง ไปจนถึง 7 ตำแหน่ง 100 นาโนวินาที smalldatetime เก็บทั้งวันที่และเวลา ตั้งแต่ 1 มกราคม ปี ค.ศ. 1900 ไปจนถึง 6 มิถุนายน ปี ค.ศ.2079 1 นาที datetimeoffset(7) ทำได้เหมือนกับ datetime2 และมีค่า offset time ผนวกมาด้วย 100 นาโนวินาที
ผู้เขียนเห็นว่าชนิดข้อมูล date นั้นหากผู้ใช้ไม่มีความจำเป็นต้องเก็บเวลาลงไปด้วยก็เป็นตัวเลือกที่ดี เพราะประหยัดพื้นที่จัดเก็บเพราะใช้เพียง 3 Bytes เท่านั้นเอง แต่หากเป็นชนิดข้อมูล datetime จะใช้พื้นที่จัดเก็บถึง 8 Bytes เลย
ส่วน smalldatetime นั้นหากคิดว่าระบบของเราจะยั่งยืนใช้ไปเกินอีก 61 ปีข้างหน้า (นับจากปี 2018) ก็อาจพิจารณาเปลี่ยนไปใช้เป็น datetime แทน สำหรับ smalldatetime ใช้พื้นที่จัดเก็บ 4 Bytes
สำหรับชนิดข้อมูล time ผู้ใช้มักไม่ค่อยนำมาใช้เท่าไหร่ เพราะความรู้สึกว่าการเก็บข้อมูลวันที่ แยกกันกับข้อมูลเวลา มันน่าจะใช้ประโยชน์ได้ลำบากกว่า
สุดท้ายชนิดข้อมูล datetimeoffset นั้นทำให้แอพพริเคชั่นที่รองรับข้อมูลจากหลายแหล่งข้อมูล หลาย time zone สามารถทำงานเกี่ยวกับเวลาได้อย่างถูกต้อง
ก่อนอื่นผู้เขียนจะสืบค้นชื่อของ time zone ที่มีใน Microsoft SQL Server ออกมาแสดง ด้วยสคริปต์ต่อไปนี้
USE master;
SELECT name, current_utc_offset FROM sys.time_zone_info
WHERE current_utc_offset='+07:00';
ผลลัพธ์ที่ได้
name current_utc_offset SE Asia Standard Time +07:00 Altai Standard Time +07:00 W. Mongolia Standard Time +07:00 North Asia Standard Time +07:00 N. Central Asia Standard Time +07:00 Tomsk Standard Time +07:00
ผู้เขียนได้ทำการกรองให้เห็นเฉพาะ time zone ของประเทศเรา โดยกำหนดค่า UTC offset หรือเวลาเราต่างจาก Coordinated Universal Time (UTC) เท่าไหร่
จากนั้นเรามาทำความเข้าใจการบันทึกข้อมูลเป็น datetimeoffset กัน ก่อนอื่น ๆ ผู้เขียนจะดึงวันเวลาปัจจุบันผ่านฟังก์ชัน SYSDATETIMEOFFSET() ที่ผนวก UTC Offset มาด้วย ดังสคริปต์ต่อไปนี้
DECLARE @xDatetimeoffset as datetimeoffset\=SYSDATETIMEOFFSET()
SELECT @xDatetimeoffset as xDatetimeoffset;
ผลลัพธ์ที่ได้
xDatetimeoffset 2018-08-31 08:23:39.2277519 +07:00
เวลาที่ผู้เขียนรันสคริปต์เป็นเวลา 08:23:39 ในประเทศไทย ผลลัพธ์ที่ได้จากฟังก์ชั่นก็ยังคงเป็นเวลาเดิมเพียงแต่ผนวก UTC Offset ว่าเป็นเท่าไหร่มาด้วย แล้วบันทึกลงฐานข้อมูลไปพร้อมกันคราวเดียวเลย
ผู้เขียนจะทดสอบให้สมจริงมากขึ้นโดยการสร้างตารางขึ้นมาจัดเก็บข้อมูล ด้วยสคริปต์ต่อไปนี้
CREATE TABLE dbo.TestTable
(
no int IDENTITY(1,1) NOT NULL
, Orderdate_offset datetimeoffset (7) NOT NULL
);
จากนั้นผู้เขียนทำการ INSERT ข้อมูลแต่ละรายการเสมือนว่ามีการใส่ข้อมูลมาจากหลายประเทศต้นทางของข้อมูล ด้วยสคริปต์ต่อไปนี้
INSERT INTO TestTable (Orderdate_offset)
VALUES
('2018-08-29 20:23:40 -10:00') --Hawaiian Standard Time
, ('2018-08-29 22:23:40 -08:00') --Alaskan Standard Time
, ('2018-08-29 23:23:40 -07:00') --US Mountain Standard Time
, ('2018-08-30 15:23:40 +09:00') --Tokyo Standard Time
SELECT * FROM TestTable;
ผลลัพธ์ที่ได้
no Orderdate_offset 1 2018-08-29 20:23:40.0000000 -10:00 2 2018-08-29 22:23:40.0000000 -08:00 3 2018-08-29 23:23:40.0000000 -07:00 4 2018-08-30 15:23:40.0000000 +09:00
จะเห็นว่าข้อมูลถูกบันทึกตามที่ได้ INSERT ไว้โดยมี UTC offset ผนวกเอาไว้ จะเห็นว่าเป็นวันและเวลาที่แตกต่างกัน และบันทึกมาจากคนละ Time Zone
ผู้เขียนจะทดลองสืบค้นอีกครั้งโดยใช้ฟังก์ชั่น SWITCHOFFSET เพื่อแสดงผลใน Time Zone ของประเทศไทยเรา ด้วยสคริปต์ต่อไปนี้
SELECT
no,Orderdate_offset
, SWITCHOFFSET(Orderdate_offset,'+07:00') as ThaiTime
FROM TestTable;
ผลลัพธ์ที่ได้
no Orderdate_offset ThaiTime 1 2018-08-29 20:23:40.0000000 -10:00 2018-08-30 13:23:40.0000000 +07:00 2 2018-08-29 22:23:40.0000000 -08:00 2018-08-30 13:23:40.0000000 +07:00 3 2018-08-29 23:23:40.0000000 -07:00 2018-08-30 13:23:40.0000000 +07:00 4 2018-08-30 15:23:40.0000000 +09:00 2018-08-30 13:23:40.0000000 +07:00
จะพบว่าข้อมูลที่บันทึกไว้ต่างวันและเวลาต่าง Time Zone กัน แต่เนื่องจากมี UTC offset ผนวกไว้ทำให้สามารถคำนวณเพื่อแสดงผลใน Time Zone ที่ต้องการได้ไม่ยาก ตัวอย่างเช่นในรายการที่ 1 บันทึกจากหมู่เกาะฮาวาย ในเวลา 2018-08-29 20:23:40 -10:00 ซึ่งจะห่างจากประเทศไทยคือ นำ Offset Time มาหาความต่างจาก -10: 00 จนถึง +07:00 ได้เท่ากับ 10+7=17 ชั่วโมง นำเอา 17 ชั่วโมงที่ได้ไปบวก ให้กับเวลาจากหมู่เกาะฮาวาย ก็จะได้เวลาของประเทศไทย
การสืบค้นวันเวลาปัจจุบัน
ฟังก์ชั่นที่สามารถดึงวันและเวลาปัจจุบันออกจากเครื่องที่ Microsoft SQL Server ติดตั้งอยู่ มีดังนี้
ฟังก์ชั่น ชนิดข้อมูล หมายเหตุ GETDATE() datetime วันเวลาปัจจุบัน ไม่ผนวก UTC offset Current_timestamp datetime วันเวลาปัจจุบันตามมาตฐาน ANSI ไม่ผนวก UTC offset GETUTCDATE() datetime วันเวลาปัจจุบัน ณ GMT จะพบว่าข้อมูลที่บันทึกไว้ต่างวันและเวลาต่าง Time Zone กัน แต่เนื่องจากมี UTC offset ผนวกไว้ทำให้สามารถคำนวณเพื่อแสดงผลใน Time Zone ที่ต้องการได้ไม่ยาก ตัวอย่างเช่นในรายการที่ 1 บันทึกจากหมู่เกาะฮาวาย ในเวลา 2018-08-29 20:23:40 -10:00 ซึ่งจะห่างจากประเทศไทยคือ นำ Offset Time มาหาความต่างจาก -10: 00 จนถึง +07:00 ได้เท่ากับ 10+7=17 ชั่วโมง นำเอา 17 ชั่วโมงที่ได้ไปบวกให้กับเวลาจากหมู่เกาะฮาวาย ก็จะได้เวลาของประเทศไทย การสืบค้นวันเวลาปัจจุบัน ฟังก์ชั่นที่สามารถดึงวันและเวลาปัจจุบันออกจากเครื่องที่ Microsoft SQL Server ติดตั้งอยู่ มีดังนี้ Time Zone (+00:00) SYSDATETIME() Datetime2 วันเวลาปัจจุบัน ไม่ผนวก UTC offset SYSUTCDATETIME() Datetime2 วันเวลาปัจจุบัน ณ GMT Time Zone (+00:00) SYSDATETIMEOFFSET() datetimeoffset วันเวลาปัจจุบัน ผนวก UTC offset
ทดสอบสืบค้นวันเวลาปัจจุบันจากฟังก์ชันที่คืนค่าเป็น datetime ด้วยสคริปต์ต่อไปนี้
SELECT
GETDATE() as aDatetime
, CURRENT_TIMESTAMP as bDatetime
, GETUTCDATE() as cDatetime;
ผลลัพธ์ที่ได้
aDatetime bDatetime cDatetime 2018-08-31 09:56:01.993 2018-08-31 09:56:01.993 2018-08-31 02:56:01.993
สำหรับผลลัพธ์ของฟังก์ชัน GETDATE() และ CURRENT_TIMESTAMP เหมือนกันทุกประการเพียงแค่ CURRENT_TIMESTAMP สามารถนำไปใช้กับ RDBMS อื่น ๆ เช่น ORACLE ได้ เพราะเป็นฟังก์ชันตามมาตรฐาน ANSI ส่วนฟังก์ชัน GETUTCDATE() ให้ผลเป็น เวลา ณ GMT Time Zone (+00:00) ออกมา จึงลดเวลาลง 7 ชั่วโมง
ทดสอบสืบค้นวันเวลาปัจจุบันจากฟังก์ชันที่คืนค่าเป็น datetime2 ด้วยสคริปต์ต่อไปนี้
SELECT
SYSDATETIME() as dDatetime
, SYSDATETIMEOFFSET() as eDatetime
, SYSUTCDATETIME() as fDatetime;
ผลลัพธ์ที่ได้
dDatetime eDatetime fDatetime 2018-08-31 10:04:35.9309809 2018-08-31 10:04:35.9309809 +07:00 2018-08-31 03:04:35.9309809
สำหรับผลลัพธ์ของฟังก์ชัน SYSDATETIME() และ SYSDATETIMEOFFSET() ได้ผลลัพธ์เหมือนกัน เพียงแต่อ SYSDATETIMEOFFSET() นั้นมี UTC offset ผนวกมาด้วย ส่วนฟังก์ชัน SYSUTCDATETIME() ให้ผลเป็น เวลา ณ GMT Time Zone (+00:00) ออกมา จึงลดเวลาลง 7 ชั่วโมง ผลลัพธ์ทั้งหมดมีจุดทศนิยม 7 ตำแหน่งจากวินาที และความละเอียด 100 นาโนวินาที
การสืบค้นข้อมูลวันและเวลาที่มักพบความผิดพลาด
ข้อมูลวันและเวลาที่ป้อนเข้ามาไม่เป็นกลาง
ก่อนอื่นผู้อ่านต้องเข้าใจก่อนว่าเมื่อทำการสืบค้นข้อมูล Microsoft นำรหัสภาษาจากฝั่ง Client (เครื่องผู้ใช้) มากำหนดให้กับการสืบค้น ผู้เขียนจะจำลองสถานการณ์ ที่ฝั่ง Client มีรหัสภาษาไม่เหมือนกัน แต่ป้อนข้อมูลวันเวลาแบบไม่เป็นกลางเข้าไป ผลที่ได้จะเป็นอย่างไร ด้วยสคริปต์ต่อไปนี้
DECLARE @strDate varchar(15)='12/8/2018'
SET LANGUAGE THAI
SELECT CONVERT(date,@strDate);
ผลลัพธ์ที่ได้ 2018-08-12
จากนั้นจำลองเปลี่ยนรหัสภาษาของเครื่องเป็น us_english ด้วยสคริปต์ต่อไปนี้
DECLARE @strDate varchar(15)='12/8/2018'
SET LANGUAGE us_english
SELECT CONVERT(date,@strDate);
ผลลัพธ์ที่ได้
2018-12-08
ผลลัพธ์ที่ได้แสดงผลตามมาตรฐาน ANSI คือมี Format เป็น YYYY-MM-DD จะเห็นว่าค่าตั้งต้นเหมือนกันคือ 12/8/2018 สำหรับ Client ที่ใช้รหัสภาษาเป็น THAI จะได้ข้อมูลเป็นวันที่ 12 สิงหาคม 2018 แต่สำหรับ Client ที่ใช้รหัสภาษาเป็น us_english จะได้ข้อมูลเป็นวันที่ 8 ธันวาคม 2018 นี่เองกระมังที่ทำให้คนหันไปบันทึกข้อมูลวันเวลาในรูปแบบตัวอักษรแทน เพราะบางผู้ใช้มาจากเครื่องที่ใช้รหัสภาษาไม่ตรงกันทำให้ข้อมูลที่ป้อนเข้ามามีบางส่วนถูกบ้าง บางส่วนผิดบ้างผสมกันไป
ทางแก้ไขสำหรับเรื่องนี้มีสองทางเลือกด้วยกัน
- บังคับให้เครื่องของผู้ใช้ ใช้รหัสภาษาเดียวกันทั้งองค์กร
- หรือเพียงแค่ป้อนข้อมูลที่มีความเป็นกลาง ก็จบปัญหานี้ได้
ทดลองอีกครั้งด้วยข้อมูลที่มีความเป็นกลาง ด้วยสคริปต์ต่อไปนี้
DECLARE
@strDate varchar(15)='20180805'
SET LANGUAGE THAI
SELECT CONVERT(date,@strDate);
ผลลัพธ์ที่ได้
2018-08-05
จากนั้นจำลองเปลี่ยนรหัสภาษาของเครื่องเป็น us_english ด้วยสคริปต์ต่อไปนี้
DECLARE @strDate varchar(15)='20180805'
SET LANGUAGE us_english
SELECT CONVERT(date,@strDate);
ผลลัพธ์ที่ได้
2018-08-05
จะเห็นว่าผลลัพธ์ที่ได้ตรงกันเพราะการป้อนลักษณะ YYYMMDD นั้นเป็นข้อมูลวันเวลาที่มีความเป็นกลาง นอกเหนือจากนั้น ผู้เขียนยังมีลักษณะการป้อนที่มีความเป็นกลางให้เลือกใช้อีกมากมายดังนี้
SELECT CONVERT(Date,'Aug 5,1974');
SELECT CONVERT(Date,'August 5,1974');
SELECT CONVERT(Date,'5 Aug 1974');
SELECT CONVERT(Date,'5 August 1974');
SELECT CONVERT(Date,'19740805');
SELECT CONVERT(Date,'1974/08/05');
SELECT CONVERT(Date,'1974-08-05');
SELECT CONVERT(Date,'1974.08.05');
ข้อมูลเป็น Datetime แต่สืบค้นโดยใช้วันเทียบเพียงอย่างเดียว
ผู้เขียนจะทดลองสร้างตารางที่มีข้อมูลชนิด datetime บันทึกอยู่ ด้วยสคริปต์ต่อไปนี้
CREATE TABLE dbo.TestTable2
(
no int IDENTITY(1,1) NOT NULL
, Orderdate datetime NOT NULL
);
INSERT INTO dbo.TestTable2 (Orderdate)
VALUES
('2018-05-01 09:59:31')
, ('2018-05-01 10:10:09')
, ('2018-05-02 14:48:11')
, ('2018-05-03 11:12:15')
, ('2018-05-03 17:59:59')
, ('2018-05-04 08:00:00');
จากนั้นผู้เขียนทดลองสืบค้น ด้วยสคริปต์ต่อไปนี้
SELECT *
FROM TestTable2
WHERE Orderdate='2018-05-03';
ผลลัพธ์ที่ได้
(0 rows affected)
จะเห็นว่าข้อมูลของวันที่ 3 พฤษภาคม 2018 มีด้วยกัน 2 รายการ แต่หากป้อนเพียงวันที่ลงไปเปรียบเทียบ มันจะถูก CONVERT เป็น '2018-05-03 00:00:00.000' ซึ่งไม่ตรงกับที่บันทึกไว้ จึงสืบค้นไม่ได้ผลลัพธ์ใดออกมา
คราวนี้ก็เกิดการสืบค้นหลายรูปแบบที่ไม่มีประสิทธิภาพ ดังสคริปต์ต่อไปนี้
ตัวอย่างที่ 1
SELECT *
FROM TestTable2
WHERE CONVERT(date,Orderdate) ='2018-05-03';
ตัวอย่างที่ 2
SELECT *
FROM TestTable2
WHERE YEAR(Orderdate)=2018 AND MONTH(Orderdate)=5 AND DAY(Orderdate)=3;
ทั้งสองตัวอย่างได้ผลลัพธ์เหมือนกัน คือ
no Orderdate 4 2018-05-03 11:12:15.000 5 2018-05-03 17:59:59.000
ตั้งต้นด้วยหากมีการสร้าง Index จากข้อมูลในคอลัมน์ OrderDate จะเป็น Index ชนิด Datetime แต่การ CONVERT ค่าในคอลัมน์ OrderDate ไปเป็น date แล้วค่อยไปเปรียบเทียบ ในตัวอย่างที่ 1 การกระทำแบบนี้ถึงแม้เราสร้าง Index ไว้ แต่จะไม่ถูกใช้งาน เพราะเป็นกฎพื้นฐานอยู่แล้วว่า อย่า CONVERT ค่าในคอลัมน์ก่อนนำไปเปรียบเทียบ มันจะทำงานแบบ Row by Row และไม่ใช้ Index ในตัวอย่างที่ 2 ก็เช่นกันมีการสกัดเลขปี เลขเดือน และเลขวัน ออกจากคอลัมน์ OrderDate แล้วค่อยนำไปเทียบกับ Integer มันจะทำงานแบบ Row by Row และไม่ใช้ Index เหมือนกันเลย
ที่ถูกต้องเราควรสืบค้นเป็นช่วงของวันเวลา ดังสคริปต์ต่อไปนี้
SELECT *
FROM TestTable2
WHERE Orderdate>='2018-05-03' AND Orderdate<'2018-05-04';
ผลลัพธ์ที่ได้ยังคงเหมือนกับตัวอย่างที่ 1 และ 2 และหากมีการสร้าง Index จากข้อมูลในคอลัมน์ OrderDate เอาไว้ Index ก็จะถูกพิจารณาเลือกใช้ แถมการประมวลผลจะทำคราวเดียวทั้งตาราง
ฟังก์ชันแนะนำ
ฟังก์ชัน PARSE()
สุดยอดฟังก์ชันที่ผู้เขียนจะแนะนำให้รู้จักคือฟังก์ชัน PARSE() เป็นฟังก์ชันที่ทำการแปลงข้อมูล string ไปเป็น datetime บางคนก็จะเกิดข้อสงสัยว่าจะมีฟังก์ชันนี้ทำไม ในเมื่อการแปลงข้อมูล string ไปเป็น datetime เกิดขึ้นอัตโนมัติผ่าน Implicit Conversion อยู่แล้ว ตัวอย่างที่ผ่าน ๆ มาของผู้เขียน ก็แสดงให้เห็นว่ามันทำอัตโนมัติ ทำไม่ถึงต้องมี ผู้เขียนขอแสดงให้เห็นผ่านสคริปต์ต่อไปนี้
SELECT PARSE(N'31 สิงหาคม 2561' as datetime using 'th-TH');
ผลลัพธ์ที่ได้ 2018-08-31 00:00:00.000
จะเห็นว่าเราสามารถป้อนข้อมูลทั้งวัน เดือน และปีพุทธศักราช ลงไป มันจะแปลงเป็นวันและเวลาตามมาตรฐาน ANSI แน่นอนเป็นปีคริสตศักราช
นอกเหนือจากนั้นหากผู้เขียนลองเปลี่ยนวันที่ และเดือน ดังสคริปต์ต่อไปนี้
SELECT PARSE(N'29 กุมภาพันธ์ 2561' as datetime using 'th-TH');
ผลลัพธ์ที่ได้
Msg 9819, Level 16, State 1, Line 1
Error converting string value '29 กุมภาพันธ์ 2561' into data type datetime using culture 'th-TH'.
แต่หากลองเปลี่ยนปี ไปเป็นปี 2559 แทน ดังสคริปต์ต่อไปนี้
SELECT PARSE(N'29 กุมภาพันธ์ 2559' as datetime using 'th-TH');
ผลลัพธ์ที่ได้
2016-02-29 00:00:00.000
เพราะปี ค.ศ. 2016 หรือปี พ.ศ.2559 นั้นเป็นเป็นปีอธิกสุรทิน นั่นแสดงว่าฟังก์ชัน PARSE() ไม่เพียงแต่แปลงข้อมูลจาก Culture ต่าง ๆ จากตัวอย่างคือ th-TH ยังตรวจเช็คค่าในปฏิทินว่ามีอยู่จริงหรือไม่อีกด้วย
ฟังก์ชัน FORMAT()
เป็นฟังก์ชันที่แปลงข้อมูล datetime ไปเป็น string วัตถุประสงค์เพื่อแสดงผลออกมาถูกใจผู้ใช้ ผู้เขียนขอแสดงให้เห็นผ่านสคริปต์ต่อไปนี้
DECLARE @myDate datetime \='Aug 5,2018 9:02'
SELECT FORMAT(@myDate,N'dd MMMM พ.ศ. yyyy เวลา HH โมง mm นาที','th-TH');
ผลลัพธ์ที่ได้
05 สิงหาคม พ.ศ. 2561 เวลา 09 โมง 02 นาที
ในส่วนนี้ผู้เขียนได้สร้าง FORMAT ขึ้นมาเอง ไม่ได้ใช้ค่าตั้งต้นที่ Microsoft SQL Server เตรียมไว้ให้ จะเห็นว่าข้อมูลแสดงปีพุทธศักราชได้อย่างถูกต้องอีกด้วย
ฟังก์ชัน DATENAME()
ผู้เขียนใช้ฟังก์ชันนี้ในการดึงชื่อของเดือน และวันในสัปดาห์ออกมาแสดง แถมให้แสดงเป็น เดือนไทย และวันไทย โดยใช้คำสั่ง SET LANGUAGE ช่วย ดังสคริปต์ต่อไปนี้
SET LANGUAGE ENGLISH;
DECLARE @myDate datetime \='Aug 5,2018 9:02';
SET LANGUAGE THAI;
SELECT DATENAME(MONTH,@myDate) as MonthName, DATENAME(WEEKDAY,@myDate) as DayName;
ผลลัพธ์ที่ได้
MonthName DayName สิงหาคม อาทิตย์
จะเห็นว่า Microsoft SQL Server ก็สามารถแสดงไทย ชื่อเดือนไทย ปีพุทธศักราช ได้อย่างถูกต้อง ไม่ต้องไปนั่งใช้ นิพจน์ CASE หรือฟังก์ชัน IIF() เพื่อแปลงเป็นไทยกันหรอก ผู้เขียนเห็นโค๊ดแบบนั้นที่ไหนก็คันมืออยากจะแก้ให้ทุกทีไป
สรุป
ผู้เขียนส่งเสริมให้เกิดข้อมูลวันที่และเวลา เป็นชนิดข้อมูลบรรดา datetime, datetime2 ,datetimeoffset เหล่านี้ดีกว่า หากใช้ถูกไม่ต้องห่วงว่า จะมีข้อมูลบันทึกผิดวัน ผิดเวลาอย่างแน่นอน แถมประโยชน์ในการสืบค้นก็หลากหลาย ผู้เขียนนึกประเด็นเกี่ยวกับข้อมูลวันที่และเวลา ได้อีกมากมาย แล้วจะหาเวลามาเขียนตอนตต่อ ๆ ไปให้ครับ