Current time based select query

[muppet]

We have 3 shifts running at our plant,

day 0720 - 1520
swing 1520 - 1120
night 2320 - 0720

I have a table storing the bags packed on each shift, and now need to display the team (tech, packer_l, packer_r etc) with the greatest number of packed bags depending on the time of day it is now.

So if it is 1100 (am) I want the query to return the best day shift result for this month.

The query works fine if I give it a time range manually but I cannot get the case to work right. I have tried if, else without success.

select day(timestamp) as timestamp, tech, packer_l, packer_r,  (packed_l + packed_r) as packed, timestamp as ts
  FROM abm_status
  where
    time(ts) between

      case time(now())
        when between '07:20:00' and '15:19:59'
        then '07:20:00' and '15:19:59'

        when between '15:20:00' and '23:19:59'
        then '15:20:00' and '23:19:59'

        when between '23:20:00' and '07:19:59'
        then '23:20:00' and '07:19:59'

      end case
    and tech != ''
    and tech != 'Dummy Login'
    and YEAR(timestamp) = YEAR(CURDATE()) AND MONTH(timestamp) = MONTH(CURDATE())

  order by packed desc limit 1

[d5e5]

I think the problem is that your CASE statement is evaluating to the number 1 instead of the string that you want for completing your WHERE condition. Look at this example:

mysql> select '07:20:00' AND '15:19:59' as shift;
+-------+
| shift |
+-------+
|     1 | 
+-------+

See? The result is 1 instead of '07:20:00' AND '15:19:59'. Put double quotes around the result to get a string:

mysql> select "'07:20:00' AND '15:19:59'" as shift;
+---------------------------+
| shift                     |
+---------------------------+
| '07:20:00' AND '15:19:59' | 
+---------------------------+

[muppet]

I can see why that would stop it from working. I have added the " around the case statement as you suggested, but still have no output.

Here is the error I am getting

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'between '07:20:00' and '15:19:59'
        then "'07:20:00' and '15:19:59'"

 ' at line 7

Am I using CASE correctly? Can I use CASE with BETWEEN??

[tesuji]

Hello again

He, what about the 66 whales, could any of them be rescued?

Hell yeah, '07:20:00' AND '15:19:59' evaluates to 1! I have never seen other database systems than mysql doing seriously boolean algebra on time strings! Though this be kind of compensation for the below rough violation of ANSI SQL standard aliases? (now() of 1st line can differs from 2nd and 3rd lines ones!)

No, your case is not correct. I would make use of such a construct:

select time(now()) as "too sad, mysql cannot use this alias elsewhere",
case 
  when (time(now()) between '07:20:00' and '15:19:59') then 'The early bird catches the worm'
  when (time(now()) between '15:20:00' and '23:19:59') then 'We are from swinging' 
  else 'Nighty night everyone!'
end as "what a pitty";
/*
too sad, mysql cannot use this alias elsewhere  what a pitty
--------------------------------------------------------------------
15:50:42                                        We are from swinging

*/

-- tesu

(btw, a practial example like your earlier one would help much)

[muppet]

tesu,

It took me a while to work out what your 66 whales was referring to. I think you have a much better grasp on the news in NZ than I do, my scope does not go much past the rugby scores :P

I understand the changes you made and implemented them to get this query

select day(timestamp) as tamp, tech, packer_l, packer_r,  (packed_l + packed_r) as packed, timestamp as ts
  FROM abm_status
  where
    time(timestamp) between

      case
        when (time(now()) between '07:20:00' and '15:19:59')
        then "'07:20:00' and '15:19:59'"

        when (time(now()) between '15:20:00' and '23:19:59')
        then "'15:20:00' and '23:19:59'"

        when (time(now()) between '23:20:00' and '07:19:59')
        then "'23:20:00' and '07:19:59'"

      end
    and tech != ''
    and tech != 'Dummy Login'
    and YEAR(timestamp) = YEAR(CURDATE()) AND MONTH(timestamp) = MONTH(CURDATE())

  order by packed desc limit 1

This is the result I get, note the time in the timestamp is outside of where it should be.

12, 'Juan Velsquez', 'Herminia Garcia', 'Herminia Garcia', 23483, 2010-08-12 07:18:03

If I remove the 'limit 1' from the end of the code I get all the entries for this month in order and from all shifts. If I change the query time to outside of the current time (from then "'07:20:00' and '15:19:59'" to then "'14:20:00' and '15:19:59'" while it is currently 11 am) I get a blank result which tells me it is using the correct case, just not implementing it properly

