Getting the time spent in the room by the logs of inputs / outputs?

Question

SQL

Good afternoon.
There is a table with a log of employees entering / exiting the premises:

trans_date    timestamp
user_id       integer
trans_type    integer -- 0=вход, 1=выход

I need to find the total time spent by a given employee in a room on a given day.
Whether it is possible to manage with normal request or it is necessary to write procedure?

Answer 1

I suspect that it is necessary to complicate the task: Some employees may check in, but not check in (by mistake to marry someone for the company) and vice versa ... right?
And yes, in any case, you can get by with one request.

Answer 2

The most primitive option:
using window functions, we calculate the delta between adjacent events (for example, in minutes or seconds),
then we aggregate this by employee days, and summarize the deltas, indicating the sign of entry / exit as a sign,
then start shedding bloody tears in situations where "twice threw , caught once" or "went out twice, then went in", "went in, but did not go out", etc.
a similar, but slightly more convenient option in terms of detection - using pivot, we decompose the timestamps into two columns, and then, as in the first option, deltas (only having the ability to filter out-entered)
ps from practice - a completely unpromising path if at the input there is no lock-mill under the supervision of two stern Vokhrovites.
A more promising option - you can offer a more detailed description of the original task / problem