PostgreSQL中存储年/周数据的最优方案及date类型存储可行性咨询

能否直接存入date类型？ #

不行。
PostgreSQL的date类型要求必须存储精确到天的完整日期（包含年、月、日三个维度的有效信息），单独的年+周组合没有明确的日期指向，无法直接存入date类型。

如果强制将年周映射到当周某一天（比如周一、周日）再存入date，会存在歧义：尤其是跨年周场景下，比如ISO标准的2023年第1周可能包含2022年12月的日期，直接存日期很容易导致周数对应的年份归属错误。

年/周数据最优存储方案 #

根据使用场景可以选择以下两种优先级最高的方案：

• 方案1：双smallint字段存储（最通用，推荐）
  分别用两个smallint类型字段存储iso_year和iso_week，该方案优势：
  • 无歧义，排序、过滤、聚合逻辑都非常直观，例如筛选2021年第30周到2022年第10周的数据可直接写条件：WHERE (iso_year = 2021 AND iso_week >=30) OR (iso_year = 2022 AND iso_week <=10)
  • 占用空间极小，两个smallint合计仅占4字节，性能优秀
  • 插入取值时可直接用PostgreSQL内置函数获取正确值：

    -- 从指定日期获取ISO年、周
    SELECT EXTRACT(ISOYEAR FROM CURRENT_DATE) AS iso_year, EXTRACT(WEEK FROM CURRENT_DATE) AS iso_week;
    

• 方案2：单int字段存储（适合需要单字段的场景）
  将年周拼接为YYYYWW格式的整数存储，例如2021年第32周存为202132，该方案优势：
  • 仅需单个字段，展示时可快速转换为2021/32格式，也支持正常排序、过滤
  • 仅占4字节，空间开销同样很低
  • 可通过内置函数快速转成对应周的基准日期，例如转成当周周一：

    SELECT TO_DATE(202132::text || '1', 'IYYYIWID');
    

不推荐直接存储为2021/32格式的字符串：不仅占用空间更高，且容易出现格式不统一导致的排序错误、计算时需要额外拆分字符串，性能和可维护性都很差。

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