postgresql query for hour minutes and seconds

SELECT to_char((col1 - col0), 'HH24 hrs MI "minutes" SS "seconds"') FROM T1; 

Here is a sqlfiddle : link

The to_char function takes an interval (an interval is the time span between two timestamps, and subtracting timestamps gives you an interval). It then takes a formatting, and you can apply pretty much what you want.

Formatting functions in PostgreSQL

Try use this sql:

SELECT to_char(column2 - column1, 'DD" days "HH24" hours "MI" minutes "SS" seconds"');

The subtraction of two timestamp or timestamptz values produces an interval. (While subtracting two date values produces an integer!)
Details about date/time types in the manual.

The default text representation of an interval may be sufficient:

SELECT timestamp '2017-1-6 12:34:56' - timestamp '2017-1-1 0:0'; 

Result is an interval, displayed as:

5 days 12:34:56 

If you need the format in the question, precisely, you need to specify how to deal with intervals >= 24 hours. Add 'days'? Or just increase hours accordingly?

@Nobody provided how to use to_char(). But add days one way or the other:

SELECT to_char(ts_col2 - ts_col1, 'DD" days "HH24" hours "MI" minutes "SS" seconds"'); 

Result:

05 days 12 hours 34 minutes 56 seconds 

'days' covers the rest. There are no greater time units in the result by default.

Simple

SELECT EXTRACT(year FROM LOCALTIMESTAMP(0) - yourFieldTime)||' year '|| EXTRACT(month FROM LOCALTIMESTAMP(0) - yourFieldTime)||' month '|| EXTRACT(day FROM LOCALTIMESTAMP(0) - yourFieldTime)||' day '|| EXTRACT(hour FROM LOCALTIMESTAMP(0) - yourFieldTime)||' hour '|| EXTRACT(minute FROM LOCALTIMESTAMP(0) - yourFieldTime)||' minute '|| EXTRACT(second FROM LOCALTIMESTAMP(0) - yourFieldTime)||' second ' AS full_time_as_you_wish FROM your_table; 

Result

full_time_as_you_wish
---------------------------------
0 year 0 month 0 day 0 hour 0 minute 0 second