[tesuji]

Hi muppet

CASE is kind of function which is only allowed to return a constant value. However with "'07:20:00' and '15:19:59'" you return part of an executeable sql statement (AND) what is illegal. Actually, mysql must notify an error yet remains silently.

Also experts then rubbing their eyes in disbelief for their statement is considered to be correct by the mighty system though the result is absurd ("...note the time in the timestamp is outside of where it should be.")

However that be, you could try this modified query:

select day(timestamp) as tamp, tech, packer_l, packer_r,  (packed_l + packed_r) as packed, timestamp as ts
 FROM abm_status
  where
   (
   case
     when (time(timestamp) between '07:20:00' and '15:19:59') then 'day' 
     when (time(timestamp) between '15:20:00' and '23:19:59') then 'swing' 
     else 'night' 
   end 
     
   =
   
   case
     when (time(now()) between '07:20:00' and '15:19:59') then 'day' 
     when (time(now()) between '15:20:00' and '23:19:59') then 'swing' 
     else 'night' 
   end
   )
   and tech != ''  
   and tech != 'Dummy Login' 
   and YEAR(timestamp) = YEAR(CURDATE()) AND MONTH(timestamp) = MONTH(CURDATE())
   order by packed desc; /* limit 1  ;*/

Please tell me whether this will work successfully, also what modifications you did to get it going.

Hint: You can't write that: "(time(now()) between '23:20:00' and '07:19:59')". Also this is formally incorrect for the left boundary must always be less or equal to the right one, usually you don't get an error reported, even on Oacle database. Neither you can interchange the boundaries. Instead you should write:

(time(now()) between '23:20:00' and '23:59:59') OR (time(now()) between '00:00:00' and '07:19:59').

Do NOT forget the parentheses around both OR-termes for the next mysql gotcha is all around you, silently of course!

tata for now,

-- tesu

As for the 66 pilot whales, our news usually report such stuff in great detail to keep the whole nation deeply aghast at such act of nature, so no one can overlook it.

[d5e5]

I think tesu may have solved this. However I find the query hard to test without your data, so if you still have a problem with it I would consider putting the CASE logic into a function. Then we can easily test the function without having your data and you can incorporate the function into your query, making it more readable and easier to debug.

You can create a function called determine_shift as follows (change the DEFINER to the appropriate user)

DROP FUNCTION `determine_shift`//
CREATE DEFINER=`root`@`localhost` FUNCTION `determine_shift`(ts TIMESTAMP) RETURNS varchar(5) CHARSET latin1
BEGIN
    IF ISNULL(ts) THEN RETURN NULL; END IF;
    
 CASE
     WHEN (TIME(ts) between '07:20:00' and '15:19:59') THEN RETURN 'day';
     WHEN (TIME(ts) between '15:20:00' and '23:19:59') THEN RETURN 'swing';
     WHEN (TIME(ts) BETWEEN '23:20:00' AND '23:59:59') OR (TIME(NOW()) BETWEEN '00:00:00' AND '07:19:59') THEN RETURN 'night';
     ELSE RETURN 'ERROR';
 END CASE;
END

Now we can test it as follows:

mysql> select determine_shift('2010-08-25 07:54:36') as should_be_day;
+---------------+
| should_be_day |
+---------------+
| day           | 
+---------------+
1 row in set (0.00 sec)

mysql> select determine_shift('2010-08-25 16:54:36') as should_be_swing;
+-----------------+
| should_be_swing |
+-----------------+
| swing           | 
+-----------------+
1 row in set (0.00 sec)

mysql> select determine_shift('2010-08-25 23:54:36') as should_be_night;
+-----------------+
| should_be_night |
+-----------------+
| night           | 
+-----------------+
1 row in set (0.00 sec)

Seems to work OK.

Now you can use it in your query as follows:

select day(timestamp) as tamp, tech, packer_l, packer_r,  (packed_l + packed_r) as packed, timestamp as ts
  FROM abm_status
  where
    determine_shift(timestamp) = determine_shift(TIME(NOW()))
    and tech != ''
    and tech != 'Dummy Login'
    and YEAR(timestamp) = YEAR(CURDATE()) AND MONTH(timestamp) = MONTH(CURDATE())

  order by packed desc limit 1

[muppet]

Thanks d5e5, but tesu hit it on the head. Worked perfectly tesu! Well done.