Postgresql – SQL beginner trying to calculate total time in minutes; need help with WHERE syntax error

postgresql

I'm trying to calculate the total minutes employees were on a system.
The data, stored as 'Action' looks something like below. Note that employees log on and off multiple times a day.

INSERT INTO Activity (empid, action, log_date) VALUES
('a1', 'ON', '2019-01-01T07:00:00.000Z'),
('a1', 'OFF', '2019-01-01T07:30:00.000Z'),
('a1', 'ON', '2019-01-01T08:30:00.000Z'),
('a1', 'OFF', '2019-01-01T11:30:00.000Z'),
('a1', 'ON', '2019-01-01T12:30:00.000Z'),
('a1', 'OFF', '2019-01-01T12:45:00.000Z'),
('a2', 'ON', '2019-01-01T08:45:00.000Z'),
('a2', 'OFF', '2019-01-01T09:15:00.000Z'),
('a2', 'ON', '2019-01-01T10:00:00.000Z'),
('a2', 'OFF', '2019-01-01T11:45:00.000Z');

Below is the SQL query code that I have been working on so far, but it's returning 'Query Error: error: syntax error at or near "WHERE"'.

SELECT e.empid, CAST(e.log_date AS DATE) AS date,
SUM(DATEPART(MINUTE, CAST(e.log_date - s.log_date AS TIME))) AS work_minutes
FROM (SELECT empid, log_date
      FROM Activity
      WHERE action = 'ON') AS s
JOIN (SELECT empid
      FROM Activity
      WHERE action = 'OFF') AS e
ON e.empid = s.empid
WHERE e.log_date = (SELECT log_date
                    FROM Activity
                    LIMIT 1
                    WHERE empid = e.empid
                    AND log_date > s.log_date)
GROUP BY e.empid, CAST(e.log_date AS DATE);

What seems to be the problem? I want the query result to look something like this:

|---------------------|------------------|------------------------|
|         empid       |        date      |      work_minutes      | 
|---------------------|------------------|------------------------|
|          a1         |    2019-01-01    |        100 minutes     |
|---------------------|------------------|------------------------|
|          b2         |    2019-01-01    |        150 minutes     |
|---------------------|------------------|------------------------|

(work_minutes values above are random)

Any help will be greatly appreciated.
Thank you!

Best Answer

You are overcomplicating things.

You only need one derived table (aka "sub-select"). Using a lateral join makes it possible to push the values from the outer query into the sub-select:

select a1.empid, a1.log_date::date, sum(a2.log_date - a1.log_date) as duration
from activity a1
  join lateral (
    select min(off.log_date) as log_date
    from activity off
    where off.action = 'OFF'
      and off.log_date > a1.log_date
      and off.empid = a1.empid
  ) a2 on true
where a1.action = 'ON'         
group by a1.empid, a1.log_date::date;

This assumes that an OFF event always comes after an ON event.

a2.log_date - a1.log_date returns an interval. Do turn that into minutes, extract the epoch value from that interval which is the interval in seconds. Then divide that by 60:

extract(epoch from sum(a2.log_date - a1.log_date)) / 60 as minutes
Related Question