การสืบค้นตามช่วงเวลา

นี่ก็เป็นอีกหนึ่งบทความใน “ซีรีย์คิวรี่ตามคำขอ” เพราะได้รับคำถามมา ก่อนอื่นผู้อ่านพบว่ามีผู้พัฒนาจำนวนไม่น้อยเก็บข้อมูล datetime เป็น string เพราะอาจไม่ชำนาญในการจัดการข้อมูล datetime เลยสตาฟมันไว้ในรูปแบบ string แทน ซึ่งกลายเป็นภาระในการสืบค้นเพราะเมื่อต้องการนำมาใช้ประโยชน์ก็ต้องหาทางสกัดข้อมูลจาก string มาเป็น datetime ก่อนอยู่ดี

เหตุผลที่นักพัฒนาตัดรำคาญด้วยการเก็บข้อมูล datetime ในรูปแบบ string เป็นเพราะการป้อนข้อมูล datetime นั้นมักจะป้อนเป็น string แล้วระบบจะจัดเก็บเป็น datetime ให้เอง หากแต่ string ที่ป้อนเข้ามาไม่มีความเป็นกลาง (Neutral date format) แต่ไปขึ้นอยู่กับ Locale ของฝั่ง Client

Microsoft SQL Server สามารถจำลอง Locale ฝั่ง Client ได้ผ่านคำสั่ง SET LANGUAGE

DECLARE @myDate date;

SET LANGUAGE British;
SET @myDate='05/08/2019'
SELECT @myDate as BritishEng;

SET LANGUAGE US_ENGLISH;
SET @myDate='05/08/2019'
SELECT @myDate as USEng;

ผลลัพธ์ที่ได้

Changed language setting to British.
BritishEng
----------
2019-08-05

Changed language setting to us_english.
USEng
----------
2019-05-08

จะเห็นว่าเมื่อป้อน '05/08/2019' ลงไปหากฝั่ง Client มี Locale เป็น British แล้วจะหมายถึง 2019-08-05 หรือวันที่ 5 สิงหาคม 2562 แต่หากฝั่ง Client มี Locale เป็น US แล้วจะหมายถึง 2019-05-08 หรือวันที่ 8 พฤษภาคม 2562

แม้ว่าจะป้อนข้อมูลเหมือนกันแต่มาจาก Client ต่าง Locale กันทำให้การจัดเก็บข้อมูล date ลงบนฝั่ง SQL Server ต่างวันกันดังแสดง

แต่หากผู้ใช้เปลี่ยนการป้อนข้อมูลวันที่ที่เป็นกลาง อาทิ ‘Aug 5, 2019’ , ‘August 5, 2019’ , ‘5 Aug 2019’ , ‘5 August 2019’ , ‘2019-08-05’, ‘2019.08.05’, ‘20190805’ ไม่ว่าจะมาจาก Client ต่าง Locale กันก็จะไม่มีปัญหา ดังตัวอย่าง

DECLARE @myDate date;

SET LANGUAGE British;
SET @myDate='Aug 5, 2019'
SELECT @myDate as BritishEng;

SET LANGUAGE US_ENGLISH;
SET @myDate='Aug 5, 2019'
SELECT @myDate as USEng;

หรือ

DECLARE @myDate date;

SET LANGUAGE British;
SET @myDate='2019.08.05'
SELECT @myDate as BritishEng;

SET LANGUAGE US_ENGLISH;
SET @myDate='2019.08.05'
SELECT @myDate as USEng;

ผลลัพธ์ที่ได้คือ

Changed language setting to British.
BritishEng
----------
2019-08-05

Changed language setting to us_english.
USEng
----------
2019-08-05

จะเห็นว่าแม้จะมาจาก Client ต่าง Locale กันการป้อนข้อมูลวันที่ที่เป็นกลางจะทำให้ Microsoft SQL Server ยังคงจัดเก็บข้อมูลได้ตรงตามวันที่ต้องการ ผู้เขียนจะใช้การป้อนข้อมูลวันที่ที่เป็นกลางสำหรับการทดสอบทั้งหมดในบทความนี้

การเตรียมข้อมูลตัวอย่าง

ผู้เขียนเตรียมตาราง TestOrders ตามสคริปต์ต่อไปนี้ เพื่อใช้ทดสอบการคิวรี่

CREATE DATABASE TestDB;
GO

USE TestDB;
GO

CREATE TABLE TestOrders
(
       OrderID int IDENTITY(1,1) PRIMARY KEY
,      OrderDate datetime NOT NULL
)
GO

