database - Setting default-time-zone on MySql Server via PhPMyAdmin

Question

I have a shared hosting mySql instance which has it's system_time_zone set to Pacific Standard Time and it's time_zone variable set to System, hence effectively it's running on Pacific Standard Time.

i.e. I've run the following command to find this out:

SELECT version( ) , @@time_zone , @@system_time_zone , NOW( ) , UTC_TIMESTAMP( )

I would like to change the default mySql database / local mySql DB time-zone to GMT/UTC time. I tried to run, SET time_zone = '+0:00', and this does execute successfully!

However, this does not seem to affect the time_zone variable, when I check the state of @@time_zone. I've looked at another post dealing with similar issue How to set MySQL to use GMT in Windows and Linux and I also checked the MySql documentation, with little progress. Since I am on a shared-hosting solution, I have limited access and I don't have access to more than what my PhPMyAdmin mySql functionality has on offer.

I wonder if there is any way to change the default_time-zone from within an SQL query, or do I need to fall back to the command line (to which I don't have access to, unfortunately).

Thanks for your help and advice,

Martin

Answer 1

In short, MySQL actually stores 'datetime' data type fields internally as UTC.

However, PhpMyAdmin shows you the dates using the server default time, hence your confusion.

For example, try adding this line before your SQL statement in PhpMyAdmin:

SET @@session.time_zone='+00:00';
SELECT * FROM MY_TABLE

See the MySQL documentation for further details, or the answer in this post: How to correctly set mysql timezone

Cheers Matt