Question 114572 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to return interval as a ISO8601 duration?

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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search