SQL 日期范围深入介绍

作者：社区 / 开发者
2023年11月15日

在数据库查询中，日期无处不在。几乎无法避免，你为什么要避免呢？因为它们带来了如此多的实用性。（非有意为之。）更具体地说，如果你需要查找特定时间范围内的数据，那么 SQL 日期范围就是你所需要的。

在这篇文章中，我们将带你进行一次 SQL 日期范围的旅行。你将了解“SQL 日期范围”的含义，它对数据库查询的价值以及如何利用它带来好处。

我们首先介绍一些先决条件，确保我们处于同一页面上，准备开始。然后，我们将深入探讨紧迫的问题：SQL 日期范围是什么以及为什么它有用。然后，我们将简要回顾 SQL 中使用的主要日期和时间数据类型。

处理完这些之后，我们将进入文章的核心部分，我们将通过回答有关该主题的常见问题，向您展示如何以实用方式使用日期范围。

到那时，你不仅将牢固地理解 SQL 日期范围，还将知道如何在实践中使用它来提高你的查询效果。让我们深入研究吧！

先决条件

为了最大限度地发挥本文的效用，你需要以下内容

• 对数据库和 SQL 语言的基本了解
• 本地安装的 PostgreSQL 实例——完成此操作的最简单方法是使用官方 Docker 镜像
• 能够连接到您的 PostgreSQL 实例的数据库客户端——例如，pgAdmin

你准备好了吗？那么，让我们开始吧。

什么是 SQL 日期范围？为什么你应该关心？

我们所说的“SQL 日期范围”是什么意思？正如其名所示：SQL 语言中日期的范围。

在许多业务流程中，一个常见的场景是需要获取属于特定时间范围的数据。你可能需要获取在指定月份被雇佣的员工数据。也许销售副总裁需要 Q2 的收入数据。你明白了。

借助日期范围，我们可以查询特定时间段内的数据。接下来，让我们简要概述与时间和数据相关的数据类型。

SQL 中的日期和时间类型：概述

由于我们将在示例中使用 PostgreSQL 作为 RDBMS，让我们回顾一下它提供的用于处理日期/时间问题的数据类型。

• TIME: 此类型用于存储时间。
• DATE: 如果你只需要存储日期，请使用此类型。
• TIMESTAMP: 此类型存储日期和时间。
• TIMESTAMP WITH TIME ZONE: 与 TIMESTAMP 相同，但还存储时区。
• DATERANGE: 如其名所示，此类型存储日期范围。

正如你所见，在 PostgreSQL 中有一个专门用于存储和查询范围的日期范围数据类型，这对于存储和查询范围来说非常方便。

SQL 日期范围：问答指南

在讨论了SQL中日期范围的“是什么”和“为什么”之后，现在最后让我们来探讨“如何做”。本节将采用问答格式：我们将列出与日期范围相关的常见问题，并以实用的方式进行解答。

如何在SQL中选择日期范围？

我们如何选择落在特定时间段内的值？让我们首先创建一个表

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created DATE
);

然后，让我们插入一些数据

INSERT INTO users (username, email, created)
VALUES
    ('JohnDoe', '[email protected]', '2021-06-15'),
    ('JaneSmith', '[email protected]', '2022-09-20'),
    ('MichaelJohnson', '[email protected]', '2022-03-10'),
    ('EmilyWilliams', '[email protected]', '2023-01-05'),
    ('DavidBrown', '[email protected]', '2023-07-18'),
    ('JenniferMiller', '[email protected]', '2019-02-28'),
    ('ChristopherDavis', '[email protected]', '2019-08-12'),
    ('JessicaWilson', '[email protected]', '2021-11-03'),
    ('DanielMoore', '[email protected]', '2022-04-25'),
    ('LindaTaylor', '[email protected]', '2019-05-17');

在我们的第一个示例中，我们想要选择在2022年第一季度创建的用户。为此，我们可以使用BETWEEN关键字

SELECT * FROM users 
WHERE created BETWEEN '2022-01-01' AND '2022-06-30';

请注意，BETWEEN是包含两端的，这意味着创建日期为1月1日或6月30日的用户将被包括在结果中。

如何在SQL中选择日期范围？版本2

