CODEWITHSUNDEEP
mysqldatabasetimezone
sigi
sigi

Reputation: 153

Setting default-time-zone on MySql Server via PhPMyAdmin

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

Upvotes: 12

Views: 47630

Answers (3)

Matty J
Matty J

Reputation: 3156

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

Upvotes: 8

AceStaCy
AceStaCy

Reputation: 1

<?php
date_default_timezone_set('UTC'); //define local time

$date=date('l jS \of F Y h:i:s A'); //type of time shown

$conn=mysql_connect("localhost","root","") or die('Could not connect!'); //your database connection here

$db_selected = mysql_select_db('databasename', $conn); //select db

$result=mysql_query("INSERT INTO table (date) VALUES ('$date')", $conn); 
?>

Simply sent the time as VARCHAR into db hope it helps and sorry for syntax errors (if there are any).

Upvotes: -1

Wh1T3h4Ck5
Wh1T3h4Ck5

Reputation: 8509

For shared hosting, you have to ask support-guys to help you and change default time zone for you? I had similar problem with Arcor hosting-provider, called them and they fixed it. Before that, I found temporary solution in date_default_timezone_set() from PHP code. Probably the best solution is to ask someone who has privilege to change that parameter.

Upvotes: 1

Related Questions