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.

1. run in MYSQL : SELECT @@system_time_zone;
2. Add this to the first line in PHP code, date_default_timezone_set(‘US/Eastern’);
3. Replace ‘US/Eastern’ in Step 2, with the timezone value you got from Step 1.
4. In codeigniter, you can place Step 2 line in config.php
5. 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/

}