Need to split out whole and partial hours from a time period (postgresql)

Question

I have a table of data that data that contains a date, time-in, and time-out pulled from a postgresql db table. Currently the table shows the time a person checked in, and out over a period of hours. I need to use postgresql to split out the hours between time-in and time-out such that I capture each full hour (eg. 10am-11am) and each fraction (eg. 4.15pm to 5pm) of the hour during which that person was checked in. So if a person checked in at 9.30am, took a 1 hour lunch break at 12, and checked out at 4.30pm, my current table would show two rows for that member i.e. one row for the time before lunch break, and the time after the lunch break. I want to show each hour (whole or partial) on each row, with 1 representing that 1 whole hour, and the minute portion to capture the partial hour worked. Below are the before and after images. Any help is appreciated.

Shows that I want to convert from

Shows that I want to convert to

Thanks

Answer 1

try this.

CAST(time_out - time_in AS TIME)