按日期筛选 SQL

学习 SQL 日期筛选：如何按日期筛选数据，从简单的精确匹配到复杂的模式，如工作日和相对时期。

使用 Metabase 学习 SQL
免费下载 Metabase，或免费试用 Metabase Cloud

人们喜欢知道事情发生的时间。本指南将向您展示如何按日期筛选数据，从简单的精确匹配到复杂的模式，如工作日和滚动周期。

我们将涵盖的内容

日期和时间戳的区别
精确日期匹配
某个日期之前或之后
使用 BETWEEN 进行日期范围筛选
日期的一部分（周、月等）
相对日期
星期几
小时范围
会计期间
循环日期
工作日
过去 X 天
两个日期之间的差值
有间隔的日期范围
缺失日期
日期变量

日期和时间戳以及字符串形式存储的日期之间的区别

在 SQL 中，DATE 和 TIMESTAMP 是不同的数据类型

SELECT
  DATE '2025-05-04' AS this_is_a_date,
  TIMESTAMP '2025-05-04 14:30:00' AS this_is_a_timestamp
FROM
  orders
LIMIT
  1;

一个 DATE 值

仅存储日历日期（年、月、日）
没有时间分量
典型格式为“YYYY-MM-DD”
占用（略微）较少的存储空间

一个 TIMESTAMP 值

存储日期和时间（年、月、日、小时、分钟、秒，通常还有小数秒）
典型格式为 YYYY-MM-DD HH:MM:SS.SSS（小数毫秒 .SSS 是可选的）
可能包含时区信息（TIMESTAMP WITH TIME ZONE 或 TIMESTAMPTZ）

日期也可以（很少）存储为字符串（即文本）。

大多数工具（包括 Metabase）在数据参考部分提供列的类型信息。您通常也可以查询数据库的 INFORMATION_SCHEMA。以下是获取示例数据库订单表中列数据类型的方法：

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'ORDERS';

| TABLE_NAME | COLUMN_NAME | DATA_TYPE        |
| ---------- | ----------- | ---------------- |
| ORDERS     | ID          | BIGINT           |
| ORDERS     | USER_ID     | INTEGER          |
| ORDERS     | PRODUCT_ID  | INTEGER          |
| ORDERS     | SUBTOTAL    | DOUBLE PRECISION |
| ORDERS     | TAX         | DOUBLE PRECISION |
| ORDERS     | TOTAL       | DOUBLE PRECISION |
| ORDERS     | DISCOUNT    | DOUBLE PRECISION |
| ORDERS     | CREATED_AT  | TIMESTAMP        |
| ORDERS     | QUANTITY    | INTEGER          |

这里日期列 CREATED_AT 是一个 TIMESTAMP。

实际上，除非您处理精确时间很重要的日期，否则在查询表时会希望转换为 DATE 类型，因为您通常会按天（或周、月、季度或年）筛选和分组结果。

将时间戳转换为日期

您可以使用 CAST 将时间戳转换为日期

SELECT
  id,
  CAST(created_at AS DATE) AS order_date
FROM
  orders;

按单个日期筛选行的 SQL

要搜索精确的日期匹配，请使用 WHERE 子句和 = 运算符。以下是获取 2025 年 5 月 4 日所有订单的查询。

SELECT
  id,
  created_at
FROM
  orders
WHERE
  created_at >= DATE '2025-05-04'
  AND created_at < DATE '2025-05-05';

为什么不只用 WHERE created_at = '2025-05-04'？有两个原因：

created_at 是一个包含时间戳的字段。因此，尽管 WHERE created_at = '2025-05-04' 是一个有效的子句，但该筛选器只会返回在 2025-05-04T00:00:00（2025 年 5 月 4 日午夜）下达的订单。使用 AND 我们可以要求数据库返回 5 月 4 日午夜到 5 月 5 日午夜（不包括）之间下达的所有订单。
使用范围保持查询可索引 (sargable)，这是一个行话，意思是“允许查询处理器利用列上的任何索引的查询”。Sargable 是 Search ARGument ABLE 的缩写。（我们将在另一篇文章中介绍索引。）

或者，您可以将时间戳转换为日期，如下所示：

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Converting the column to a date type to lop off the time
  CAST(created_at AS DATE) = DATE '2025-05-04';

这个查询是有效的，但是由于查询处理器必须对列中的每个值运行 CAST 函数，所以查询处理器无法利用列上的任何索引来加快结果速度（也就是说：查询不是可索引的）。

DATE 关键字不是必需的。大多数数据库会识别 YYYY-MM-DD 为日期，但您也可以明确指出。

在某个日期之前或之后筛选的 SQL

