PostgreSQL convert float value to time, hh:mm

Question

I am converting from SQL server to PostgreSQL. I have a function that take a float and converts it to a time. For example if the float number is 12.5 it converts to 12:30. Is there anyway to do this in postgres? Thanks

Answer 1

Something like this?

select to_char(to_timestamp((12.5) * 60), 'MI:SS');

DEMO

If seconds is the only thing you need, you can write something like this:

select date_part('second', to_timestamp((12.37) * 60))::int;

or

select to_char(to_timestamp((12.37) * 60), 'SS');

Answer 2

In my personal case, this worked:

to_char(to_timestamp(table.time_stamp/1000), 'DD/MM/YYYY HH24:MI:SS')

You can even change the date format (for instance, 'mm/dd/yyyy' or 'dd-mm-yyyy) and the space between the date and the time (another example: 'dd/mm/yyyy | HH24:MI:SS')