如何在SQL中基于RFC 3339格式日期计算死亡时年龄（含缺失值处理）

Alright, let's tackle this problem head-on. You need to compute the age (in years, formatted like "34岁") from two text fields (birth and death) storing RFC 3339 dates, with "Unknown" returned if either date is missing (marked as 'None'). Here's how to do this across popular SQL dialects:

PostgreSQL Solution #

PostgreSQL has great built-in support for timezone-aware timestamps, which makes parsing RFC 3339 dates straightforward. We'll use TO_TIMESTAMP to convert the text fields to timestamps, then AGE to calculate the interval between death and birth, and finally extract the year component:

SELECT
  CASE
    WHEN birth = 'None' OR death = 'None' THEN 'Unknown'
    ELSE 
      CAST(
        EXTRACT(YEAR FROM AGE(TO_TIMESTAMP(death, 'YYYY-MM-DD"T"HH24:MI:SSTZ'), TO_TIMESTAMP(birth, 'YYYY-MM-DD"T"HH24:MI:SSTZ')))
        AS TEXT
      ) || '岁'
  END AS age
FROM your_table;

• TO_TIMESTAMP parses the RFC 3339 string into a timezone-aware timestamp.
• AGE computes the exact interval between the two dates, automatically accounting for whether the death date falls before or after the birthday in the death year.
• We cast the extracted year to text and append "岁" to match your desired format.

MySQL Solution #

MySQL's TIMESTAMPDIFF function simplifies calculating year differences while handling the birthday check automatically. We'll use STR_TO_DATE to parse the RFC 3339 strings first:

SELECT
  CASE
    WHEN birth = 'None' OR death = 'None' THEN 'Unknown'
    ELSE
      CONCAT(
        TIMESTAMPDIFF(YEAR, STR_TO_DATE(birth, '%Y-%m-%dT%H:%i:%s%z'), STR_TO_DATE(death, '%Y-%m-%dT%H:%i:%s%z')),
        '岁'
      )
  END AS age
FROM your_table;

• STR_TO_DATE uses the format specifier %z to handle the timezone offset in RFC 3339.
• TIMESTAMPDIFF(YEAR, start, end) returns the whole number of years between the two dates, so it'll correctly return 28 for your sample data (birth 0012-08-31, death 0041-01-24) since the death date is before the birthday that year.

SQL Server Solution #

SQL Server requires a bit more manual work to account for the birthday check, since DATEDIFF(YEAR) just counts the year boundary crossings. We'll use TRY_CONVERT to parse the RFC 3339 strings into datetimeoffset types:

SELECT
  CASE
    WHEN birth = 'None' OR death = 'None' THEN 'Unknown'
    ELSE
      CONCAT(
        DATEDIFF(YEAR, TRY_CONVERT(datetimeoffset, birth), TRY_CONVERT(datetimeoffset, death))
        - CASE 
            WHEN DATEADD(YEAR, DATEDIFF(YEAR, TRY_CONVERT(datetimeoffset, birth), TRY_CONVERT(datetimeoffset, death)), TRY_CONVERT(datetimeoffset, birth)) > TRY_CONVERT(datetimeoffset, death) 
            THEN 1 
            ELSE 0 
          END,
        '岁'
      )
  END AS age
FROM your_table;

• TRY_CONVERT safely converts the text to a timezone-aware datetime; if the format is invalid, it returns NULL (you can add OR birth IS NULL OR death IS NULL to the CASE condition if you want to treat invalid formats as "Unknown").
• The extra CASE statement adjusts the year difference: if adding the calculated years to the birth date results in a date later than the death date, we subtract 1 (since the birthday hadn't occurred yet in the death year).

Key Notes #

• Format Validation: If your birth/death fields might have invalid RFC 3339 strings (not just 'None'), add checks for NULL values (from failed conversions) to the CASE condition, e.g., WHEN birth = 'None' OR death = 'None' OR birth IS NULL OR death IS NULL THEN 'Unknown'.
• Unit Customization: If you need English instead of Chinese, replace "岁" with " years old".
• Sample Data Test: For your example (birth "0012-08-31T00:53:28+00:53", death "0041-01-24T00:53:28+00:53"), all these queries will return "28岁" which is correct.

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