MySQL Time Zones, time_zone

The MySQL server maintains several time zone settings:
  • System time zone. When the server starts, it attempts to determine the time zone of the host machine and uses it to set the system_time_zone system variable.
  • The server’s time zone. The global time_zone system variable. If you have SUPER privileges, you may: SET GLOBAL time_zone = timezone;. You may use: SELECT @@global.time_zone; to get its current value.
  • Per-connection time zones. Initially takes its value from time_zone, but the client can change its own time zone with: SET time_zone = timezone;. You may use: SELECT @@session.time_zone; to get its current value.
The ‘timezone’ values format:
  • 'SYSTEM' means: Same as the system’s time zone (read above).
  • A string indicating an offset from UTC, such as '+10:00' or '-6:00'. So for example, to set your current connection’s time zone to UTC, use: SET time_zone='+00:00'; (and if you change your mind, revert to using 'SYSTEM' time zone using: SET time_zone='SYSTEM';).
  • Named time zone, such as 'Europe/Helsinki', 'US/Eastern', or 'MET'. Can be used only if the time zone information tables in the mysql database have been created and populated!
More info at MySQL documentation here.

MySQL, AUTO_INCREMENT, SHOW TABLE STATUS

Some useful notes about MySQL’s AUTO_INCREMENT value:
  • Available only for those tables where one column uses AUTO_INCREMENT. See a very clear example for a table that uses AUTO_INCREMENT in MySQL documentation here.
  • To find what the current AUTO_INCREMENT value of some table is, you should NOT rely on the rows count, as some threads in StackOverflow suggest. Instead, SHOW TABLE STATUS LIKE `yourtablename`; will reveal some info on your table. One of the columns will be “AUTO_INCREMENT”, with the value you’re looking for: The next time a row is inserted, the field with the AUTO_INCREMENT property will get this value. For example, this value is initially 1 when creating the table, and also after a TRUNCATE.
  • To get only the AUTO_INCREMENT value: SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name=`yourtablename` AND table_schema = DATABASE();
  • To compare with the number of rows (say, to verify that the AUTO_INCREMENT value equals to the number of rows plus 1), consider: SELECT TABLE_ROWS, AUTO_INCREMENT FROM information_schema.tables WHERE table_name=`yourtablename` AND table_schema = DATABASE();
  • To modify the AUTO_INCREMENT value yourself (say, to the number 123), you need: ALTER TABLE `yourtablename` AUTO_INCREMENT = 123;
  • A hack: To “reset” the AUTO_INCREMENT value yourself to the lowest possible number, try: ALTER TABLE `yourtablename` AUTO_INCREMENT = 1;
  • Last notes about ‘SHOW TABLE STATUS’: Yes, it has a weird syntax. Also, don’t get startled if the TABLE_ROWS column shows a different number of rows than you actually have in that table (i.e. when calling SELECT COUNT(1) FROM `yourtablename`, for example).