现在让我们看看一个更高级的例子。考虑以下表，它存储了多个日活动的数据

CREATE TABLE Events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    start_date DATE,
    end_date DATE
);

让我们向其中插入几行

INSERT INTO Events (name, start_date, end_date)
VALUES
    ('Conference A', '2023-09-15', '2023-09-18'),
    ('Workshop B', '2023-10-05', '2023-10-07'),
    ('Seminar C', '2023-08-20', '2023-08-22'),
    ('Exhibition D', '2023-11-10', '2023-11-12'),
    ('Training E', '2023-09-25', '2023-09-28'),('Workshop F', '2023-10-16', '2023-10-19'),('Conference G', '2023-09-16', '2023-12-23');

现在，假设我们想要检索在特定时间段内举行的所有活动——比如，从10月15日到11月15日。我们该如何做呢？

首先，考虑以下关于活动在所需时间段内发生的情况

• 活动的开始和结束日期在所需时间段内
• 其中一个日期在范围内，但另一个不在
• 所需时间段在活动的开始和结束日期内

为了获取所有想要的活动，本质上有两种方法

• 列出所有可能的情况
• 排除无效的情况

第二种方法是其中最容易的一种。我们只需确保排除开始日期在所需时间段结束日期之后或结束日期在所需时间段开始日期之前的活动

SELECT * FROM Events 
WHERE NOT (start_date > '2023-11-15' OR end_date < '2023-10-15')

我们还可以反转上面的查询，使其更易于阅读

SELECT *
FROM Events
WHERE start_date <= '2023-11-15' AND end_date >= '2023-10-15';

如何在SQL中选择日期范围？版本3

现在让我们回到之前的例子，但有一些变化。首先，让我们创建一个新版本的Events表，但这次使用DATERANGE类型而不是两个列

CREATE TABLE EventsWithRange (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    period DATERANGE
);

然后，让我们插入与之前相同的行

INSERT INTO EventsWithRange (name, period)
VALUES
    ('Conference A', '[2023-09-15, 2023-09-18]'),
    ('Workshop B', '[2023-10-05, 2023-10-07]'),
    ('Seminar C', '[2023-08-20, 2023-08-22]'),
    ('Exhibition D', '[2023-11-10, 2023-11-12]'),
    ('Training E', '[2023-09-25, 2023-09-28]'),
    ('Workshop F', '[2023-10-16, 2023-10-19]'),
    ('Conference G', '[2023-09-16, 2023-12-23]');

我们如何编写与之前相同的查询？实际上，现在要简单得多

SELECT *
FROM EventsWithRange
WHERE period && '[2023-10-15, 2023-11-15]';

在上面的查询中，我们使用&&运算符来检查指定的范围（用‘[2023-10-15, 2023-11-15]’表示）是否与event_period列重叠。是的，如果你不习惯这个语法，它可能不是那么直观，但它确实允许编写更简单的查询。

如何在SQL中比较日期范围？

在处理类型为DATERANGE的列时，我们可以像平常一样使用比较运算符。例如，让我们查询时间段正好匹配所需范围的事件

SELECT name
FROM EventsWithRange
WHERE period = '[2023-09-15, 2023-09-18]';

让我们获取时间段比指定范围大的活动

SELECT *
FROM EventsWithRange
WHERE period > '[2023-09-15, 2023-09-16]';

总结

在编写SQL查询时处理日期是生活不可避免的一部分。SQL日期范围是这个方面，也是一个棘手的部分——尽管日期范围无处不在，但在编写针对日期范围的查询时可能会很容易混淆，从而导致错误。

幸运的是，PostgreSQL可以通过其DATERANGE类型帮助我们。这种类型大大简化了SQL日期范围的使用，从而产生了更简单、更不容易出错的查询。

其他SQL资源

• 使用SQL填充时间序列数据
• 使用SQL格式化日期
• 如何使用SQL获取当前日期和时间
• SQL DATEDIFF函数用于时间间隔
• SQL时间戳指南

本文由Carlos Schults撰写。Carlos是一位咨询顾问和软件工程师，拥有桌面、Web和移动开发的经验。尽管他的主要语言是C#，但他对多种语言和平台都有经验。他的主要兴趣包括自动化测试、版本控制和代码质量。