您可以使用比较运算符来查找特定日期之前或之后的日期。这里我们获取的是 2025 年 5 月 4 日之前的订单。

SELECT
  *
FROM
  orders
WHERE
  -- Get orders from before midnight on May 4th, 2025
  -- (midnight is the start of a day)
  created_at < DATE '2025-05-04';

如果您想包含在 2025-05-04 当天下的订单，您可以将日期增加到 2025-05-05，或者使用 INTERVAL 添加一天

SELECT
  *
FROM
  orders
WHERE
  -- Get orders from May 4th, 2025 and before
  created_at < DATE '2025-05-04' + INTERVAL '1' DAY;

SQL 支持所有标准比较运算符，但请记住，这些运算符会根据您使用的是日期还是时间戳而返回不同的结果。

>（之后）
>=（在或之后）
<（之前）
<=（在或之前：如果您正在处理时间戳，则只包括该日期的午夜）。

使用 `BETWEEN` 筛选日期范围的 SQL

要查找某个范围内的日期，请使用 BETWEEN。这里我们筛选的是 2025 年 5 月 1 日午夜到 5 月 15 日午夜之间下的订单。

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders from midnight May 1 through midnight May 15, 2025
  created_at BETWEEN DATE '2025-05-01' AND  DATE '2025-05-15';

尽管 BETWEEN 包含开始日期和结束日期，但此查询不会返回 2025 年 5 月 15 日下的所有订单。这是因为 created_at 列包含时间戳，而不是日期，因此查询将只包含截至 5 月 15 日午夜下的订单。如果您想包含 15 日其他时间下的订单，则需要将范围扩大到 16 日。

或者，您可以组合比较筛选器以返回一个范围。以下是上面使用 BETWEEN 的查询的转换：我们再次筛选 2025 年 5 月 1 日午夜到 5 月 15 日午夜之间下的订单。

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Mimics BETWEEN: gets orders from
  -- midnight May 1 through midnight May 15, 2025
  -- If you wanted to include all orders on the 15th,
  -- you'd need to write `< '2025-05-16'`
  created_at >= DATE '2025-05-01'
  AND created_at <= DATE '2025-05-15';

按日期的一部分（按周或月等）筛选的 SQL

您可以使用 EXTRACT 根据日期的特定部分（如年、月或日）进行筛选。假设您想获取所有在 5 月下的订单，无论年份如何。您可以从日期列中提取 MONTH FROM，如下所示：

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get all orders created in May
  EXTRACT(MONTH FROM created_at) = 5;

您还可以提取：

YEAR（年）
MONTH（月）
DAY（日）
HOUR（小时）
MINUTE（分钟）
SECOND（秒）
DOW (星期几)
DOY (一年中的第几天)

按相对日期筛选的 SQL

要按相对日期（如过去 X 天）进行筛选，您可以使用 CURRENT_DATE 和 INTERVAL。以下是获取过去七天（包括今天）订单的查询

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Since we're working with timestamps, CURRENT_DATE will return the current date at midnight
  -- So we'll need to add a day to include orders placed on the current date.
  created_at <= CURRENT_DATE + INTERVAL '1' DAY
  -- Get orders from the last 7 days
  AND created_at >= CURRENT_DATE - INTERVAL '7' DAY;

相对日期函数因数据库而异，因此您需要查找您的数据库使用哪些函数。常见的相对日期函数名称包括：

CURRENT_DATE: 今天日期
CURRENT_TIMESTAMP: 当前日期和时间
NOW(): 当前日期和时间
INTERVAL: 指定时间段

INTERVAL 关键字接受各种时间单位。以下是大多数数据库支持的常用单位：

YEAR / YEARS（年）
MONTH / MONTHS（月）
WEEK / WEEKS（周）
DAY / DAYS（天）
HOUR / HOURS（小时）
MINUTE / MINUTES（分钟）
SECOND / SECONDS（秒）
MILLISECOND / MILLISECONDS（毫秒）

请注意，不同的数据库可能支持不同的单位或具有略微不同的语法。务必查阅数据库文档，以获取支持的间隔单位的完整列表。

按星期几筛选的 SQL

要查找在特定星期几下的订单，您可以 EXTRACT 星期几 (DOW)。以下是筛选所有在周一或周五下的订单的查询。

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders placed on Mondays (2) and Fridays (6)
  EXTRACT(DOW FROM created_at) IN (2, 6);

不方便的是，不同的数据库对星期几的编号方式不同，因此请检查您的查询结果以确保数字返回的是正确的星期几。

按小时范围筛选的 SQL

