how to convert integer minutes to interval in postgres

sql postgresql intervals
Abhijeet Gulve picture Abhijeet Gulve · Apr 27, 2017 · Viewed 18.9k times · Source

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

Is their 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

Answer

Evan Carroll picture Evan Carroll · Apr 27, 2017

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;

Related questions

Make a range in postgres

How can I make a range in a select in PostgreSQL? I'd like to have this result: num --- 1 2 3 4 5 6 From a query like: SELECT range(1,6) AS num;

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More