Home -> Usenet -> comp.databases.mysql -> Re: SQL lite query for get the records in which current time range exist between range of time slots

Re: SQL lite query for get the records in which current time range exist between range of time slots

From: Stefan Ram <ram_at_zedat.fu-berlin.de>
Date: 29 Apr 2016 14:12:00 GMT
Message-ID: <time-20160429150621_at_ram.dialup.fu-berlin.de> 






smallubotlla_at_gmail.com writes:


>The requirement is to get the time slot from sql table with


>the help of current time . Here is the table below . The

>problem arises when i am trying to get the time slot that

>surpasses 24th hours for example like the Night shift . if my

>current time is 21:00 i am unable to get the time slot .

>query made so far

>SELECT 


>  *,CURRENT_TIME


>FROM


>  tbl_shift 

>WHERE CURRENT_TIME BETWEEN shift_start

>  AND shift_end

>shift_id  shift_name  shift_start  shift_end  

>--------  ----------  -----------  -----------

>       1  Morning     09:00:00     13:00:00   

>       2  Evening    13:00:00      16:15:00   

>       3  Night       16:01:00     09:00:00 




  I don't have SQL lite handy, so I tried it with MySQL.



  You don't need to use a procedure. The procedure below is
  just there to try it out. The actual SELECT statement used
  can be found in the procedure. It can be used without the
  procedure.



mysql>


mysql> DROP SCHEMA S20160429;


Query OK, 1 row affected (0.01 sec)



mysql> CREATE SCHEMA S20160429;


Query OK, 1 row affected (0.00 sec)



mysql> USE S20160429;


Database changed

mysql>
mysql> CREATE TABLE TBL_SHIFT
    -> ( SHIFT_ID SERIAL,
    ->   SHIFT_NAME VARCHAR( 255 ) UNIQUE NOT NULL,
    ->   SHIFT_START CHAR( 8 ) NOT NULL,
    ->   SHIFT_END CHAR( 8 ) NOT NULL );


Query OK, 0 rows affected (0.06 sec)



mysql>


mysql> INSERT INTO TBL_SHIFT ( SHIFT_NAME, SHIFT_START, SHIFT_END ) VALUES ( 'Morning', '09:00:00', '13:00:00' );
Query OK, 1 row affected (0.00 sec)



mysql> INSERT INTO TBL_SHIFT ( SHIFT_NAME, SHIFT_START, SHIFT_END ) VALUES ( 'Evening', '13:00:00', '16:15:00' );
Query OK, 1 row affected (0.00 sec)



mysql> INSERT INTO TBL_SHIFT ( SHIFT_NAME, SHIFT_START, SHIFT_END ) VALUES ( 'Night',   '16:01:00', '09:00:00' );
Query OK, 1 row affected (0.02 sec)


mysql>
mysql> DELIMITER $$
mysql> CREATE PROCEDURE TRYOUT()
    -> BEGIN
    ->   DECLARE X INT DEFAULT 0;
    ->   DECLARE CURRENT_TIME_ VARCHAR( 255 ) DEFAULT '';
    ->   SET X = 1;
    ->   WHILE x <= 24 DO
    ->     SET CURRENT_TIME_ = IF( X < 10, CONCAT( '0', X, ':00:00' ), CONCAT( X, ':00:00' ));
    ->     SELECT
    ->       *, CURRENT_TIME_
    ->     FROM
    ->       TBL_SHIFT
    ->     WHERE IF
    ->     ( SHIFT_START <= SHIFT_END,
    ->       CURRENT_TIME_ BETWEEN SHIFT_START AND SHIFT_END,
    ->       CURRENT_TIME_ BETWEEN SHIFT_START AND '24:00:00' OR
    ->       CURRENT_TIME_ BETWEEN '00:00:00' AND SHIFT_END );
    ->     SET X = X + 1;
    ->   END WHILE;
    -> END$$




Query OK, 0 rows affected (0.00 sec)


mysql> DELIMITER ;
mysql>
mysql> CALL TRYOUT();




+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 01:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.00 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 02:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.01 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 03:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.02 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 04:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.03 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 05:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.04 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 06:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.04 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 07:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.05 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 08:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.06 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+

|        1 | Morning    | 09:00:00    | 13:00:00  | 09:00:00      |
|        3 | Night      | 16:01:00    | 09:00:00  | 09:00:00      |


+----------+------------+-------------+-----------+---------------+
2 rows in set (0.06 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        1 | Morning    | 09:00:00    | 13:00:00  | 10:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.07 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        1 | Morning    | 09:00:00    | 13:00:00  | 11:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.08 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        1 | Morning    | 09:00:00    | 13:00:00  | 12:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.08 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+

|        1 | Morning    | 09:00:00    | 13:00:00  | 13:00:00      |
|        2 | Evening    | 13:00:00    | 16:15:00  | 13:00:00      |


+----------+------------+-------------+-----------+---------------+
2 rows in set (0.09 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        2 | Evening    | 13:00:00    | 16:15:00  | 14:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.10 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        2 | Evening    | 13:00:00    | 16:15:00  | 15:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.10 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        2 | Evening    | 13:00:00    | 16:15:00  | 16:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.11 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 17:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.11 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 18:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.12 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 19:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.13 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 20:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.13 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 21:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.14 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 22:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.14 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 23:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.15 sec)



+----------+------------+-------------+-----------+---------------+
| SHIFT_ID | SHIFT_NAME | SHIFT_START | SHIFT_END | CURRENT_TIME_ |

+----------+------------+-------------+-----------+---------------+
|        3 | Night      | 16:01:00    | 09:00:00  | 24:00:00      |
+----------+------------+-------------+-----------+---------------+
1 row in set (0.15 sec)



Query OK, 0 rows affected (0.16 sec)



mysql>
Received on Fri Apr 29 2016 - 16:12:00 CEST












Original text of this message