CODEWITHSUNDEEP
phpmysqltimezoneprepared-statement
heron
heron

Reputation: 3661

How to change NOW() timezone

I'm using NOW() in sql query. I want to change it to another timezone. is that possible?

Tried this function 

class common {

    public function getTime() {
        $date = new DateTime();
        $date->setTimezone(new DateTimeZone('Europe/Paris'));
        return $date->format('Y-m-d H:i:s');
    }

}

And getting following error

Catchable fatal error:  Object of class common could not be converted to string in line below

        $stmt = $this->db->prepare("INSERT INTO `items` 
      (`refno`, `color`, `size`, `qt`, `stackno`, `notes`, `price`, `add_date`)
      VALUES (?, ?, ?, ?, ?, ?, ?, $this->common->getTime())") or die($db->error);

What did I do wrong?

Upvotes: 3

Views: 373

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562573

The error is actually a PHP error, not an SQL error. Trying to evaluate complex variable expansion expressions inside string interpolation doesn't work the way you're doing it.

Try putting the object inside {} braces:

$stmt = $this->db->prepare("INSERT INTO `items` 
      (`refno`, `color`, `size`, `qt`, `stackno`, `notes`, `price`, `add_date`)
      VALUES (?, ?, ?, ?, ?, ?, ?, {$this->common->getTime()})") or die($db->error);

See the subheading Complex (curly) syntax in manual page http://php.net/manual/en/language.types.string.php

Re your comment:

now getting this error on the same line: Trying to get property of non-object

You haven't shown how you are setting $this->common. Given the syntax you've shown, common needs to be an object instance of your class common. I'm guessing you're trying to call the getTime() function without instantiating the class. If you want to use static methods of a class, you'll have to do it this way:

class common {

    public static function getTime() {
        $date = new DateTime();
        $date->setTimezone(new DateTimeZone('Europe/Paris'));
        return $date->format('Y-m-d H:i:s');
    }

}

$stmt = $this->db->prepare("INSERT INTO `items` 
      (`refno`, `color`, `size`, `qt`, `stackno`, `notes`, `price`, `add_date`)
      VALUES (?, ?, ?, ?, ?, ?, ?, " . common::getTime() . ")") or die($db->error);

If you are unfamiliar with the difference between classes and objects, and the uses of static, then you need to do some reading, e.g. Object-Oriented PHP.

Upvotes: 3

thenetimp
thenetimp

Reputation: 9820

You would use this sql call.

SET time_zone = timezone;

Per the mysql documentation.

http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Upvotes: 0

Related Questions