Converting a string to date in SQL-CodePudding

I am working on a table where I have two cols named birth and death with dates as strings(to be specific character varying).

I want to calculate the age by using those two columns. If any one of the values is missing, i.e. if either birth is missing or death is missing then it should return 'unknown' (By the way in the table that I am working with the missing values are given as None)

When I try to convert them into date either they are giving me an error or they are giving a wrong age.

For Example, let's say these are the birth and death dates respectively:
birth: 0133-01-30T00:53:28 00:53
death: 0193-07-01T00:53:28 00:53

I used the the following command:
CAST(death as date) - CAST(birth as date)
But this is returning ages such as 2210 or just some absurd age
BY THE WAY I AM DOING THIS IN THE JUPYTER NOTEBOOK USING POSTGRESQL

CodePudding user response：

You can use postgresql age function to calculate the age.

Example:

SELECT coalesce(age('0193-07-01T00:53:28 00:53'::timestamp, '0133-01-30T00:53:28 00:53'::timestamp)::text, 'Unknown');
          age
------------------------
 60 years 5 mons 2 days
(1 row)

To get years:

SELECT coalesce(EXTRACT(years FROM age('0193-07-01T00:53:28 00:53'::timestamp, '0133-01-30T00:53:28 00:53'::timestamp))::text, 'Unknown');

 extract
---------
      60
(1 row)

SELECT coalesce(EXTRACT(years FROM age('0193-07-01T00:53:28 00:53'::timestamp, null))::text, 'Unknown');
 coalesce
----------
 Unknown
(1 row)