价格
登录 开始使用
文档 学习
课程
Metabase 教程
提升您的数据技能
备忘单

按日期进行 SQL 筛选

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

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

我们将涵盖的内容

在深入学习高级 SQL 之前需要快速复习吗？请查看我们的SQL 速查表，了解核心命令和语法。它也非常适合与开始进行数据分析的同事分享。

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

在 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
  -- `>=` and `<` are alligators that eat the bigger number
  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 日午夜（不包括）之间下达的所有订单。
  • 使用范围保持查询可索引，这是一个术语，表示“允许查询处理器利用列上任何索引的查询”。可索引是 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 转换为日期，我们将只排除那些正好在午夜下的订单。

SQL 筛选最近 X 天

要根据订单的“年龄”进行筛选，您可以使用 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 参数。

延伸阅读

这有帮助吗？

感谢您的反馈！
分析师每周技巧
获取可行的见解
关于 AI 和数据的资讯，直接发送到您的收件箱
© . This site is unofficial and not affiliated with Metabase, Inc.