Set Timezone in PHP and MySQL

Set timezone in PHP and MySQL

In PHP:

<?php
define('TIMEZONE', 'Europe/Paris');
date_default_timezone_set(TIMEZONE);

For MySQL:

<?php
$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
$db = new PDO('mysql:host=localhost;dbname=test', 'dbuser', 'dbpassword');
$db->exec("SET time_zone='$offset';");

The PHP and MySQL timezones are now synchronized within your application. No need to go for php.ini or MySQL console!

This is from this article on SitePoint.

Set timezone for MYSQL NOW()

Your question is not clear but if you want to change date and time value from UTC to your time zone, you can use

CONVERT_TZ(date, 'UTC',  'Asia/Kolkata')

You can select the UTC date and time value in your time zone:

SELECT CONVERT_TZ(date, 'UTC',  'Asia/Kolkata') AS local FROM table

If CONVERT_TZ function does not work, you need to install time zones (time zones should be already stored in MySQL). One note is that you need to store UTC value in your database and later convert to whatever time zone you want.

How to change time zone in MYSQL database?

At the beginning of your script, just after connection to MySQL, execute this query:

SET SESSION time_zone = 'Asia/Kolkata';

Documentation: MySQL Server Time Zone Support

if you get an error when executing the query:

ERROR 1298 (HY000): Unknown or incorrect time zone: 'Asia/Kolkata'

then it means the timezone data is not loaded in your MySQL server. You may do so by following the explanations given in the chapter Populating the Time Zone Tables or you may try using numerical values instead of named timezones:

SET SESSION time_zone = "+5:30";

How to handle MYSQL NOW() in different timezones?

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());";

Having Timezone problems with PHP and MySQL

The trick here is to know what column type date_last_active is. I've had enough experience with TIMESTAMP and DATETIME to know that one translates (and honors) the MySQL time_zone session variable, the other doesn't.

mysql> SET SESSION time_zone = 'America/New_York';
Query OK, 0 rows affected (0.00 sec)

mysql> create table timetest ( dt datetime NULL, ts timestamp NOT NULL DEFAULT 0 );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO timetest ( dt, ts ) VALUES ( UTC_TIMESTAMP(), UTC_TIMESTAMP() );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timetest ( dt, ts ) VALUES ( NOW(), NOW() );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM timetest;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2009-06-27 17:53:51 | 2009-06-27 17:53:51 | 
| 2009-06-27 13:53:54 | 2009-06-27 13:53:54 | 
+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> set session time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM timetest;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2009-06-27 17:53:51 | 2009-06-27 21:53:51 | 
| 2009-06-27 13:53:54 | 2009-06-27 17:53:54 | 
+---------------------+---------------------+
2 rows in set (0.00 sec)

So, here is what I do:

• When inserting into the database, use UTC in PHP and in MySQL. In my.cnf I have: time_zone=UTC to avoid any issues in MySQL, and in PHP I date_default_timezone_set('UTC').
• Use UTC_TIMESTAMP() instead of NOW() - one is UTC, one uses the local (session) time zone. If you're following the first point, above, use UTC_TIMESTAMP().
• When selecting to display to the user, set time_zone='local_time_zone' before displaying anything.
• If you have to display stuff, then update, make sure to bracket your calls with the appropriate timezone changes in PHP and MySQL.

Hopefully that's enough to figure out how to approach this. Let me know if you have further questions.

---

Related Topics