Categorize datetime value to shifts where shift ends on the next day

I have a MySQL query for example:

select sum(qty) as qty
from myitem
where filedatetime >='2019-08-01 06:30:00'
  and filedatetime < '2019-09-01 18:30:00'

My table only have qty and filedatetime columns.

Based on this query is it possible to query out the following conditions:

• if filedatetime is filedatetime >= ‘2019-08-01 06:30:00’ and filedatetime < ‘2019-08-01 18:30:00’ then
  • assign value ‘Day’ to column shift
  • assign value ‘2019-08-01’ to column shiftDate
• if filedatetime >= ‘2019-08-01 18:30:00’ and filedatetime < ‘2019-08-02 06:30:00’ then
  • assign value ‘Night’ to column shift
  • assign value ‘2019-08-01’ to column shiftDate

So this how the result will be look like:

shiftDate   |   Shift   |   QTY     |
2019-08-01  |   Day     |   10      |
2019-08-01  |   Night   |   12      |
2019-08-02  |   Day     |   11      |
2019-08-02  |   Night   |   15      |
2019-08-11  |   Day     |   11      |
2019-08-11  |   Night   |   18      |
2019-08-12  |   Day     |   13      |
2019-08-12  |   Night   |   19      |

Answer

You can use Time() function to extract time (hh:mm:ss) portion out of a datetime value. Once we have determined that, we can use CASE .. WHEN conditional expression, to check whether “Day” or “Night” shift based on your defined conditions. Also, we can use Date() function to extract the date (YYYY-mm-dd) portion and add/subtract 1 day accordingly.

We can then GROUP BY on these calculated expressions, and calculate sum of the qty; no need of using a subquery for further aggregation:

SELECT 

  -- Determining day of the shift
  CASE 
     -- When below 06:30:00 then previous day's Night shift
     WHEN TIME(filedatetime) < '06:30:00'
     THEN DATE(filedatetime) - INTERVAL 1 DAY
     -- Else When >= 06:30:00 then current day's shift
     ELSE DATE(filedatetime)
  END AS shiftDate, 

  -- Determining Night / Day
  CASE 
      -- When between 06:30:00 (inclusive) and 18:30:00 then Day
      WHEN TIME(filedatetime) >= '06:30:00' AND TIME(filedatetime) < '18:30:00'
      THEN 'Day'
      ELSE 'Night'
  END AS shift, 

  -- Do aggregation
  SUM(qty) AS qty 
FROM myitem 
WHERE filedatetime >='2019-08-01 06:30:00'
  AND filedatetime < '2019-09-01 18:30:00'
GROUP BY shiftDate, shift