如何在Google BigQuery中实现PostgreSQL的age()函数精确日期差计算

Google BigQuery没有内置的AGE()函数，直接用DATE_DIFF按单一时间单位计算会出现精度偏差。比如：

PostgreSQL中执行：

SELECT AGE('2020-10-10','2000-10-11');

返回结果："19 years 11 mons 30 days"

但在BigQuery中直接按年计算：

SELECT DATE_DIFF(SAFE.PARSE_DATE('%Y%m%d', SAFE_CAST(20201010 AS STRING)), SAFE.PARSE_DATE('%Y%m%d', SAFE_CAST(20001011 AS STRING)), YEAR);

得到结果：20，这与实际精确时间差不符。

自定义精确计算方案 #

通过分步计算年、月、日差值，并处理借位逻辑，可以模拟PostgreSQL AGE()的效果。以下是可直接使用的SQL代码：

WITH date_inputs AS (
  -- 替换为你的目标结束日期和起始日期
  SELECT 
    DATE '2020-10-10' AS end_date,
    DATE '2000-10-11' AS start_date
),
calculate_years AS (
  SELECT
    end_date,
    start_date,
    -- 计算精确年差：若结束日期早于「起始日期+初始年差」，则年差减1
    CASE
      WHEN end_date < DATE_ADD(start_date, INTERVAL DATE_DIFF(end_date, start_date, YEAR) YEAR)
      THEN DATE_DIFF(end_date, start_date, YEAR) - 1
      ELSE DATE_DIFF(end_date, start_date, YEAR)
    END AS years,
    -- 基于年差调整起始日期，用于后续月差计算
    DATE_ADD(start_date, INTERVAL 
      CASE
        WHEN end_date < DATE_ADD(start_date, INTERVAL DATE_DIFF(end_date, start_date, YEAR) YEAR)
        THEN DATE_DIFF(end_date, start_date, YEAR) - 1
        ELSE DATE_DIFF(end_date, start_date, YEAR)
      END YEAR) AS adjusted_start_year
  FROM date_inputs
),
calculate_months AS (
  SELECT
    years,
    end_date,
    adjusted_start_year,
    -- 计算精确月差：若结束日期早于「调整后起始日期+初始月差」，则月差减1
    CASE
      WHEN end_date < DATE_ADD(adjusted_start_year, INTERVAL DATE_DIFF(end_date, adjusted_start_year, MONTH) MONTH)
      THEN DATE_DIFF(end_date, adjusted_start_year, MONTH) - 1
      ELSE DATE_DIFF(end_date, adjusted_start_year, MONTH)
    END AS months,
    -- 基于月差再次调整起始日期，用于后续日差计算
    DATE_ADD(adjusted_start_year, INTERVAL 
      CASE
        WHEN end_date < DATE_ADD(adjusted_start_year, INTERVAL DATE_DIFF(end_date, adjusted_start_year, MONTH) MONTH)
        THEN DATE_DIFF(end_date, adjusted_start_year, MONTH) - 1
        ELSE DATE_DIFF(end_date, adjusted_start_year, MONTH)
      END MONTH) AS adjusted_start_month
  FROM calculate_years
)
SELECT
  CONCAT(
    CAST(years AS STRING), ' years ',
    CAST(months AS STRING), ' mons ',
    CAST(DATE_DIFF(end_date, adjusted_start_month, DAY) AS STRING), ' days'
  ) AS age_result
FROM calculate_months;

逻辑说明 #

1. 年差计算：先通过DATE_DIFF得到初始年差，再验证结束日期是否达到「起始日期+初始年差」的时间点，若未达到则年差减1，确保得到完整的整年数。
2. 月差计算：基于调整后的起始日期（已加上精确年差），重复类似年差的计算逻辑，得到完整的整月数。
3. 日差计算：用结束日期减去最终调整后的起始日期，得到剩余的天数。
4. 结果拼接：将年、月、日差按X years Y mons Z days的格式拼接，与PostgreSQL AGE()的输出格式一致。

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