How to deal with time mismatch in MySQL records when date('Y-m-d H:i:s') in PHP does not match MySQL's server time?

Try setting the locale in PHP for the current application in code.

run in MYSQL : SELECT @@system_time_zone;
Add this to the first line in PHP code, date_default_timezone_set(‘US/Eastern’);
Replace ‘US/Eastern’ in Step 2, with the timezone value you got from Step 1.
In codeigniter, you can place Step 2 line in config.php
If there are errors, use the right timezones here:
1. https://www.php.net/manual/en/timezones.america.php
2. date_default_timezone_set(‘America/New_York’); used for aspa and webhooks.

function getCurrentDatabaseTime() {
    $ci =& get_instance(); 
    $ci->load->database();
     
    $query = $ci->db->query('SELECT NOW() as `current_time`;');
    // if exist
    $results = array();
    if ($query->num_rows() > 0) {
        foreach ($query->result_array() as $row)
            {
                return $row;
            }            
    }
         
    return FALSE;   
}
 
function getCurrentDatabaseTimezone() {
    //SELECT @@system_time_zone
    $ci =& get_instance(); 
    $ci->load->database();
     
    $query = $ci->db->query('SELECT @@system_time_zone as `current_time_zone`;');
    // if exist
    $results = array();
    if ($query->num_rows() > 0) {
        foreach ($query->result_array() as $row)
            {
                return $row;
            }            
    }
         
    return FALSE;   
}
 
function getPacificTime($timeString) {
// Right now it's about four minutes before 1 PM, PST.
$pst = new DateTimeZone('America/Los_Angeles');
//$three_hours_ago = new DateTime('-3 hours', $pst); // first argument uses strtotime parsing
$now = new DateTime($timeString, $pst); // first argument uses strtotime parsing
//echo $three_hours_ago->format('Y-m-d H:i:s'); // "2010-06-15 09:56:36"
return $now->format('Y-m-d H:i:s');  
}
 
 
// inside a controller , index()
 
public function index() {
        $this->load->helper('common');
        $current_timezone_array = getCurrentDatabaseTimezone();
        $current_time_array = getCurrentDatabaseTime();
        echo 'Current Database Timezone: '.$current_timezone_array['current_time_zone'];
        echo '<hr>';
        echo 'Current Database Time: '.$current_time_array['current_time'];
        echo '<hr>';
        echo 'Current PHP Server Time: '.date('Y-m-d H:i:s');
        echo '<hr>';
        echo 'Current Pacific Time: '.getPacificTime('now');
        if ($current_time_array['current_time'] != date('Y-m-d H:i:s')) echo '<hr><font color=red>Possible time issues! Database time and server time mismatch!</font><hr>';
        else echo '<hr><font color=green>Good! Database time and server time matches!</font><hr>';
         
        // application should have mysql time and server time matching for things to work properly.
        // see: https://apps.badjoerichards.com/apps/developerhack/deal-time-mismtach-mysql-records-datey-m-d-php-not-match-mysqls-server-time/
 
}

How to deal with time mismatch in MySQL records when date(‘Y-m-d H:i:s’) in PHP does not match MySQL’s server time?

Leave a Reply Cancel reply