I have a table called daily_report that register what time/date worker goes out from the building and what time/date he/she comes in.
CREATE TABLE daily_report ( id serial, worker_id integer, in_or_out integer, the_time time, the_date date );
I assigned "in" status to 1, "out" to 0. I need a query that shows when the worker first enter the building and when he/she exits it in the specific day.(notice some workers may enter the building and exit next day, like night guards) . Like : Select min(time), max (time) group by date........ I tried to join table to itself but cant get a result. Trigger function also will be ok that records daily report in a new table like worker with id 3 enters 08:00 am, exits 18 00 pm in '2016-01-01' for example
please help me to do that.