MySQL: แปลงวันที่ YYYY-MM-DD เป็น UNIX Timestamp ด้วย UNIX_TIMESTAMP

สร้างตารางและข้อมูลตัวอย่าง

mysql> create table DemoTable1997 ( DueDate date );

mysql> insert into DemoTable1997 values('2018-10-11');
mysql> insert into DemoTable1997 values('2019-12-21');
mysql> insert into DemoTable1997 values('2017-01-31');

ข้อมูลในตาราง

+------------+
| DueDate    |
+------------+
| 2018-10-11 |
| 2019-12-21 |
| 2017-01-31 |
+------------+

Query แปลงเป็น UNIX Timestamp

mysql> select unix_timestamp(cast(DueDate as date)) as Result from DemoTable1997;

ผลลัพธ์

+------------+
| Result     |
+------------+
| 1539196200 |
| 1576866600 |
| 1485801000 |
+------------+

อธิบาย

• UNIX_TIMESTAMP() แปลง DATE/DATETIME/TIMESTAMP เป็นวินาทีนับจาก Epoch (1970-01-01 00:00:00 UTC)
• CAST(DueDate AS DATE) รับประกันว่าเป็น TYPE DATE (แม้คอลัมน์เป็น DATE แล้วก็ไม่เสียหาย)
• ผลลัพธ์เป็น BIGINT (วินาที)

ฟังก์ชันเกี่ยวข้อง

ตัวอย่างกลับกัน (Unix Time → Date)

SELECT FROM_UNIXTIME(1576866600);        -- 2019-12-21 07:30:00 (ขึ้นกับ Timezone)
SELECT FROM_UNIXTIME(1576866600, '%Y-%m-%d'); -- 2019-12-21

Timezone Consideration

• UNIX_TIMESTAMP() ใช้ Timezone ของ Session (@@session.time_zone)
• หากเก็บ UTC ให้ตั้ง SET time_zone = '+00:00' ก่อน Query
• MySQL 8.0+ รองรับ AT TIME ZONE (บางเวอร์ชัน)

Year 2038 Problem

Unix Timestamp 32-bit จะล้นในปี 2038 (Signed 32-bit Max = 2,147,483,647 = 2038-01-19) MySQL ใช้ 64-bit สำหรับ UNIX_TIMESTAMP() จึงไม่มีปัญหานี้