'postgres time conversion hh:mm:ss.us to hh:mm

Using PostgreSQL database for my attendance application.

I have a table with IN and out times (hh:mm:ss.us format).

When I subtract the times (OUT -IN) to calculate the working hours, results are not as expected due to precision.

If IN Time is "22:12:56.09" & OUT TIme is "22:14:06.06" the difference considering only HH:mm should be 00:02 but it actually shows "00:01:09.97" which becomes "00:01" in excel using only HH:mm.

I am trying to do the time conversion from hh:mm:ss.us to hh:mm (time format) so that I can subtract the time and get the desired output.

I have done similar things in SQL Server but I did not find any function in PostgreSQL. Please advise.

postgresqldatetimetime


Solution 1:[1]

First you need to truncate the seconds. Then subtract to get desired result

select

to_char(
  (
    to_char('22:14:06.06' :: time, 'HH24:MI'):: time - 
    to_char('22:12:56.09' :: time, 'HH24:MI'):: time
  ),
  'HH24:MI'
)

Result: 00:02

General Solution:

select
  to_char(
  (
    to_char(out, 'HH24:MI'):: time - to_char(in, 'HH24:MI'):: time
  ),
  'HH24:MI'
)

Here the purpose of to_char() is to format result to hours:minutes and not to include seconds. Postgres includes seconds in interval by default.

Solution 2:[2]

You can use the date_trunc function with timestamp.

It would work something like this:
select date_trunc('minute', out) - date_trunc('minute', in)

This would set a minute level precision on the timestamp and convert HH:mm:ss to HH:mm:00

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions