Search code examples
sqlpostgresqltimesyntaxcasting

Cast integer with hour to time in PostgreSQL

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.

Solution

  • 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: