如何编写SQL查询筛选指定日期范围及每日6-12点的交易记录？

原查询的问题在于直接用BETWEEN指定了单个日期的时间区间，当扩展到跨多月的范围时，会把中间日期的所有时间都包含进去，而不是只保留每日6:00-12:00的记录。要解决这个问题，需要拆分筛选条件：先限定日期范围，再筛选每日的时间区间。

通用思路 #

将PROCESS_DATE_TIME拆分为日期和时间两部分，分别设置过滤条件：

1. 日期部分落在2023-07-01至2023-10-31之间
2. 时间部分落在06:00:00至12:00:00之间

以下是几种主流数据库的具体实现：

MySQL/MariaDB

使用DATE()提取日期、TIME()提取时间：

SELECT TRANSACTION_PROCESS_TIME, TIMESTAMP(PROCESS_DATE_TIME) AS PROCESS_DATE_TIME
FROM TRANSACTION_TABLE
WHERE 
  DATE(PROCESS_DATE_TIME) BETWEEN '2023-07-01' AND '2023-10-31'
  AND TIME(PROCESS_DATE_TIME) BETWEEN '06:00:00' AND '12:00:00'
ORDER BY PROCESS_DATE_TIME;

PostgreSQL

可通过::DATE转换日期类型，用EXTRACT提取小时或直接用TIME()筛选：

SELECT TRANSACTION_PROCESS_TIME, PROCESS_DATE_TIME::TIMESTAMP AS PROCESS_DATE_TIME
FROM TRANSACTION_TABLE
WHERE 
  PROCESS_DATE_TIME::DATE BETWEEN '2023-07-01' AND '2023-10-31'
  -- 两种时间筛选方式二选一
  AND EXTRACT(HOUR FROM PROCESS_DATE_TIME::TIMESTAMP) BETWEEN 6 AND 11
  -- OR TIME(PROCESS_DATE_TIME) BETWEEN '06:00:00' AND '12:00:00'
ORDER BY PROCESS_DATE_TIME;

BigQuery

利用DATE()和EXTRACT函数拆分时间维度：

SELECT TRANSACTION_PROCESS_TIME, TIMESTAMP(PROCESS_DATE_TIME) AS PROCESS_DATE_TIME
FROM TRANSACTION_TABLE
WHERE 
  DATE(PROCESS_DATE_TIME) BETWEEN DATE('2023-07-01') AND DATE('2023-10-31')
  -- 两种时间筛选方式二选一
  AND EXTRACT(HOUR FROM TIMESTAMP(PROCESS_DATE_TIME)) BETWEEN 6 AND 11
  -- OR TIME(TIMESTAMP(PROCESS_DATE_TIME)) BETWEEN TIME('06:00:00') AND TIME('12:00:00')
ORDER BY PROCESS_DATE_TIME;

Oracle

通过TRUNC()截断日期，TO_CHAR()提取时间字符串：

SELECT TRANSACTION_PROCESS_TIME, TO_TIMESTAMP(PROCESS_DATE_TIME, 'YYYY-MM-DD HH24:MI:SS') AS PROCESS_DATE_TIME
FROM TRANSACTION_TABLE
WHERE 
  TRUNC(TO_TIMESTAMP(PROCESS_DATE_TIME, 'YYYY-MM-DD HH24:MI:SS')) BETWEEN TO_DATE('2023-07-01', 'YYYY-MM-DD') AND TO_DATE('2023-10-31', 'YYYY-MM-DD')
  AND TO_CHAR(TO_TIMESTAMP(PROCESS_DATE_TIME, 'YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS') BETWEEN '06:00:00' AND '12:00:00'
ORDER BY PROCESS_DATE_TIME;

注意事项 #

• 如果需要排除12:00:00整的记录，可将时间条件改为TIME(PROCESS_DATE_TIME) >= '06:00:00' AND TIME(PROCESS_DATE_TIME) < '12:00:00'
• 若PROCESS_DATE_TIME字段有索引，使用函数转换可能会导致索引失效。如果追求性能，可尝试结合范围条件和时间筛选：

  -- 以MySQL为例
  SELECT TRANSACTION_PROCESS_TIME, TIMESTAMP(PROCESS_DATE_TIME) AS PROCESS_DATE_TIME
  FROM TRANSACTION_TABLE
  WHERE 
    PROCESS_DATE_TIME >= '2023-07-01 06:00:00'
    AND PROCESS_DATE_TIME <= '2023-10-31 12:00:00'
    AND (TIME(PROCESS_DATE_TIME) BETWEEN '06:00:00' AND '12:00:00')
  ORDER BY PROCESS_DATE_TIME;
  

内容的提问来源于stack exchange，提问作者fern