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, '我的第一篇博客文章', '你好，世界！');

在这个示例中，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;

这个查询将日期格式化为"星期二，六月二十日，2023年"，并将时间格式化为"02:30 PM"。就像给我们的日期穿上盛装！

以下是一些常用的格式指定符：

指定符	描述	示例
%Y	4位年份	2023
%y	2位年份	23
%M	月份名称	一月
%m	月份（01-12）	01
%d	月份中的日（01-31）	01
%W	星期名称	星期一
%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 '周末登录'
ELSE '工作日登录'
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