How to convert now() into GMT +8 before insert into mysql

Question

This is my sql to insert current time when a request password is made.

$stmt2 = $pdo->prepare('UPDATE authsessions SET reset_req=now(), reset_req_uuid=:reset_uuid WHERE useruuid=:user_id');

This causes a confusion because it follows the server's timezone to insert the time.

Therefore in my localhost, it inserts Asia/Kuala Lumpur time as I declared it in the page.But in the amazon server which I believe GMT it inserts accordingly. But I want to enforce it to insert time as in Asia/Kuala Lumpur.It's because the site meant to be used within Malaysia and there's no point using GMT time.

How do I make now() to convert to GMT 8+ before inserting, please?

possible duplicate of [How to set time zone of mysql?](http://stackoverflow.com/q/930900) - Also I'd wager to say it's not fruitful to store timezone-specific dates to begin with. Use localization for inputting/outputting dates, not for the persistence layer. — mario, Jul 04 '15 at 12:01

score 2 · Answer 1 · answered Jul 04 '15 at 12:05

2

You can convert the timezone in the statement using CONVERT_TZ (see MySQL manual).

answered Jul 04 '15 at 12:05

GluePear

7,244
20
67
120

score 0 · Answer 2 · edited Jul 04 '15 at 13:06

First of all it depends on the MySQL field you are using. If you're using TIMESTAMP field it will be stored as UTC timestamp in the database. This is the most easy one, because your php connection will use the timezone of your local server. In this way you don't have to convert any timezone issues.

If you are using DATETIME field it will store the time as it is. Mysql NOW() will use the database timezone setting and you can override the setting when you connect with:

SET time_zone = '+08:00';

The other option you have is to handle the Timezone from PHP. Example:

$now = new DateTime('NOW', new DateTimeZone('Asia/Kuala_Lumpur'));
$stmt2 = $pdo->prepare('UPDATE authsessions SET reset_req=:now, reset_req_uuid=:reset_uuid WHERE useruuid=:user_id');
$stmt2->bindParam(':now', $now->format('Y-m-d H:i:s');

How to convert now() into GMT +8 before insert into mysql

2 Answers2