Fetch data between morning and evening-CodePudding

im trying to fetch the data between two times, i.e., morning (7:30AM to 7:30PM) evening (7:30PM to 7:30AM). required only hour based output.{ > 7:30 < 19:30 as morning and > 19:30 < 7:30 as night)

Please suggest the query

Tablename: data
----------------
created_date     services_name   id
28-08-2022 18:54    KANE         1
28-08-2022 19:00    BAPLO        2
28-08-2022 23:22    BAPLO        3
28-08-2022 23:40    VLOTLS       4
29-08-2022 00:02    DELLP        5
29-08-2022 00:42    SECON        6
29-08-2022 02:00    BAPLO        7
29-08-2022 03:00    PRODC        8
29-08-2022 05:14    DELLP        9
29-08-2022 05:30    SECON        10
29-08-2022 05:42    SECON        11

im using below command to fetch output

SELECT 
  CONCAT( HOUR(created_date),  ' to ', CONCAT( HOUR(created_date), ':59:59' ) ) as time_frame,
  COUNT(*) 
FROM 
  data
GROUP BY 
  DATE(created_date), 
  HOUR(created_date)
ORDER BY
  DATE(created_date), 
  HOUR(created_date)

Out as below

0 to 0:59:59    2
2 to 2:59:59    1
3 to 3:59:59    1
5 to 5:59:59    3
18 to 18:59:59  1
19 to 19:59:59  1
23 to 23:59:59  2

But i required as

morning ( between 07:30 to 19:30) count is 2
evening ( between 19:30 to 07:30) count is 9

CodePudding user response：

You can perform a UNION to get two separate counts, one for morning times and one evening times. Within the WHERE clause, you can convert created_date to a time format using the TIME() data type and then plug in your morning and evening times within the BETWEEN condition.

Note that you have to use the NOT operator to negate the BETWEEN operator for the evening count because BETWEEN works within a range (small to large 0..1, not large to small 1..0) so the NOT operator will essentially get everything else outside of the morning BETWEEN range.

SELECT 'Morning' as time_frame, COUNT(*) AS `count` FROM temp 
         WHERE TIME(created_date)  
         BETWEEN '07:30:00' AND '19:30:00'
UNION
SELECT 'Evening' as time_frame, COUNT(*) AS `count` FROM temp 
         WHERE TIME(created_date)  
         NOT BETWEEN '07:30:00' AND '19:30:00'

Output:

time_frame	count
Morning	2
Evening	9

See Fiddle.