MySQL Time zone processing

The developer executes the following SQL

[Email protected] {WM_ZTCJ} >select Timestampdiff (second, ' 1970-1-1 ', ' 2014-07-23 9:18:40 ') as timestamp;+------------+| Timestamp |+------------+| 1406107120 |+------------+1 row in Set (0.00 sec)

Pass the obtained timestamp in code and restore the date with the From_unixtime function

Mysql> Select From_unixtime (1406107120); +---------------------------+| From_unixtime (1406107120) |+---------------------------+| 2014-07-23 17:18:40 |+---------------------------+1 row in Set (0.00 sec)

The time value returned is 8 hours apart to view the current system timestamp

[Email protected] {WM_ZTCJ} >\! datewed Jul 10:49:09 CST 2014

To view the time zone settings for a database

Mysql> Show variables like '%system_time% '; +------------------+-------+| variable_name | Value |+------------------+-------+| System_time_zone | CST |+------------------+-------+1 row in Set (0.00 sec)

This value is the same as the operating system's time zone by default

Check official documents:

Http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html

The default is controlled by the System_time_zone parameter after Mysqld is started , and the time zone setting for each session is controlled by the Time_zone variable

Mysql> SELECT @ @global. system_time_zone,@ @time_zone, +---------------------------+-------------+| @ @global. System_time_zone | @ @time_zone |+---------------------------+-------------+| CST | SYSTEM |+---------------------------+-------------+1 row in Set (0.00 sec)

Since the system time zone is cst=gmt+8 at this time, replacing Time_zone with +00:00 is GMT.

Mysql> set time_zone= ' +00:00 '; Query OK, 0 rows Affected (0.00 sec) mysql> Select From_unixtime (1406107120); +---------------------------+| From_unixtime (1406107120) |+---------------------------+| 2014-07-23 09:18:40 |+---------------------------+1 row in Set (0.00 sec)

The difference between From_unixtime and Unix_timestamp is recorded in another blog post.