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'
Option 2: Edit the MySQL Configuration File Scroll down to the [mysqld] section, and find the default-time-zone = "+00:00" line. Change the +00:00 value to the GMT value for the time zone you want. Save the file and exit. In the example below we set the MySQL Server time zone to +08:00 (GMT +8).
You can also set MySQL server time zone in your server configuration file my. cnf. Open the file in a terminal. Change the '+00:00' to your time zone's GMT offset, such as '-6:00'.
The MySQL NOW() function returns the current date and time in the configured time zone as a string or a number in the 'YYYY-MM-DD HH:MM:DD' or 'YYYYMMDDHHMMSS.
date_default_timezone_set('Asia/Calcutta'); Show activity on this post. You can simply use the "date_default_timezone_set" function to set the date to your location.
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.
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.
Don't forget to restart your MySQL server for the changes to take effect.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With