SQL - 日期與時間：初学者的全面指南

你好，未來的數據庫大師！今天，我們將深入探索 SQL 裡日期和時間的迷人世界。作為你友善的鄰居計算機老師，我將帶領你踏上這次旅程，並提供大量的範例和解釋。所以，抓緊你的虛擬時間機器，讓我們開始吧！

SQL 中的日期與時間數據類型

在我們開始操作日期和時間之前，我們需要了解 SQL 如何存儲這些信息。將這些數據類型想像成為時間數據設計的特殊容器。

DATE

DATE 數據類型以 YYYY-MM-DD 的格式存儲日期。

CREATE TABLE events (
event_id INT PRIMARY KEY,
event_name VARCHAR(100),
event_date DATE
);

INSERT INTO events (event_id, event_name, event_date)
VALUES (1, '公司野餐', '2023-07-15');

在這個範例中，我們創建了一個 events 表，並添加了一個於 2023 年 7 月 15 日舉行的公司野餐。DATE 數據類型確保我們只存儲日期，而不包含任何時間信息。

TIME

TIME 數據類型以 HH:MI:SS 的格式存儲時間。

CREATE TABLE class_schedule (
class_id INT PRIMARY KEY,
class_name VARCHAR(100),
start_time TIME
);

INSERT INTO class_schedule (class_id, class_name, start_time)
VALUES (1, 'SQL 入門', '09:30:00');

在這裡，我們創建了一個課程時間表，並添加了一個於上午 9:30 開始的 SQL 課程。TIME 數據類型讓我們能夠精確地存儲這個時間。

DATETIME

DATETIME 數據類型結合了日期和時間，通常以 YYYY-MM-DD HH:MI:SS 的格式。

CREATE TABLE appointments (
appointment_id INT PRIMARY KEY,
patient_name VARCHAR(100),
appointment_datetime DATETIME
);

INSERT INTO appointments (appointment_id, patient_name, appointment_datetime)
VALUES (1, 'John Doe', '2023-06-20 14:30:00');

在這個範例中，我們為 John Doe 預約了 2023 年 6 月 20 日下午 2:30 的醫生問診。DATETIME 數據類型讓我們能夠在單個列中存儲日期和時間。

TIMESTAMP

TIMESTAMP 數據類型與 DATETIME 相似，但通常用於跟踪記錄最後一次被修改的時間。

CREATE TABLE blog_posts (
post_id INT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO blog_posts (post_id, title, content)
VALUES (1, '我的第一篇部落格文章', 'Hello, World!');

在這個範例中，last_updated 列將在記錄被插入或更新時自動設置為當前日期和時間。

SQL 中的日期與時間函數

既然我們知道了如何存儲日期和時間，讓我們來探索一些 SQL 提供的用於操作這些數據的神奇函數。我喜歡將這些函數視為我們 SQL 工具包中的時間旅行工具！

當前日期和時間

讓我們從基礎開始 - 獲取當前的日期和時間。

SELECT CURRENT_DATE() AS today,
CURRENT_TIME() AS current_time,
NOW() AS current_datetime;

這個查詢會給你當前的日期、時間和日期時間。就像問 SQL，“嘿，現在幾點了？”很方便，對吧？

提取日期的部分

有時我們只需要日期的特定部分。SQL 也為此提供了函數！

SELECT YEAR('2023-06-20') AS year,
MONTH('2023-06-20') AS month,
DAY('2023-06-20') AS day,
WEEKDAY('2023-06-20') AS weekday;

這個查詢從給定的日期中提取年、月、日和星期幾。就像剖析日期以獲取我們需要的信息。

日期算術

SQL 允許我們對日期進行算術操作。這特別有用於計算持續時間或找到未來/過去的日期。

SELECT DATE_ADD('2023-06-20', INTERVAL 7 DAY) AS week_later,
DATE_SUB('2023-06-20', INTERVAL 1 MONTH) AS month_ago,
DATEDIFF('2023-12-31', '2023-06-20') AS days_until_new_year;

在這個範例中，我們在日期上添加了 7 天，從日期中減去 1 個月，並計算兩個日期之間的天數。就像手上有了一台時間機器！

格式化日期

有時我們需要以特定的格式呈現日期。DATE_FORMAT 函數來幫助我們。

SELECT DATE_FORMAT('2023-06-20', '%W, %M %d, %Y') AS formatted_date,
DATE_FORMAT('2023-06-20 14:30:00', '%h:%i %p') AS formatted_time;

這個查詢將日期格式化為 "Tuesday, June 20, 2023" 和時間為 "02:30 PM"。就像給我們的日期穿上了盛裝！

這裡是一張常用格式指定符的表格：

指定符	描述	範例
%Y	4 位年	2023
%y	2 位年	23
%M	月份名稱	January
%m	月份 (01-12)	01
%d	月份中的日 (01-31)	01
%W	星期名稱	Monday
%w	星期中的日 (0-6，0 為星期日)	1
%H	小時 (00-23)	13
%h	小時 (01-12)	01
%i	分鐘 (00-59)	30
%s	秒 (00-59)	45
%p	AM 或 PM	PM

結合所有概念

讓我們用一個更複雜的範例來總結，這個範例結合了多個概念：

CREATE TABLE user_logins (
login_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
login_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user_logins (user_id) VALUES (1), (2), (3);

SELECT
user_id,
login_time,
DATE_FORMAT(login_time, '%W, %M %d, %Y at %h:%i %p') AS formatted_login_time,
CASE
WHEN WEEKDAY(login_time) IN (5, 6) THEN 'Weekend Login'
ELSE 'Weekday Login'
END AS login_type,
TIMESTAMPDIFF(HOUR, login_time, NOW()) AS hours_since_login
FROM user_logins
WHERE login_time > DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK);

這個範例創建了一個 user_logins 表，插入了一些數據，然後使用各種日期和時間操作進行查詢。我們格式化登錄時間，判斷是週末還是工作日登錄，並計算自登錄以來過了多少小時。

就这样，各位！我們用 SQL 旅行了時間，學習了日期和時間的數據類型和函數。記住，熟能生巧，所以不要害怕嘗試這些概念。在你還沒有意識到之前，你就會變得能夠在數據庫中操縱時間！

Credits: Image by storyset