Cast integer with hour to time in PostgreSQL

Question

I have a number passed as integer and would like to convert it to time, and use this number as hour.

I have found solutions for using a number as minutes, but I'm not familiar with PostgreSQL syntax. Not sure what to do here:

select CAST(to_char(czas, 'FM99909:99') AS TIME WITHOUT   
TIME ZONE)::TIME from test

The result would be:

00:15:00

But I'm looking for a way to make it:

15:00:00

I have been working with MS SQL for quite a while, I'm surprised how much more complex PostgreSQL syntax is.

Answer 1

If your column test.czas to signifies hours just multiply with interval '1 hour':

SELECT (interval '01:00' * czas)::time FROM test;

Produces your desired result exactly, even with fractional digits.

'01:00' and '1 hour' are equivalent interval literals.
For czas > 24 you get the remainder (full days cut off) - like if you'd use czas%24.

Related:

• Postgres data type cast
• How do I add a column to a date in Postgres?