问题描述:

I have next 6 fields in MySQL.

TIME_RIGHT_NOW_HOUR (INT)

TIME_RIGHT_NOW_MINUTE (INT)

TIME_WORKING_FROM_HOUR (INT)

TIME_WORKING_FROM_MINUTE (INT)

TIME_WORKING_TO_HOUR (INT)

TIME_WORKING_TO_MINUTE (INT)

Hour and minutes are in 24 time format.

I need to check is the right now time between two working times.

for example if TIME_RIGHT_NOW_HOUR is 15 and TIME_RIGHT_NOW_MINUTE is 30 -> 15:30

is it between TIME_WORKING_FROM_HOUR 10, TIME_WORKING_FROM_MINUTE 15

10:15 and TIME_WORKING_TO_HOUR 18, TIME_WORKING_TO_MINUTE 20 -> 18:20

网友答案:

Perhaps maketime() is what you are seeking.

SELECT 1
FROM table
WHERE 
  MAKETIME(TIME_RIGHT_NOW_HOUR,TIME_RIGHT_NOW_MINUTE,0)
    BETWEEN MAKETIME(TIME_WORKING_FROM_HOUR,TIME_WORKING_FROM_MINUTE,0)
    AND MAKETIME(TIME_WORKING_TO_HOUR,TIME_WORKING_TO_MINUTE,0);
网友答案:

Very easy solution: Convert hour&time to float then compare:

    TIME_WORKING_FROM_HOUR + TIME_WORKING_FROM_MINUTE / 100 -----> 10.15
    TIME_WORKING_TO_HOUR + TIME_WORKING_TO_MINUTE / 100; ------> 18.20
    TIME_RIGHT_NOW_HOUR + TIME_RIGHT_NOW_MINUTE / 100; ------> 15.30

SQL WHERE:

    WHERE ( (TIME_WORKING_FROM_HOUR + TIME_WORKING_FROM_MINUTE / 100) <= (TIME_RIGHT_NOW_HOUR +   TIME_RIGHT_NOW_MINUTE / 100) )
    AND ( (TIME_WORKING_TO_HOUR + TIME_WORKING_TO_MINUTE / 100) >= (TIME_RIGHT_NOW_HOUR +     TIME_RIGHT_NOW_MINUTE / 100) )
网友答案:

You can use XOR (or the IF function), in order to cope with midnight rolling (i.e., FROM is 22:00, TO is 06:00, and the question is "is 23:30 inside or outside this period?", which mathematically is outside, but clock-wise is inside).

SELECT 
    ( TIME_WORKING_FROM_HOUR*60+TIME_WORKING_FROM_MINUTE < 
    TIME_WORKING_TO_HOUR*60+TIME_WORKING_TO_MINUTE )
    XOR NOT
    (
    TIME_RIGHT_NOW_HOUR*60+TIME_RIGHT_NOW_MINUTE BETWEEN
        TIME_WORKING_FROM_HOUR*60+TIME_WORKING_FROM_MINUTE
        AND
        TIME_WORKING_TO_HOUR*60+TIME_WORKING_TO_MINUTE
    )

Test

CREATE TABLE test ( TIME_RIGHT_NOW_HOUR integer, TIME_RIGHT_NOW_MINUTE integer, 
TIME_WORKING_FROM_HOUR integer, TIME_WORKING_FROM_MINUTE integer,
TIME_WORKING_TO_HOUR integer, TIME_WORKING_TO_MINUTE integer );

INSERT INTO test VALUES 
    ( 7, 30, 8, 00, 17, 00 ), 
    ( 12, 30, 8, 00, 17, 00 ),
    ( 4, 30, 22, 00, 06, 00 ),
    ( 0, 0, 22, 00, 06, 00 ),
    ( 22, 0, 6, 0, 6, 5 );

SELECT  TIME_WORKING_FROM_HOUR AS FROM_H,
    TIME_WORKING_FROM_MINUTE AS FROM_M,
    TIME_WORKING_TO_HOUR AS TO_H,
    TIME_WORKING_TO_MINUTE AS TO_M,
    TIME_RIGHT_NOW_HOUR AS NOW_H,
    TIME_RIGHT_NOW_MINUTE AS NOW_M,
    ( TIME_WORKING_FROM_HOUR*60+TIME_WORKING_FROM_MINUTE < 
    TIME_WORKING_TO_HOUR*60+TIME_WORKING_TO_MINUTE )
    XOR NOT
    (
    TIME_RIGHT_NOW_HOUR*60+TIME_RIGHT_NOW_MINUTE BETWEEN
        TIME_WORKING_FROM_HOUR*60+TIME_WORKING_FROM_MINUTE
        AND
        TIME_WORKING_TO_HOUR*60+TIME_WORKING_TO_MINUTE
    ) AS in_orario FROM test;

Result:

+--------+--------+------+------+-------+-------+-----------+
| FROM_H | FROM_M | TO_H | TO_M | NOW_H | NOW_M | in_orario |
+--------+--------+------+------+-------+-------+-----------+
|      8 |      0 |   17 |    0 |     7 |    30 |         0 |
|      8 |      0 |   17 |    0 |    12 |    30 |         1 |
|     22 |      0 |    6 |    0 |     4 |    30 |         1 |
|     22 |      0 |    6 |    0 |     0 |     0 |         1 |
|      6 |      0 |    6 |    5 |    22 |     0 |         0 |
+--------+--------+------+------+-------+-------+-----------+
5 rows in set (0.00 sec)

In this SQLfiddle, the condition has been moved into the WHERE.

网友答案:

This will work for both 5:00 -> 23:00 and 23:00 -> 5:00 ( NEXT day)

    WHERE
    (
            -- CASE: 5:00 -> 23:00
            ( 
                    TIME_WORKING_FROM_HOUR + TIME_WORKING_FROM_MINUTE / 100.00 < TIME_WORKING_TO_HOUR + TIME_WORKING_TO_MINUTE / 100.00
            )
            AND
            (
                    (
                            TIME_WORKING_FROM_HOUR + TIME_WORKING_FROM_MINUTE / 100.00 <= TIME_RIGHT_NOW_HOUR + TIME_RIGHT_NOW_MINUTE / 100.00
                    )
                    AND 
                    (
                            TIME_WORKING_TO_HOUR + TIME_WORKING_TO_MINUTE / 100.00 >= TIME_RIGHT_NOW_HOUR + TIME_RIGHT_NOW_MINUTE / 100.00
                    )
            )
    )
    OR
    (
            -- CASE: 23:00 -> 5:00 of Next day
            ( 
                    TIME_WORKING_FROM_HOUR + TIME_WORKING_FROM_MINUTE / 100.00 >= TIME_WORKING_TO_HOUR + TIME_WORKING_TO_MINUTE / 100.00
            )
            AND
            (
                (
                    TIME_RIGHT_NOW_HOUR + TIME_RIGHT_NOW_MINUTE / 100.00 >= TIME_WORKING_FROM_HOUR + TIME_WORKING_FROM_MINUTE / 100.00
                )
                OR
                (
                    TIME_RIGHT_NOW_HOUR + TIME_RIGHT_NOW_MINUTE / 100.00 <= TIME_WORKING_TO_HOUR + TIME_WORKING_TO_MINUTE / 100.00
                )
            )
    )
相关阅读:
Top