要查找在某个小时范围内（无论日期如何）下的订单，我们可以 EXTRACT 小时并使用 BETWEEN 在一天中的两个小时之间进行筛选。这里我们筛选每天 09:00 到 17:59 之间下的订单。

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders placed between 9 AM and 5 PM
  EXTRACT(HOUR FROM created_at) BETWEEN 9 AND 17;

请注意，小时包含整个小时。如果您想截断下午 5 点 (17:00) 之后下的订单，则需要使用 BETWEEN 9 AND 16。

按会计期间筛选的 SQL

要查找特定会计期间（如季度或财政年度）的订单，您可以 EXTRACT 季度 (QUARTER) 和年份 (YEAR)。以下是获取 2025 年第二季度所有订单的查询：

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders from Q2 2025 (April through June)
  EXTRACT(QUARTER FROM created_at) = 2
  AND
  EXTRACT(YEAR FROM created_at) = 2025;

按循环日期筛选的 SQL

要查找每月在同一天发生的订单，请使用 EXTRACT 和 = 运算符。以下是查找每月 15 日所有订单的查询：

SELECT
  *
FROM
  orders
WHERE
  -- Get orders placed on the 15th of any month
  EXTRACT(DAY FROM created_at) = 15;

显然，有些月份的天数比其他月份少。如果您要查找每月的 31 日，您将错过 2 月、4 月、6 月、9 月和 11 月。要获取每月最后一天的所有订单，您可以使用 EXTRACT 和 INTERVAL

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Orders placed on the last day of the each month
  EXTRACT(DAY FROM (created_at + INTERVAL '1' DAY)) = 1;

EXTRACT(DAY FROM (created_at + INTERVAL '1' DAY)) = 1 检查将 created_at 的值加一天后，该月份的日期是否为 1。进一步分解：

created_at + INTERVAL '1' DAY 是日期加一天。
EXTRACT (DAY FROM ...) 获取日期的值。
我们将其与值 1 进行比较（即，如果我们将日期加一，它是否是下个月的第一天？）

如果将 created_at 的值加一天后等于 1（即该月的第一天），则意味着 created_at 的值一定是上个月的最后一天。

按工作日筛选的 SQL

要查找在工作日（周一至周五，不包括周末）下的订单，您可以 EXTRACT 星期几并使用 BETWEEN 进行范围筛选。以下是筛选周一至周五下的订单的查询。

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- If your Day of Week (DOW)starts Sunday as 1, then it's BETWEEN 2 AND 6 (Mon-Fri)
  -- If your DOW starts Monday as 1, then it's BETWEEN 1 AND 5 (Mon-Fri)
 EXTRACT(DOW FROM created_at) BETWEEN 2 AND 6;

您还可以使用 NOT IN 指定要排除的一组假期

SELECT
  id,
  created_at
FROM
  orders
WHERE
  created_at > '2024-12-31'
  AND created_at < '2025-02-01'
  AND EXTRACT(DOW FROM created_at) BETWEEN 2 AND 6
  -- Exclude some American holidays in 2025.
  -- Because created_at is a timestamp, we'll need to cast it as a date.
  AND CAST(created_at AS DATE) NOT IN (
    DATE '2025-01-01', -- New Year's Day
    DATE '2025-07-04', -- Independence Day
    DATE '2025-09-01', -- Labor Day
    DATE '2025-09-07', -- Yet Another Syntax Error Day
    DATE '2025-11-27' -- Thanksgiving Day
    -- and whatever other holidays and dates you want to exclude
  );

如果我们没有将 created_at 强制转换为日期类型，我们只会排除在这些日期的午夜精确时间下的订单。

筛选最近 X 天的 SQL

要根据订单的“年龄”进行筛选，您可以使用 BETWEEN、CURRENT_DATE 和 INTERVAL。以下是筛选 30 到 60 天前下的订单的查询。

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders that are between 30 and 60 days old
  created_at BETWEEN CURRENT_DATE - INTERVAL '60' DAY AND CURRENT_DATE - INTERVAL '30' DAY;

按两个日期之间的差值筛选的 SQL

假设我们想查看所有在创建后三天内取消的账户。我们需要：

筛选已取消的账户
从两个日期列中提取日期以计算差值
筛选差值小于或等于 3 的记录。

SELECT
  id,
  created_at,
  canceled_at,
  -- Get the day number for the date
  -- Calculate the difference in dates
  EXTRACT(DAY FROM canceled_at - created_at) AS days_active
FROM
  accounts
WHERE
  -- Filter for canceled accounts.
  canceled_at IS NOT NULL
 AND
  -- Calculate the day again
  EXTRACT(DAY FROM canceled_at - created_at) <= 3;

