duan5991518
2016-07-11 07:24 阅读 180
已采纳

如何在MYSQL数据库中更改时区?

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;
} 
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    douleng3463 douleng3463 2016-07-11 07:28

    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";
    
    点赞 评论 复制链接分享
  • dongxian8272 dongxian8272 2016-07-11 07:31

    for session scope

    set time_zone = "+5:30"
    

    global scope

    set global time_zone = "+5:30"
    

    If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement).

    点赞 评论 复制链接分享

相关推荐