12

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

Community
  • 1
  • 1
sigi
  • 153
  • 1
  • 1
  • 9
  • 1
    Have you tried SET SESSION time_zone = '+0:00' or SET GLOBAL time_zone = '+0:00'? – dseibert Mar 21 '11 at 18:35
  • thanks for the tip 'client09', but unfortunatelly I get "#1227 - Access denied; you need the SUPER privilege for this operation", not enough privileges on shared hosting. – sigi Mar 29 '11 at 08:25

3 Answers3

8

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

Community
  • 1
  • 1
Matty J
  • 3,116
  • 32
  • 31
1

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.

Wh1T3h4Ck5
  • 8,399
  • 9
  • 59
  • 79
  • I think in my shared hosting situation this is probably the wisest thing to do. I contacted them, and unfortunatelly they declined to change the default time zone for me. I found this strange, but I didnt follow it up, since I just then decided to convert all my times to UTC on server side and just work with UTC instead, this seems to be working. – sigi Mar 29 '11 at 08:28
-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).

Tiago Sippert
  • 1,324
  • 7
  • 24
  • 33
AceStaCy
  • 1