CODEWITHSUNDEEP
mysqlsqldate
parkway
parkway

Reputation: 276

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:

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      |

Upvotes: 2

Views: 556

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28854

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You should be able to use time functionality directly:

select mi.*,
       (case when cast(filedatetime as time) >= '06:30:00' and
                  cast(filedatetime as time) < '18:30:00'
             then 'day' else 'night'
        end) as shift
from myitem mi;

In MySQL, you can also use time(filedatetime) function instead of cast().

Upvotes: 0

Related Questions