Search code examples
postgresqldatetimestring-function

Convert PostgreSQL age function output to upper case

I am working with PostgreSQL 8.4.4. I am calculating time difference between two Unix time-stamps using PostgreSQL's age function. I am getting the output as expected. The only thing I want is to convert the time difference in UPPERCASE. For example,

select coalesce(nullif(age(to_timestamp(1389078075), to_timestamp(1380703432))::text,''), UPPER('Missing')) FROM transactions_transactions WHERE id = 947

This query giving the result as

3 mons 4 days 22:17:23

But I want this output to be like

3 MONTHS 4 DAYS 22:17:23

Note: I am using this for dynamic report generation purpose. So I cannot convert it to UPPERCASE after fetching from database. I want it to be in UPPERCASE at the time of coming from database itself, i.e., in the query.

Solution

  • PostgreSQL's upper() function should be use 

    SELECT upper(age(to_timestamp(1389078075), to_timestamp(1380703432))::text) 
FROM transactions_transactions WHERE id = 947

    as per OP's comment and edit

    select upper(coalesce(nullif(age(to_timestamp(1389078075), to_timestamp(1380703432))::text,''), UPPER('Missing')))