hour() function of excel in postgres (equivalent)

Question

I am working recently with postgres and I have to make several calculations. However I have not been able to imitate the HOUR () function of Excel, I read the official information but it did not help me much.

The function receives a decimal and obtains the hour, minutes and seconds of the same, example the decimal 0,99988426 returns 11:59:50. Try doing this in postgres (i use PostgreSQL 10.4) with the to_timestamp function: select to_char (to_timestamp (0.99988426), 'HH24: MI: SS'); this return 19:00:00. Surely I am omitting something, some idea of how to solve this?

Answer 1

24:00:00 or 86400 seconds = 1 
Half day(12:00 noon) or 43200 seconds = 43200/86400 = 0.5
11:59:50 or 86390 seconds = 86390/86400 = 0.99988426

So to convert your decimal value to time, all you have to do is multiply it with 86400 which will give you seconds and convert it to your format in following ways:

SELECT TO_CHAR((0.99988426 * 86400) * '1 second'::interval, 'HH24:MI:SS');

SELECT (0.99988426 * 86400) * interval '1 sec';