ข้อมูลชนิด Datetime บน Microsoft SQL Server

ผู้เขียนเห็นว่ามีผู้ใช้งาน 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 ข้อมูลตัวหนังสือไปเป็นวันเวลาก่อนสิ แล้วค่อยนำไปเปรียบเทียบ แบบนี้

SELECT

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

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