MySQL Ver. 5.7 Time Zone New Variable log_timestamps

You can see new system varable log_timestamps in MySQL Ver. 5.7. It introduced MySQL Ver. 5.7.2 .

[log_timestamps] 

log_timestamps controls the timestamp time zone of error log, general query log and slow query log's time information.
You can set log_timestamps at runtime.
Value type is enumeration ( 'SYSTEM'.'UTC') .
Value format is YYYY-MM-DDhh:mm:ss.uuuuuu + z signifying Zulu time (UTC) or + hh:mm ( offset from UTC)

[Warnning]
If MySQL startup with --timezone to mysqld_safe, No effect log_timestamps.
You should use default-time-zone system variable instead of --timezone to mysqld_safe.

MySQL TimeZone Configuration

MySQL Time Zone.

1. 3 Level Time Zone. 

MySQL support 3 level time zone setting.

1). System Time Zone. 

System time zone is the highest level time zone. You can see system_time_zone system variable. If you don't setting anything, When the server starts, it attempts to determine the time zone of the machine.

When MySQL startup with the --timezone=XXX option to mysqld_safe, You can set system_time_zone variable. And, you can set system_time_zone system variable by setting the TZ environment variable.

2). Server Time Zone.

It means MySQL server's current time zone. System variables is time_zone. If system_time_zone doesn't set, the initial value of time_zone is SYSTEM.

You can set the global value of time_zone with --default-time-zone=XXX option to mysqld_safe. Or, You can write the following line in an option file

[mysqld]
default-time-zone='XXX'

You can set the global value of time_zone at runtime with this statement:

mysql> set global time_zone= XXX;

3). Connection Time Zone. 

Each client has its own time zone setting with this statement:

mysql> set time_zone = XXX; 

2. Configuration of Timezone. 

Value of Timezone can be given  several formats.

1) SYSTEM 

It means the time zone should be the same as the system's.

2) Offset from UTC 

such as '+00:00' , '+10:00' or '-6:00'

3) Named time zone 

such as 'Asia/Seoul' , 'US/Eastern' or 'Europe/Paris'
You must loading named time zone data in mysql schema.

Using Timestamp data with UTC type.

1. Using Function 

[root@oraclelinux6.localdomain][test]> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.01 sec)

[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-24 23:41:50 |
+---------------------+
1 row in set (0.00 sec)

[root@oraclelinux6.localdomain][test]> create table test ( ts timestamp not null) ;
Query OK, 0 rows affected (0.01 sec)

[root@oraclelinux6.localdomain][test]> insert into test( ts) values (utc_timestamp());
Query OK, 1 row affected (0.00 sec)

[root@oraclelinux6.localdomain][test]> select * from test;
+---------------------+
| ts                  |
+---------------------+
| 2015-06-25 06:41:39 |
+---------------------+
1 row in set (0.00 sec)

2. Setting time_zone value 

[root@oraclelinux6.localdomain][test]> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.01 sec)

[root@oraclelinux6.localdomain][test]> set session time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-25 06:44:20 |
+---------------------+
1 row in set (0.01 sec)

[root@oraclelinux6.localdomain][test]> set session time_zone='SYSTEM';
Query OK, 0 rows affected (0.00 sec)

[root@oraclelinux6.localdomain][test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-24 23:45:39 |
+---------------------+
1 row in set (0.00 sec)

[root@oraclelinux6.localdomain][test]> system date;
Wed Jun 24 23:45:47 PDT 2015
[root@oraclelinux6.localdomain][test]>

3. Loading Named Time Zone table 

You can use system's local's zoneinfo database. Or, You can use package that is available for download at the MySQL Developer Zone.

3.1 Using system's zoneinfo database. 

shell> cd /db/mysql/bin
shell> ./mysql_tzinfo_to_sql /usr/share/zoneino >> /tmp/timezone.sql
shell> ./mysql -p -u root
[root@oraclelinux6.localdomain][]> source /tmp/timezone.sql  

3.2 Using a package that is available for download at the MySQL Developer Zone. 

You Can see a package file list this url.
http://dev.mysql.com/downloads/timezones.html
Select one package for you. And then, Load MySQL's mysql schema.

[root@oraclelinux6.localdomain][]> use mysql;
[root@oraclelinux6.localdomain][mysql]> source /tmp/timezone_posix.sql

DATETIME and TIMESTAMP

1. DATA Type

DATETIME and TIMESTAMP datatype have year,month,day,hour,minute and seconds. Additionally, They can save fraction of second. They use data format with YYYY-MM-DD HH:MM:SS[.fraction]. DATETIME can save value from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999' . TIMESTAMP can save value from '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.99999' UTC.

2. Different DATETIME and TIMESTAMP 

DATETIME have no timezone information. TIMESTAMP have timezone information with time_zone system  variables. In detail,  TIMESTAMP have date and time data in UTC timezone. So, Before saving date and time data, MySQL convert timezone to UTC.

3. Setting default value 

After MySQL Version 5.6, DATETIME setting default time value.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);