7

According to this question on how to set the time_zone in mysql and this timezone list, I thougth this should work:

SET time_zone = 'Europe/Madrid';

But this is what sqlBuddy logs:

The following errors were reported:Unknown or incorrect time zone: 'Europe/Madrid'

Community
  • 1
  • 1
Toni Michel Caubet
  • 19,333
  • 56
  • 202
  • 378

2 Answers2

5

Ensure that the time zone information tables have been populated. From mysql:

The value can be given as a named time zone, such as 'Europe/Helsinki', 'US/Eastern', or 'MET'. Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.

Although personally I prefer storing all dates in UTC. I find it makes reasoning far simpler particularly when daylight savings time is introduced.

Rich O'Kelly
  • 41,274
  • 9
  • 83
  • 114
  • 2
    The tables have to be loaded; the mechanism depends on the OS. The instructions are [here](http://dev.mysql.com/doc/refman//5.5/en/time-zone-support.html). On most Linux distros, this should work: `mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql` – Mark Reed Jul 08 '12 at 22:55
  • @ToniMichelCaubet The link to the mysql documentation I provided contains this information. As MarkReed points out, it is OS dependent. Which OS is your database on? – Rich O'Kelly Jul 09 '12 at 08:48
  • Debian. Thanks i'll try your sippet – Toni Michel Caubet Jul 09 '12 at 15:28
  • You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql' at line 1 – Toni Michel Caubet Jul 09 '12 at 15:29
  • @ToniMichelCaubet: That's not a SQL statement, it's a command to run from the shell. – Jon Skeet Jul 10 '12 at 03:46
  • Ok so i was able to run both and now the query of my question doesn't return errors. The thing the time still the same using myql NOW(); I even reboted the server – Toni Michel Caubet Jul 11 '12 at 15:46
  • 1
    @ToniMichelCaubet The value returned by `NOW()` is expressed in the current time zone. Now you have some populated time zones, for debugging purposes, try changing the time zone to, say, `'US/Eastern'` and calling `NOW()` and then changing back to `'Europe/Madrid'` and calling `NOW()` and then ensuring that there is a difference. – Rich O'Kelly Jul 11 '12 at 15:53
  • No difference.. do I have to reboot every time? – Toni Michel Caubet Jul 11 '12 at 17:21
  • @ToniMichelCaubet If you run the following SQL: `SELECT @@global.time_zone, @@session.time_zone;`, what is the result? – Rich O'Kelly Jul 13 '12 at 13:11
  • 1
    @ToniMichelCaubet With `SUPER` privilege execure the follwoing: `SET GLOBAL time_zone = 'Europe/Madrid';` then run `SELECT @@global.time_zone, @@session.time_zone;`, and then repeat with the `'US/Eastern'` timezone. What're the results? – Rich O'Kelly Jul 13 '12 at 13:58
2

An alternate solution if you don't want to make your own timezone settings tables.

If have your own server, leave MySQL as it is. It defaults to the timezone SYSTEM.

Ubuntu users can use 

dpkg-reconfigure tzdata

For more conservative OSes (Centos, RedHat, Fedora) use

tzconfig

commands for changing the system timezone. A CLI application will pop up where you can select the region and location. It's not only two birds by one stone, but it also automatically accomodates to daylight changes without having to specify when and what in MySQL timezone tables.

enter image description here

Don't forget to restart your MySQL server for the changes to take effect.

Rápli András
  • 3,869
  • 1
  • 35
  • 55