Search code examples
sqlpostgresqldateconverters

Age calculation in RFC 3339 date format in SQL

I want find age at time of death, in years (e.g., 34). There are two columns named birth and death. How to calculate age from this data,

enter image description here

Example for one row:

birth date = "0012-08-31T00:53:28+00:53" ,

death date = "0041-01-24T00:53:28+00:53" inSQL.

This is present as a text and have 'None' value. In cases where either the birth or death date are missing the value should read 'Unknown'. If missing the value, new column is age should show 'unknown'.

Solution

  • Use TIMESTAMPDIFF and CASE statement for null column

    SELECT birth_date,
       death_date,
       CASE 
           WHEN birth_date IS NULL OR death_date IS NULL
              THEN 'unknown'
           ELSE TIMESTAMPDIFF(year, birth_date, death_date) 
       END AS Age
FROM table

    Demo in db<>fiddle