CODEWITHSUNDEEP
sqlpostgresql-9.4
Y A N I V
Y A N I V

Reputation: 65

PostgreSQL: How to change the age Format in the output

I have a patient_age column in patient table and the format of the age in that column will be like this,

10D8M45Y

(It means 10 Days 8 Months 45 Years)

I need a Postgresql Query which can give the output like this, 

45 Years 8 Months 10 Days

Thank You.

Upvotes: 3

Views: 5922

Answers (2)

Richard Haussmann
Richard Haussmann

Reputation: 73

As mentioned above, you'd want to calculate age at the time of the query (if possible) like this...

SELECT extract(year from AGE(end_date, NOW()))||' Years '||
    extract(month from AGE(end_date, NOW()))||' Months '||
    extract(day from AGE(end_date, NOW()))||' Days '||
    extract(hour from AGE(end_date, NOW()))||' Hours '||
    extract(minute from AGE(end_date, NOW()))||' Minutes' as formatted_age
FROM my_table

It might be the case that you're dealing with something that got hard coded into a field.

SELECT
    extract(DAY FROM INTERVAL '10D8M45Y') || ' days ' ||
    extract(MONTH FROM CAST(REPLACE('10D8M45Y', 'M', 'Mon') AS INTERVAL)) || ' months ' ||
    extract(YEAR FROM INTERVAL '10D8M45Y') || ' years ';

Upvotes: 0

Hardik
Hardik

Reputation: 1717

Format of the age in that column like this 'YY "Years" mm "Months" DD "Days".

SELECT  TO_CHAR(age(current_date, '2017-04-17 00:00:00.0'), 'YY "Years" mm "Months" DD "Days"') as length_of_service

Upvotes: 5

Related Questions