I would like to get start and end of working hour by each machine per day-CodePudding

there is a table name events which has the time of the work of different machine, each machine per day work different hours. i would like to get the start time and end time of each machine for each day of month

    timestamp      | machine_id
--------------------- ------------
 2021-11-10 12:00:14 |         24
 2021-11-10 12:10:14 |         24
 2021-11-16 07:18:44 |          9
 2021-11-16 07:21:13 |          5
 2021-11-16 07:21:14 |         11
 2021-11-15 12:30:35 |         24
 2021-11-15 12:40:35 |         24
 2021-11-15 12:50:35 |         24
 2021-11-15 13:00:35 |         24
 2021-11-15 13:10:35 |         24
 2021-11-15 13:20:35 |         24
 2021-11-15 13:30:35 |         24
 2021-11-16 07:10:59 |          5
 2021-11-16 07:11:14 |         11
 2021-11-16 07:11:13 |          5
 2021-11-16 07:11:56 |         12
 2021-11-16 07:07:47 |         21
 2021-11-16 07:08:51 |         21
 2021-11-16 07:13:44 |         22

CodePudding user response：

SELECT machine_id, date(timestamp) as workday, MIN(timestamp) as starttime, MAX(timestamp) as endtime
FROM events 
GROUP BY machine_id, date(timestamp)
ORDER BY machine_id, date(timestamp);

You need to group by machine and date both and then find min and max event time.

CodePudding user response：

Try this:

SELECT machine_id, MIN(timestamp) as start, MAX(timestamp) as stop
FROM events
GROUP BY machine_id;

CodePudding user response：

Here is solution. It takes every date and machine_id and pulls first and last timestamp for that date and machine_id.

with withMaxAndMin as
(
    SELECT max(timestamp) OVER(PARTITION BY machine_id, CAST (timestamp as date)
    ORDER BY CAST (timestamp as date) ASC) as endtime,
    min(timestamp) OVER(PARTITION BY machine_id, CAST (timestamp as date)
    ORDER BY CAST (timestamp as date) ASC) as starttime,
    machine_id,
    CAST (timestamp as date) as 'date',
    timestamp
    FROM #temptable
)
Select date, machine_id, starttime, endtime from withMaxAndMin group by machine_id, date, starttime, endtime

CodePudding user response：

Try this.

SELECT machine_id, MIN(timestamp) as starttime, MAX(timestamp) as  Endtime
FROM mach  group by machine_id ,CAST(timestamp AS DATE)

Here i removed machine_id from where clause, bcz you want all machine with start and end time.

Form this you will get machine_id as 24 for 2 times , bcz when 2 dates are repeated for machine_id 24.

24  2021-11-10 12:00:00 2021-11-10 12:10:00
24  2021-11-15 12:31:00 2021-11-15 13:31:00
5   2021-11-16 07:11:00 2021-11-16 07:21:00
9   2021-11-16 07:19:00 2021-11-16 07:19:00
11  2021-11-16 07:11:00 2021-11-16 07:21:00
12  2021-11-16 07:12:00 2021-11-16 07:12:00
21  2021-11-16 07:08:00 2021-11-16 07:09:00
22  2021-11-16 07:14:00 2021-11-16 07:14:00