您不能在 WHERE 子句中使用 days_active 别名，原因在于 SQL 中的操作顺序。在这种情况下，WHERE 子句在 SELECT 子句之前评估，因此它不知道 days_active 别名。当然，数据库引擎有所不同，因此在某些情况下您可能可以引用别名。如果您想避免两次编写 EXTRACT，您可以使用公共表表达式

WITH account_activity AS (
  SELECT
    id,
    created_at,
    canceled_at,
    EXTRACT(DAY FROM canceled_at - created_at) AS days_active
  FROM
    accounts
  WHERE
    canceled_at IS NOT NULL
)

SELECT
  *
FROM
  account_activity
WHERE
  days_active <= 3;

虽然不是标准 SQL，但许多数据库都支持 DATEDIFF 函数，该函数允许您根据以特定单位测量的日期之间的差异来筛选行。您可以使用 DATEDIFF 来查找两个日期之间经过特定时间量的记录。

SELECT
  id,
  created_at,
  canceled_at,
  DATEDIFF (DAY, created_at, canceled_at) as days_active
FROM
  accounts
WHERE
  -- Filter for canceled accounts
  canceled_at IS NOT NULL
  -- And filter for accounts that canceled within three days
  AND DATEDIFF (DAY, created_at, canceled_at) <= 3;

这里 DATEDIFF 接受一个单位（DAY），以及开始和结束日期，并计算两个日期之间单位的数量（在本例中为天数）。一些数据库对 DATEDIFF 函数的实现方式不同，因此请查阅它们的文档以了解其函数签名。

筛选有间隔的日期范围的 SQL

要查找特定日期范围内但排除某些时期的订单，您可以结合多个筛选器与 AND。这里我们获取 2025 年 5 月的日期，但排除周末，以及在 5 月 25 日和 26 日举行的假设大型促销活动期间创建的任何订单。

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders from May 2025
  created_at >= '2025-05-01'
  AND created_at < '2025-06-01'
  -- But exclude weekends: not Sunday (1) or Saturday (7)
  -- Though note that the numbering of days of the week isn't consistent across databases
  AND EXTRACT(DOW FROM created_at) NOT IN (1, 7)
  -- And exclude specific dates
  -- Which we'll need to cast, as created_at contains timestamps
  AND CAST(created_at AS DATE) NOT IN (
    DATE '2025-05-25',
    DATE '2025-05-26'
    );

筛选或排除具有缺失日期的行的 SQL

要查找日期列为空（null）的行：

SELECT
  *
FROM
  orders
WHERE
  created_at IS NULL;

要查找实际有值的行（不为空）：

SELECT
  *
FROM
  orders
WHERE
  created_at IS NOT NULL;

按日期变量筛选的 SQL

请查看SQL 参数。

商业智能

嵌入式分析

文档

学习

课程

Metabase 基础知识

提升您的数据技能

备忘单

按日期筛选 SQL

学习 SQL 日期筛选：如何按日期筛选数据，从简单的精确匹配到复杂的模式，如工作日和相对时期。

我们将涵盖的内容

日期和时间戳以及字符串形式存储的日期之间的区别

将时间戳转换为日期

按单个日期筛选行的 SQL

在某个日期之前或之后筛选的 SQL

使用 `BETWEEN` 筛选日期范围的 SQL

按日期的一部分（按周或月等）筛选的 SQL

按相对日期筛选的 SQL

按星期几筛选的 SQL

按小时范围筛选的 SQL

按会计期间筛选的 SQL

按循环日期筛选的 SQL

按工作日筛选的 SQL

筛选最近 X 天的 SQL

按两个日期之间的差值筛选的 SQL

筛选有间隔的日期范围的 SQL

筛选或排除具有缺失日期的行的 SQL

按日期变量筛选的 SQL

这有帮助吗？

感谢您的反馈！

商业智能

嵌入式分析

文档

学习

课程

Metabase 基础知识

提升您的数据技能

备忘单

我们将涵盖的内容

日期和时间戳以及字符串形式存储的日期之间的区别

将时间戳转换为日期

按单个日期筛选行的 SQL

在某个日期之前或之后筛选的 SQL

使用 BETWEEN 筛选日期范围的 SQL

按日期的一部分（按周或月等）筛选的 SQL

按相对日期筛选的 SQL

按星期几筛选的 SQL

按小时范围筛选的 SQL

按会计期间筛选的 SQL

按循环日期筛选的 SQL

按工作日筛选的 SQL

筛选最近 X 天的 SQL

按两个日期之间的差值筛选的 SQL

筛选有间隔的日期范围的 SQL

筛选或排除具有缺失日期的行的 SQL

按日期变量筛选的 SQL

这有帮助吗？

感谢您的反馈！

使用 `BETWEEN` 筛选日期范围的 SQL