基于Typescript+Prisma+PostgreSQL的时间范围管理方案咨询

一、直接用PostgreSQL实现（高效且精准） #

数据库层处理时间范围逻辑性能最优，还能通过自定义函数提升可读性，下面是核心场景的实现：

1. 查询特定日期活跃的实体 #

针对activeEndDate可能为null（表示永久有效）的情况，直接写SQL或通过Prisma映射：

-- PostgreSQL原生查询
SELECT * FROM your_entity_table
WHERE activeStartDate <= '2024-05-20'
AND (activeEndDate >= '2024-05-20' OR activeEndDate IS NULL);

对应Prisma写法：

const targetDate = new Date('2024-05-20');
const activeEntities = await prisma.yourEntity.findMany({
  where: {
    activeStartDate: { lte: targetDate },
    OR: [
      { activeEndDate: { gte: targetDate } },
      { activeEndDate: null }
    ]
  }
});

2. 匹配时间范围重叠的实体 #

两个时间范围[aStart, aEnd]和[bStart, bEnd]重叠的核心判断逻辑是aStart < bEnd AND bStart < aEnd，处理null时用COALESCE转为极大值（代表永久有效）：

-- PostgreSQL原生查询（匹配与指定实体重叠的其他实体）
SELECT t2.* FROM your_entity_table t1
JOIN your_entity_table t2 ON t1.id != t2.id
AND t1.activeStartDate < COALESCE(t2.activeEndDate, '9999-12-31')
AND t2.activeStartDate < COALESCE(t1.activeEndDate, '9999-12-31');

3. 自定义SQL函数提升可读性 #

可以封装重复逻辑为PostgreSQL函数，后续查询直接调用：

-- 判断实体在指定日期是否活跃
CREATE FUNCTION is_active_on(target_date DATE, start_date DATE, end_date DATE)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN start_date <= target_date AND (end_date >= target_date OR end_date IS NULL);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 判断两个时间范围是否重叠
CREATE FUNCTION ranges_overlap(a_start DATE, a_end DATE, b_start DATE, b_end DATE)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN a_start < COALESCE(b_end, '9999-12-31') 
         AND b_start < COALESCE(a_end, '9999-12-31');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

调用示例：

SELECT * FROM your_entity_table WHERE is_active_on('2024-05-20', activeStartDate, activeEndDate);

二、适合的TypeScript库（提升代码可读性） #

如果需要在应用层做复杂时间范围逻辑，以下两个库非常实用：

1. Luxon（推荐） #

专门提供Interval类处理时间范围，内置overlaps、contains等方法，逻辑直观：

import { Interval, DateTime } from 'luxon';

// 生成实体的活跃时间区间
const getActiveInterval = (entity: YourEntity) => {
  const start = DateTime.fromJSDate(entity.activeStartDate);
  const end = entity.activeEndDate 
    ? DateTime.fromJSDate(entity.activeEndDate) 
    : DateTime.fromISO('9999-12-31');
  return Interval.fromDateTimes(start, end);
};

// 判断实体是否在指定日期活跃
const isEntityActive = (entity: YourEntity, targetDate: Date) => {
  const interval = getActiveInterval(entity);
  return interval.contains(DateTime.fromJSDate(targetDate));
};

// 判断两个实体的时间范围是否重叠
const doRangesOverlap = (entityA: YourEntity, entityB: YourEntity) => {
  const intervalA = getActiveInterval(entityA);
  const intervalB = getActiveInterval(entityB);
  return intervalA.overlaps(intervalB);
};

2. date-fns（轻量灵活） #

生态成熟，通过基础函数组合实现时间范围判断：

import { isWithinInterval } from 'date-fns';

// 判断实体是否在指定日期活跃
const isEntityActive = (entity: YourEntity, targetDate: Date) => {
  const endDate = entity.activeEndDate ?? new Date('9999-12-31');
  return isWithinInterval(targetDate, { 
    start: entity.activeStartDate, 
    end: endDate 
  });
};

三、Prisma结合最佳实践 #

1. 数据量大时优先用PostgreSQL自定义函数，避免全量拉取数据到应用层处理；
2. 应用层的时间范围逻辑用Luxon/date-fns封装，提升代码可维护性；
3. Prisma支持直接调用自定义SQL函数，可在Prisma Schema中声明后调用：

const activeEntities = await prisma.$queryRaw`
  SELECT * FROM your_entity_table 
  WHERE is_active_on(${targetDate}, activeStartDate, activeEndDate)
`;

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