KP Joy KP Joy - 4 months ago 23
SQL Question

How to change time zone in MYSQL database?

I have a table

myTable
in my
MYSQL database
in which there is a column called
INSERT_DATE
which is
datetime
type and having a default value of
CURRENT_TIMESTAMP
.

While making an insert statement, this column automatically picks the current time from my system.

This is working very well on my localhost. But when I hosted my website to a server placed somewhere else in some different timezone, its picking time from that time zone which is wrong. I want to set timezone to
GMT + 5:30
('Asia/Kolkata')
. How can I achieve this in
MYSQL
?

I am trying to change timezone while connecting to database, but it is not working. Below is my code:

function connect_database()
{
$con = mysqli_connect("servername", "username", "password", "dbname");
if (!$con)
{
$con = "";
echo("Database connection failed: " . mysqli_connect_error());
}
mysqli_query($con, "SET SESSION time_zone = 'Asia/Kolkata'");
return $con;
}

Answer

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