INSERT INTO TestOrders VALUES
       ('Jan 1 ,2019 12:38:45.123')
,      ('Jan 1 ,2019 13:42:59.001')
,      ('Jan 1 ,2019 14:21:41.165')
,      ('Jan 2 ,2019 00:00:00.000')
,      ('Jan 2 ,2019 12:11:55.321')
,      ('Jan 3 ,2019 07:34:26.528')
,      ('Jan 4 ,2019 09:31:52.847')
GO

Note:
หากชนิดข้อมูลเป็น datetime แต่ป้อนเพียงแค่วันที่เวลาก็จะเป็น 00:00:00.000 แต่หากป้อนแต่เวลาวันที่ก็จะเป็น 1900-01-01 ดังแสดง

SET NOCOUNT ON;

DECLARE @myDate datetime;

SET @myDate='Jan 1, 2019';
SELECT @myDate as MyDate;

SET @myDate='13:59:00.000';
SELECT @myDate as MyDate;

ผลลัพธ์ที่ได้คือ

MyDate
-----------------------
2019-01-01 00:00:00.000

MyDate
-----------------------
1900-01-01 13:59:00.000

หากต้องการสืบค้นใบสั่งซื้อในวันที่ 1 มกราคม 2519 โดยป้อนเพียงวันที่จะไม่ได้ผลลัพธ์แม้แต่แถวข้อมูลเดียวดังแสดง

SELECT * FROM TestOrders

WHERE OrderDate='Jan 1,2019';

ผลลัพธ์ที่ได้

OrderID OrderDate
----------------------------------------------

เหตุผลเพราะหากป้อนเพียง ‘Jan 1, 2019’ จะหมายถึง ‘Jan 1, 2019 00:00:00.000’ ซึ่งไม่ตรงกับแถวข้อมูลใดเลย การสืบค้นที่ควรคือระบุเป็นช่วงข้อมูลดังนี้

SELECT * FROM TestOrders

WHERE OrderDate>='Jan 1,2019' AND OrderDate< 'Jan 2,2019';

ผลลัพธ์ที่ได้

OrderID           OrderDate
-----------                                -----------------------
1                 2019-01-01 12:38:45.123
2                 2019-01-01 13:42:59.000
3                 2019-01-01 14:21:41.167

(3 rows affected)

บางท่านท้วงติงว่าทำไมไม่ใช้ BETWEEN แทน ตัวดำเนินการ BETWEEN ไม่สามารถใช้ได้เพราะจะหมายถึง

‘Jan 1 , 2019 00:00:00.000’ <= OrderDate <=’Jan 2, 2019 00:00:00.000’

ซึ่งจะมีแถวข้อมูลของวันที่ 2 มกราคม 2019 เวลาเที่ยงคืนปนมาด้วย ดังแสดง

SELECT * FROM TestOrders
WHERE OrderDate BETWEEN 'Jan 1,2019' AND 'Jan 2,2019';

ผลลัพธ์ที่ได้

OrderID           OrderDate
-----------                                -----------------------
1           2019-01-01 12:38:45.123
2           2019-01-01 13:42:59.000
3           2019-01-01 14:21:41.167
4           2019-01-02 00:00:00.000

(4 rows affected)

การคิวรี่ที่ไม่แนะนำ

ในกรณีสืบค้นชนิดข้อมูล datetime ที่ผู้เขียนมักพบบ่อย 2 แบบที่ไม่แนะนำมีดังนี้

คิวรี่ที่ไม่แนะนำแบบที่ 1

SELECT * FROM TestOrders

WHERE CONVERT(Date,OrderDate)='Jan 1, 2019';

เป็นการแปลงค่าของคอลัมน์ OrderDate ซึ่งเป็นชนิด datetime ไปเป็น date แล้วค่อยเทียบกับ ‘Jan 1, 2019’

คิวรี่ที่ไม่แนะนำแบบที่ 2

SELECT * FROM TestOrders
WHERE
       YEAR(OrderDate)=2019
AND     MONTH(OrderDate)=1
AND     DAY(OrderDate)=1;

เป็นการสกัดข้อมูลจากคอลัมน์ OrderDate ออกมาเป็น integer ด้วยฟังก์ชัน YEAR, MONTH และ DAY แล้วนำไปเทียบกับเลขจำนวนเต็ม

เพราะทั้งสองแนวทางเป็นการนำเอาข้อมูลจากคอลัมน์มาสร้างเป็นนิพจน์ก่อนนำไปเปรียบเทียบ ทำให้อาจไม่ได้รับประโยชน์จาก Index และมีการประมาณผลแบบ Row-by-Row นั่นเอง