How to check server timezone

Question

I want to know what is the time zone that is currently set in the MySQL server. I do not have administrator rights to the computer I am using so I've tried the method by checking the registry.

I am doing a table with a timestamp column and I noticed the time stamped is different than the one on my computer's time. Is there any reason for this? How do I check what timezone it is on the MySQL server? How do I change it to match my local/computer's time?

Answer 1

Have a look at the system_time_zone system variable.

Answer 2

This may help:

http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

You can set the system time zone for MySQL Server at startup with the --timezone=timezone_name option to mysqld_safe. You can also set it by setting the TZ environment variable before you start mysqld. The permissible values for --timezone or TZ are system dependent. Consult your operating system documentation to see what values are acceptable.

Answer 3

You can convert a given timestamp to UTC (or any other TZ you want) with CONVERT_TZ

SELECT CONVERT_TZ(NOW(),@@session.time_zone,'GMT');

Note that I use NOW() as simple demonstration, you would put in the timestamp you wanted to convert.

By the same token, you could convert a timestamp in your local TZ to the system

SELECT CONVERT_TZ($timestamp,'Your Time Zone' @@session.time_zone);

Answer 4

You can set the timezone (if you know your offset) for the session by using

    set session time_zone = '+00:00';

and to revert to the system default

    set session time_zone 'SYSTEM';

Answer 5

1. In an SQL timestamp column, SQL automatically converts the time to UTC before storing it, using the session's current time offset. It will be the machine's time offset unless you change it (3). Depending on your server's settings (sql.ini), it may or may not always concert back to the expect timezone. This probably explains the time discrepancy.

2. To get the current timezone offset, try executing

   SELECT @@session.time_zone;
   

3. To manually override the SQL timezone for the rest of a particular session, execute the following, replacing 00:00 with your desired offset:

   SET @@session.time_zone = "+00:00";
   

Answer 6

To check your shared server

<?php
  echo date_default_timezone_get();
?>

To change

<?php
    date_default_timezone_set("Africa/Addis_Ababa");
    echo date_default_timezone_get();
?>