MySQL timezone change?

Question

How do I change my timezone which is currently in UTC to GMT +1, what is the correct line and do i just enter it in phpMyAdmin SQL execution?

My host just gave me this link http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html and went off so I'm kinda lost thanks

Ben D · Answer 1 · 2012-11-15T07:14:57.380

44

The easiest way to do this, as noted by Umar is, for example

mysql> SET GLOBAL time_zone = 'America/New_York';

Using the named timezone is important for timezone that has a daylights saving adjustment. However, for some linux builds you may get the following response:

#1298 - Unknown or incorrect time zone

If you're seeing this, you may need to run a tzinfo_to_sql translation... it's easy to do, but not obvious. From the linux command line type in:

mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql -u root mysql -p

Provide your root password (MySQL root, not Linux root) and it will load any definitions in your zoneinfo into mysql. You can then go back and run your

mysql> SET GLOBAL time_zone = timezone;

edited Nov 15 '12 at 07:14

answered May 21 '12 at 14:15

Ben D

14,321
3
45
59

Worked perfectly on centOS 7.1 – ryryan Dec 15 '15 at 19:28
2

What about users on windows getting "#1298 - Unknown or incorrect time zone"? – Wesley Smith Nov 08 '16 at 04:08

score 39 · Accepted Answer · answered Aug 10 '10 at 18:02

39

issue the command:

SET time_zone = 'America/New_York';

(Or whatever time zone GMT+1 is.: http://www.php.net/manual/en/timezones.php)

This is the command to set the MySQL timezone for an individual client, assuming that your clients are spread accross multiple time zones.

This command should be executed before every SQL command involving dates. If your queries go thru a class, then this is easy to implement.

answered Aug 10 '10 at 18:02

Bryon Hibbetts

430
5
2

3

This will automatically change the date/time obtained from all the TIMESTAMP fields, but not the DATETIME fields. – Timo Huovinen Sep 26 '13 at 06:29
It will not change the values stored in DATETIME or TIMESTAMP fields. – Timo Huovinen Sep 26 '13 at 06:36
14

it doesn't work for me. I I get a "Error Code: 1298. Unknown or incorrect time zone: 'America/New_York' " – user1028741 Sep 17 '14 at 14:01

score 26 · Answer 3 · answered Apr 20 '12 at 18:55

While Bryon's answer is helpful, I'd just add that his link is for PHP timezone names, which are not the same as MySQL timezone names.

If you want to set your timezone for an individual session to GMT+1 (UTC+1 to be precise) just use the string '+01:00' in that command. I.e.:

SET time_zone = '+01:00';

To see what timezone your MySQL session is using, just execute this:

SELECT @@global.time_zone, @@session.time_zone;

This is a great reference with more details: MySQL 5.5 Reference on Time Zones

SELECT @@global.time_zone, @@session.time_zone,@@global.system_time_zone; — zzapper, Nov 01 '12 at 11:25

score 6 · Answer 4 · answered Feb 02 '13 at 06:26

Here is how to synchronize PHP (>=5.3) and MySQL timezones per session and user settings. Put this where it runs when you need set and synchronized timezones.

date_default_timezone_set($my_timezone);
$n = new \DateTime();
$h = $n->getOffset()/3600;
$i = 60*($h-floor($h));
$offset = sprintf('%+d:%02d', $h, $i);
$this->db->query("SET time_zone='$offset'");

Where $my_timezone is one in the list of PHP timezones: http://www.php.net/manual/en/timezones.php

The PHP timezone has to be converted into the hour and minute offset for MySQL. That's what lines 1-4 do.

Can't you get the same with = date('P'); ?> – supersan Dec 26 '15 at 17:02 — supersan, Dec 26 '15 at 17:02

score 4 · Answer 5 · edited Sep 30 '12 at 09:05

4

If you have the SUPER privilege, you can set the global server time zone value at runtime with this statement:

mysql> SET GLOBAL time_zone = timezone;

edited Sep 30 '12 at 09:05

Akash KC

16,057
6
39
59

answered Aug 30 '10 at 09:35

Umar Adil

5,128
6
29
47

1

And what if there is no superadmin privilege? – Rolen Koh Jul 23 '14 at 07:19
just `SET time_zone = timezone;` – Thomas Orlita Jan 01 '16 at 00:11

score 1 · Answer 6 · answered Sep 02 '16 at 11:50

1

If SET time_zone or SET GLOBAL time_zone does not work, you can change as below:

Change timezone system, example: ubuntu... $ sudo dpkg-reconfigure tzdata
Restart the server or you can restart apache2 and mysql (/etc/init.d/mysql restart)

answered Sep 02 '16 at 11:50

Arjun s

81
6

score 1 · Answer 7 · answered Sep 05 '18 at 02:20

This works fine

<?php
      $con=mysqli_connect("localhost","my_user","my_password","my_db");
      $con->query("SET GLOBAL time_zone = 'Asia/Calcutta'");
      $con->query("SET time_zone = '+05:30'");
      $con->query("SET @@session.time_zone = '+05:30'");
?>

MySQL timezone change?

7 Answers7

Linked

Related