如何确定给定的开始和结束时间是否已在php中过期

Question

Please i want to create a php code to check if time has expired or still active based on the start date and end time that users specified. Below is the database structure and sample php code which i tried.

game_config

game_id | start_date | start_t | start_h | end_time | end_h | status  
--------|------------|---------|---------|----------|-------|---------
100     | 03/26/2018 |  10:45  | PM      |   12:30  |  AM   | 0 
101     | 03/27/2018 |  09:23  | AM      |   11:10  |  AM   | 0 

Php code sample

<?php
    $conf_execution_date = date('m/d/Y'); /*Current date*/
    $conf_execution_meridiem = date('A'); /*Current meridiem*/
    $conf_execution_timer = date('h:m'); /*Current time*/
    $conf_execution_proccess = false;    /*Proceed or not*/

    $conf_handler = $appconn->prepare("
        SELECT * FROM game_config WHERE game_id = :game_id AND start_date = :start_dateAND start_t = :start_t AND status = 0 LIMIT 1
    ");

    $conf_handler->bindParam(":game_id", 100);
    $conf_handler->bindParam(":start_date", $conf_execution_date);
    $conf_handler->bindParam(":start_t", $conf_execution_meridiem); 
    $conf_handler->execute();
    $appconf = $conf_handler->fetch(PDO::FETCH_OBJ);

    if($appconf){
        $config_start_time = strtotime($appconf->start_t);
        $config_end_time = strtotime($appconf->end_time);
        $current_time = strtotime($conf_execution_timer);

        if($appconf->start_h == $conf_execution_meridiem){ /*AM-PM*/
            if($config_start_time >= $current_time){ /*Check if the start time is now o still active*/
                $conf_execution_proccess = true;
            }
        }

        if($appconf->end_h == $conf_execution_meridiem){ /*AM-PM*/
            if($config_end_time >= $current_time){ /*Check if the time has ended*/
                $conf_execution_proccess = false;
            }
        }

        if($conf_execution_proccess == true){
            echo 'YES THE GAME IS STILL ACTIVE';
        }else{
            echo "NO GAME HAS ENDED";
        }
    }
?>

Answer 1

Assuming your start_date column is type VARCHAR():

Assuming your end_time is always later in the day than your start_time:

You can use this sort of query to get a starting and ending DATETIME value for each row of your table. It glues together the date, the time, and the AM/PM fields and converts them to DATETIME. (http://sqlfiddle.com/#!9/2d84ea/1/0)

SELECT game_id, 
       STR_TO_DATE(CONCAT(start_date,'-',start_t,start_h), '%m/%d/%Y-%h:%i%p') start_ts,
       STR_TO_DATE(CONCAT(start_date,'-',end_time,end_h), '%m/%d/%Y-%h:%i%p') end_ts,
       status    
  FROM game_config

Then you can compare those start_ts and end_ts values to NOW() by appending this line to your query

Then you can fill out your query with the criteria you need, giving this query.

SELECT game_id, 
       STR_TO_DATE(CONCAT(start_date,'-',start_t,start_h), '%m/%d/%Y-%h:%i%p') start_ts,
       STR_TO_DATE(CONCAT(start_date,'-',end_time,end_h), '%m/%d/%Y-%h:%i%p') end_ts,
       status    
  FROM game_config
 WHERE game_id = : gameid AND status = 0
HAVING start_ts <= NOW() and NOW() < end_ts
 LIMIT 1

But, your start and end times sometimes span midnight, which means your end_ts needs to be on the next day. That makes your date-checking logic quite a bit trickier. You'll have to do some sort of conditional expression in which you do + INTERVAL 1 DAY to your end_ts if it is before your start_ts.

You will be much better off (@eggyal pointed this out in his comment) if you put the following columns in your table.

game_id   int
start_ts  timestamp of start
end_ts    timestamp of end
status    int

Then you don't have to worry about all the stuff about making a proper timestamp from three different columns, and you don't have to do anything special when the start and end times span midnight.

And, this will work intuitively no matter what timezone your users are in.