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.

New terrain always seems more complex at first. Wait till you have gathered some experience. — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 14, 2017 at 2:11

Erwin Brandstetter · Accepted Answer · 2022-01-25 09:48:17Z

6

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.

answered Oct 14, 2017 at 2:06

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1