SQL 日期范围深入介绍

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

在数据库查询中，日期无处不在。几乎不可能避免使用它们，而且您为什么要避免呢？因为它们为表格带来了如此多的实用性。（此处没有双关语。）更具体地说，如果您需要在给定的时间范围内查找数据，那么 SQL 日期范围正是您所需要的。

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

我们将首先介绍一些先决条件，以确保我们步调一致并准备好开始。之后，我们将直接深入探讨紧迫的问题：什么是 SQL 日期范围以及它为什么有用。然后，我们将稍微转移一下注意力，概述 SQL 中使用的主要日期和时间数据类型。

完成这些之后，我们将进入本文的核心部分，在其中我们将通过回答有关该主题的常见问题，教您如何在实践中使用日期范围。

到最后，您不仅将对 SQL 日期范围有扎实的了解，而且还将知道如何在实践中使用它来改进您的查询。让我们深入了解一下！

先决条件

为了充分利用这篇文章，您需要以下内容

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

您是否拥有所需的一切？那么让我们开始吧。

什么是 SQL 日期范围？为什么您应该关注？

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

许多业务流程中的常见场景是需要获取属于特定时间范围的数据。您可能需要获取给定月份内雇用的员工的数据。也许销售副总裁需要第二季度的收入数据。您明白了吧。

借助日期范围，我们可以查询给定时期的数据。对于下一步，让我们简要概述与时间和数据相关的数据类型。

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#，但他在使用多种语言和平台方面拥有丰富的经验。他的主要兴趣包括自动化测试、版本控制和代码质量。