ผู้เขียนเห็นว่ามีผู้ใช้งาน Microsoft SQL Server จำนวนมากที่เข้ามาอบรมกับผู้เขียน ทำการจัดเก็บข้อมูลเกี่ยวกับวันและเวลาเอาไว้เป็นตัวหนังสือ เพียงเพราะหวังจะสตัฟฟ์มันไว้ คือเห็นตอนกรอกอย่างไร จัดเก็บไว้อย่างนั้น เรียกขึ้นมาดูก็สบายใจเพราะมันก็อยู่ของมันอย่างนั้น Show หารู้ไม่ว่าการจัดเก็บลักษณะนั้นส่งผลต่อประสิทธิภาพแค่ไหน เมื่อคุณจัดเก็บลงในคอลัมน์ชนิดตัวหนังสือ หากสร้าง 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 เหล่านี้ดีกว่า หากใช้ถูกไม่ต้องห่วงว่า จะมีข้อมูลบันทึกผิดวัน ผิดเวลาอย่างแน่นอน แถมประโยชน์ในการสืบค้นก็หลากหลาย ผู้เขียนนึกประเด็นเกี่ยวกับข้อมูลวันที่และเวลา ได้อีกมากมาย แล้วจะหาเวลามาเขียนตอนตต่อ ๆ ไปให้ครับ |