how to convert integer minutes to interval in postgres

Question

I'm trying to convert minutes which are in integer to interval in postgres

Is there any function that will help me to convert it to interval or should i have divide it by 60 and get the final result

20 minutes will be like 00:20:00 as result 

Solitary

You should flag this and move it to Database Administrators – Mulish

Answer 1

Fastest way is with make_interval

make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)

So it looks like this (as suggested by @Teddy)

SELECT make_interval(mins => 20);

or,

SELECT make_interval(0,0,0,0,0,20);

Not to say that's the cleanest, if speed isn't an issue I prefer the * method @a_horse_with_no_name mentioned

SELECT 20 * '1 minute'::interval;

Mulish

Why not simply make_interval(mins := 20)? – Febrifacient

Answer 2

you can concat that integer with ' minutes':

t=# with i as (
  select 20::int n
)
select concat(n,' minutes')::interval 
from i;
  concat
----------
 00:20:00
(1 row)

Time: 1.220 ms

Update:

Or: interval '1' minute * n as a_horse_with_no_name says

Sandrocottus

I chose this one and used a shorter form of concatenation: select (n||' minutes')::interval – Kranz

Answer 3

In the case you are trying to insert data in an interval column, you can use integer seconds and PostgreSQL will convert it to a interval.

Quatrain

Unfortunately am getting this: ERROR Database error 22015: interval field value out of range: "86400000000" – Eggnog