Is there a way to select or query the data only on working hours?

I'd like to get the number of the alarm activated on or during working hours from mon-fri 8am to 5pm.

I tried to use the between date but no luck.

CodePudding user response：

SELECT *        -- if you need to count them only - use SELECT COUNT(*)
FROM datatable
WHERE WEEKDAY(`datetime`) < 5                               -- test weekday
  AND TIME(`datetime`) BETWEEN '08:00:00' AND '17:00:00';   -- test hours

CodePudding user response：

Here we use the following tests:

• weekday < 6 (Saturday) = Monday to Friday
• hour more than 7 (from 08:00:00)
• hour less than 17 (to 16:59:59)

create table alarms(  
id int,
description   varchar(100),
date_time datetime);
insert into alarms values
(1,'Alarm Activated',
'2022-01-02 14:00:00'),
(2,'Alarm Deactivated',
'2022-01-02 15:00:00'),
(3,'Alarm Activated',
'2022-01-03 18:00:00'),
(4,'Alarm Activated',
'2022-01-31 11:00:00');

select * 
from alarms
where weekday(date_time) < 6
and 7 < hour(date_time) < 17;

id | description     | date_time          
-: | :-------------- | :------------------
 3 | Alarm Activated | 2022-01-03 18:00:00
 4 | Alarm Activated | 2022-01-31 11:00:00

db<>fiddle here