สำหรับข้อมูล Datatype ประเภท Date ,DateTime ,Time ,Timestamp ,Year เหล่านี้ จะทำให้เรามึนงงและเป็นปัญหา และจะจัดการได้ยาก ยกตัวอย่างเคสต่าง ๆ เหล่านี้ เช่น
- หาวันเวลา ปัจจุบัน
- ต้องการเปลี่ยน format ของ column Date ให้ส่วนของค่าที่ Query มาได้
- ต้องการที่จะหาวัน ที่ต้องการเมื่อในอดีตที่ผ่านมา
- อยากที่จะคำนวนช่วงเวลา นาที ,ชั่วโมง ,วัน ,เดือน หรือ ปีของ Datetime ทั้ง 2 columns ว่า มีช่วงห่างกันเท่าไหร่
- ต้องการหาวันสุดท้ายของเดือน เช่น 28 ,29 ,30 ,31
- และอื่น ๆ อีกมากมาย
*** Function ต่าง ๆ ที่จะยกตัวอย่าง บาง function จะมาใน MySQL 8.0 อาจจะไม่ทำงานบน MySQL ที่ต่ำกว่า 8.0 ***
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date
ลองเล่น Function ต่าง ๆ กัน
Function ที่หา Date จากปัจจุบัน
/* แสดงวันที่ เวลา ปัจจุบัน */ SELECT NOW(); /* '2020-09-22 09:15:09' */ SELECT CURRENT_TIMESTAMP(); /* '2020-09-22 09:22:11' */ /* แสดงวันที่ปัจจุบัน เท่านั้น */ SELECT CURRENT_DATE(); /* '2020-09-22' */ SELECT CURRENT_DATE; /* '2020-09-22' */ SELECT CURDATE(); /* '2020-09-22' */ /* แสดงเวลา ปัจจุบัน เท่านั้น */ SELECT CURRENT_TIME(); /* '09:21:30' */ SELECT CURRENT_TIME; /* '09:21:30' */ SELECT CURTIME(); /* '09:21:30' */ /* แสดงวัน ของแต่ละเดือน */ SELECT DAY(NOW()); /* 22 */ SELECT DAYOFMONTH(NOW()); /* 22 */ / * แสดงลำดับวัน ของสัปดาห์ */ SELECT DAYOFWEEK(NOW()); /* 3 (คือวัน วันอังคาร) */ /* แสดงวันที่ ลำดับ ใน 1 ปี ( 0 - 365 ) */ SELECT DAYOFYEAR(NOW()); /* 266 (2020-09-22)*/
แปลง หรือ เปลี่ยน format ของ Date
/* แสดง format ของ Date ต้นทาง */
SET @SOURCE_DATE = DATE('2020-09-22');
SELECT @SOURCE_DATE as origin_date; /* '2020-09-22' */
SELECT DATE_FORMAT(@SOURCE_DATE,'%d/%m/%Y') as dmy_date; /* '22/09/2020' */
SELECT DATE_FORMAT(@SOURCE_DATE,'%d/%m/%Y %H:%i:%s') as dmyhms_date; /* '22/09/2020 00:00:00' */
หาวันก่อนหน้า (วันที่ในอดีต) หรือวันถัดไป (วันที่ในอนาคต)
SET @SOURCE_DATE = TIMESTAMP('2020-09-22 00:00:00');
SELECT DATE_ADD(@SOURCE_DATE , INTERVAL 10 MONTH) as add_10_month; /* '2021-07-22 00:00:00' (เพิ่ม 10 วินาที ) */
SELECT DATE_ADD(@SOURCE_DATE , INTERVAL 10 DAY) as add_10_day; /* '2020-10-02 00:00:00' (เพิ่ม 10 วินาที ) */
SELECT DATE_ADD(@SOURCE_DATE , INTERVAL 10 HOUR) as add_10_hour; /* '2020-09-22 10:00:00' (เพิ่ม 10 วินาที ) */
SELECT DATE_ADD(@SOURCE_DATE , INTERVAL 10 MINUTE) as add_10_minute; /* '2020-09-22 00:10:00' (เพิ่ม 10 วินาที ) */
SELECT DATE_ADD(@SOURCE_DATE , INTERVAL 10 SECOND) as add_10_second; /* '2020-09-22 00:00:10' (เพิ่ม 10 วินาที ) */
SELECT DATE_ADD(@SOURCE_DATE , INTERVAL 10 MONTH) as sub_10_month; /* '2019-11-22 00:00:00' ( ลบ 10 เดือน) */
SELECT DATE_ADD(@SOURCE_DATE , INTERVAL 10 DAY) as sub_10_day; /* '2020-09-12 00:00:00' (ลบ10 วัน) */
SELECT DATE_ADD(@SOURCE_DATE , INTERVAL 10 HOUR) as sub_10_hour; /* '2020-09-21 14:00:00' (ลบ 10 ชั่วโมง) */
SELECT DATE_ADD(@SOURCE_DATE , INTERVAL 10 MINUTE) as sub_10_minute; /* '2020-09-21 23:50:00' (ลบ 10 นาที) */
SELECT DATE_ADD(@SOURCE_DATE , INTERVAL 10 SECOND) as sub_10_second; /* '2020-09-21 23:59:50' (ลบ 10 วินาที )*/
คำนวน ปี ,เดือน ,วัน ,ชั่วโมง ,นาที ,วินาที
SET @BEFORE_DATETIME = TIMESTAMP('2020-08-10 10:10:10');
SET @AFTER_DATETIME = TIMESTAMP('2020-09-20 20:20:20');
SELECT DATEDIFF(@AFTER_DATETIME,@BEFORE_DATETIME) as diff_year; /* ห่างกัน 41 วัน */
SELECT TIMESTAMPDIFF(YEAR,@BEFORE_DATETIME,@AFTER_DATETIME) as diff_year; /* ห่างกัน 0 ปี */
SELECT TIMESTAMPDIFF(MONTH,@BEFORE_DATETIME,@AFTER_DATETIME) as diff_month; /* ห่างกัน 1 เดือน */
SELECT TIMESTAMPDIFF(DAY,@BEFORE_DATETIME,@AFTER_DATETIME) as diff_day; /* ห่างกัน 41 วัน */
SELECT TIMESTAMPDIFF(HOUR,@BEFORE_DATETIME,@AFTER_DATETIME) as diff_hour; /* ห่างกัน 994 ชั่วโมง */
SELECT TIMESTAMPDIFF(MINUTE,@BEFORE_DATETIME,@AFTER_DATETIME) as diff_minute; /* ห่างกัน 59650 นาที */
SELECT TIMESTAMPDIFF(SECOND,@BEFORE_DATETIME,@AFTER_DATETIME) as diff_second; /* ห่างกัน 3579010 วินาที */
ตัด ปี ,เดือน ,วัน ,ชั่วโมง ,นาที ,วินาที
SET @TARGET_DATETIME = '2020-09-22 10:15:20'; SELECT DATE(@TARGET_DATETIME) as date_date; /* 2020-09-22 */ SELECT YEAR(@TARGET_DATETIME) as year_date; /* 2020 */ SELECT MONTH(@TARGET_DATETIME) as month_date; /* 9 */ SELECT WEEK(@TARGET_DATETIME) as week_date; /* 38 */ SELECT DAY(@TARGET_DATETIME) as day_date; /* 22 */ SELECT HOUR(@TARGET_DATETIME) as hour_date; /* 10 */ SELECT MINUTE(@TARGET_DATETIME) as minute_date; /* 15 */ SELECT SECOND(@TARGET_DATETIME) as second_date; /* 20 */
สำหรับ Function อื่น ๆ ของ Date & Time ของ MySQL Database สามารถเข้าไปศุกษาเพิ่มเติมได้ที่นี่ “Date and Time Functions”