SQL convert interval with days to hh:mm:ss format
Lately I've been dealing with some data from SQL database. I have to count average interval, but the result was in format:
x days xx:xx:xx
and it look just ugly.
So I wanted to convert it to format with only hours:minutes:seconds. I've learnt quickly that there is no simple command to do that.
There are many ways all over the internet, but most of them are not the perfect solutions for more than 3 records, cause they do some conversions on every record of data. I wanted to do convertion once, after I got average ready, to avoid performance loss.
So after reading psql documentation I've got this:
select to_char(cast((extract (epoch from avg(last_date-first_date)) || ' seconds') as interval),'HH24:MI:SS') from table where condition;
first_date and last_date, are dates in "timestamp without time zone" format.
First function (extract (epoch from ()) changes interval to seconds, then string " seconds" (|| ' seconds') is added to result.
Then it's converted to interval with cast () as interval, and then it's converted from interval in seconds to char in format HH:MM:SS with to_char('interval','HH24:MI:SS').
After that, for example from '1 day 12:44:50' you got '36:44:50'. Haven't found easier and faster way to do this.