How to handle MYSQL NOW() in different timezones?

Question

I am using PHP and MySQL. I have following insert query which is working fine.

$query="INSERT INTO mytable (user_id, user_datetime) VALUES ".
       "(".$_SESSION["user_id"].", NOW());";

Problem is that:

1) When I was testing on my localhost, NOW() was picking date and time from my system. That was good, no problem!!!

2) Now I have hosted my site to a web hosting server which is anywhere in Canada and I am in India. Now if any insert is happening, it is storing the time of Canada not of India. This is the problem. How can I get rid of this. How do I save Indian time in my database?

Answer 1

You have two solutions: use the PHP approach or the MySQL approach. (your question title is misleading, it's not a PHP NOW() but a MySQL NOW()).

With PHP, you can use the date_default_timezone_set function for this. It will set the default timezone for your PHP script.

Just remember to put it near the top so it applied to what you are doing.

BUT this will set the timezone for all PHP function, not your MySQL database NOW() command.

To modify the timezone of MySQL, you have to use SET time_zone = timezonename;. See this SO thread for setting MySQL timezone or the official documentation.

Example:

<?php
date_default_timezone_set('Asia/Calcutta');
$query = "INSERT INTO mytable (user_id, user_datetime) VALUES ".
   "(".$_SESSION["user_id"].", ".date('Y-m-d H:i:s').");";

// OR

$query = "SET time_zone = 'Asia/Calcutta';"; // this will persist only for the current connection
$query .= "INSERT INTO mytable (user_id, user_datetime) VALUES ".
           "(".$_SESSION["user_id"].", NOW());";