PostgreSQL查询指定年份区间内活跃考古遗址的SQL方法

你需要筛选的是占用期和公元前27年-公元235年区间存在重叠的所有遗址，而非起止点落在区间内的遗址，核心判断逻辑和你之前尝试的单点判断规则完全不同。

原有写法的明确问题：

• 仅判断start_date落在区间内：会漏掉建立时间早于公元前27年、但在目标时段仍有人居住的遗址
• 仅判断end_date晚于公元235年：会漏掉废弃时间在目标区间内的遗址，也无法排除建立时间晚于公元235年的无效结果

两个时间范围存在重叠的充要条件非常简单，不需要复杂的多分支判断：

• 遗址的建立时间早于目标区间的终点（公元235年1月1日）
• 遗址的废弃时间晚于目标区间的起点（公元前27年1月1日）

对于至今仍有人居住、end_date字段为NULL的遗址，用COALESCE把空值转为PostgreSQL内置的无穷大日期infinity即可兼容，不需要单独写分支条件。

注：上述判断默认时间精度到日，如果你只需要按年份判断、把边界年份全年都算入占用期，可以把边界值调整为对应年份的12月31日，或给条件加上对应的等号即可。PostgreSQL的date类型原生支持公元前日期格式，你使用的'0027-01-01 BC'写法是合法的，不需要额外转换。

SELECT site_id, name, type, start_date, end_date
FROM site_date
WHERE
  start_date < '0235-01-01'::date
  AND COALESCE(end_date, 'infinity'::date) > '0027-01-01 BC'::date;

如果你的库中把至今仍居住的遗址end_date存为固定远期值（比如9999-12-31），可以去掉COALESCE函数，直接写end_date > '0027-01-01 BC'::date即可。

对应你提到的示例遗址，判断结果完全符合预期：

• 建立早于公元前27年、废弃在目标区间内的遗址 → 命中
• 建立、废弃都在目标区间内的遗址 → 命中
• 建立早于公元前27年、废弃晚于公元235年（含至今居住）的遗址 → 命中
• 建立在目标区间内、废弃晚于公元235年（含至今居住）的遗址 → 命中
• 废弃时间早于公元前27年的遗址（如遗址E） → 不满足第二个判断条件，自动排除
• 建立时间晚于公元235年的遗址 → 不满足第一个判断条件，自动排除

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