Set time zone in php to be same with now() in mysql

I have this code where I connect to database and the code is working properly. However the now() function of MySQL is out of sync with PHP set default time zone function. There is 8 hour difference between the now() in mysql and the time zoned $datenow= new DateTime() in php. I am comparing the PHP date with MySQL now. Therefore, I wanted to set the database time zone as well.

    private $host="9999999";
    private $user="99999999";
    private $pwd="8888888";
    private $dbName="88888888";

    protected function connect(){
        $dsn='mysql:host='.$this->host.';dbname='.$this->dbName;
        $pdo = new PDO($dsn, $this->user, $this->pwd);
    
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    
        return $pdo;
    
    
    



I wanted to set the database time upon connection. I have this code I wanted to adapt it to mine. This is the code below. I came across this code and I thought it could be of help but I am finding it hard to implement with my already working code.
CODE TO ADAPT

$now = new DateTime();
$mins = $now->getOffset() / 60;
$sgn = ($mins < 0 ? -1 : 1);
$mins = abs($mins);
$hrs = floor($mins / 60);
$mins -= $hrs * 60;
$offset = sprintf('%+d:%02d', $hrs*$sgn, $mins);

//Your DB Connection - sample
$pdo = new PDO('mysql:host=localhost;dbname=test', 'dbuser', 'dbpassword');
$pdo->exec("SET time_zone='$offset';");

Please, how do I implement the two codes together

Link to the code I am adapting is this.

FULL CODE 1.(index.php)

$datedefault=date_default_timezone_set($zone['continent'].'/'.$zone['city']);

2.(dbh.class.php)

class Dbh{
    private $host="22222";
    private $user="777777";
    private $pwd="6666";
    private $dbName="55555";
    
    protected function connect(){
$now = new DateTime();
$mins = $now->getOffset() / 60;
$sgn = ($mins < 0 ? -1 : 1);
$mins = abs($mins);
$hrs = floor($mins / 60);
$mins -= $hrs * 60;
$offset = sprintf('%+d:%02d', $hrs*$sgn, $mins);
        $dsn='mysql:host='.$this->host.';dbname='.$this->dbName;
        $pdo = new PDO($dsn, $this->user, $this->pwd);
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$pdo->exec("SET time_zone='$offset';");     
return $pdo;
    }
}

This is where I am using the class 3. (user.class.php)

class Users extends Dbh{

    
        protected function getByandbye($email, $phone){
        $sql="SELECT * FROM user WHERE `email`=? OR CONCAT (`pre`,`phone`)=?";
        $stmt= $this->connect()->prepare($sql);
        $stmt->execute([$email, $phone]);
        $user=$stmt->fetch();
        return $user;

    }
}

The php ‘P’ date format -

P Difference to Greenwich time (GMT) with colon between hours and minutes (added in PHP 5.1.3) Example: +02:00 

Directly produces the value needed for the SET time_zone= ‘…’ query.

Thanks. Kindly help me rewrite this class. class Dbh{
private $host=“22222”;
private $user=“777777”;
private $pwd=“6666”;
private $dbName=“55555”;

protected function connect(){

$now = new DateTime();
$mins = $now->getOffset() / 60;
$sgn = ($mins < 0 ? -1 : 1);
$mins = abs($mins);
$hrs = floor($mins / 60);
$mins -= $hrs * 60;
$offset = sprintf(’%+d:%02d’, $hrs*$sgn, $mins);
$dsn=‘mysql:host=’.$this->host.’;dbname=’.$this->dbName;
$pdo = new PDO($dsn, $this->user, $this->pwd);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$pdo->exec(“SET time_zone=’$offset’;”);
return $pdo;
}
}

My response deals only with how you will “set the database time upon connection”
Question is why you want to set it.
Do you want to set the time for database administrator use later or for the client use later?
Understand this: date and time are determined by where your server is located and not where you and your client(s) are

If you want to store date in a database for use by a database administrator only, consider any of the followings php script (using my current experience):
1.

time() // 1607670724

If you or any administrator will work on your data later, you will need to convert time value to actual time as it was recorded on the database. This is easy but a draw back

Best are:
2.

date(“M-d-Y H:i:s”) // Dec-11-2020 08:12:04

gmdate(“M-d-Y H:i:s”) // Dec-11-2020 07:12:04

A database administrator can work on these just fine.

What if the date/time data you are saving on the database is for the client consumption later?
The best way to solve this is a simple javascript to send actual client date/time to the server side. Anyone of the following will be OK:
JS:
1.

var date = new Date(); // Fri Dec 11 2020 02:12:04 GMT-0500 (Eastern Standard Time)

var date = new Date().toLocaleDateString(); // “12/11/2020”

Now add jQuery AJAX script to send any of these to server side:

$.post(‘time.php’, {date: date}, function(data) {
// some things
});

And (part of) your php script (filename time.php):

// some things
$date = $_POST["date"]  // must do all security tests to eliminate any intruder
// some things
Sponsor our Newsletter | Privacy Policy | Terms of Service