How to return interval as a ISO8601 duration?-CodePudding

Suppose I have a query:

SELECT max(ended_at) - min(started_at)
FROM test_run

How would I return the result as a ISO8601 duration?

i.e. If time different is 5 seconds, I would I expect the response to be PT5S.

CodePudding user response：

Set the IntervalStyle to the desired format:

SET IntervalStyle to iso_8601; -- can be done in the config file as well

SELECT INTERVAL '5 seconds';

Result: PT5S

CodePudding user response：

What you are looking is basically just a format conversion with specific output format. You can achieve this, in a single trip to the server by creating function which sets the InstervalStyle the echos back the input value. (see demo)

create or replace function iso_interval_format(interval_in interval) 
   returns interval 
  language plpgsql 
as $$
begin 
     set IntervalStyle to iso_8601;
     return interval_in; 
end;
$$;

Caution: If working strictly with dates be sure to cast them to timestamp - at least one of them.

CodePudding user response：

For my particular use case, I was able to do it as:

SELECT 'PT' || ceil(extract(epoch from max(ended_at) - min(started_at))) || 'S'
FROM test_run