背景：MySql数据库安装在Linux环境上。
==================================
查看当前Linux环境上的时间和时区
SC-MOVDC-1:~ # date -R
Mon, 19 Mar 2018 20:48:17 +0800
可以看到当前时间为：Mon, 19 Mar 2018 20:48:17
可以看到当前时区为：+0800
=====================================
MySql默认时区：如果不指定MySQL时区，那么MySql默认时区是服务器的时区。
查看MySql当前使用的时区：show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
MySql查看当前时间：select now();
+---------------------+
| now() |
+---------------------+
| 2018-03-19 20:51:40 |
+---------------------+
MySql修改时间戳：set time_zone = '+00:00'; select now();
+---------------------+
| now() |
+---------------------+
| 2018-03-19 12:57:44 |
+---------------------+
发现时间较少了8小时。
=======================================
1.构造表格
CREATE TABLE test_time(
id int8 PRIMARY KEY AUTO_INCREMENT,
timestamp TIMESTAMP,
insert_time_zone VARCHAR(10),
insert_function VARCHAR(20),
insert_timestamp VARCHAR(20)
);
2.
当设置MySql时区为'+00:00'的前提下
set time_zone = '+00:00'; select now();
+---------------------+
| now() |
+---------------------+
| 2018-03-19 12:57:44 |
+---------------------+
set time_zone = '+00:00'; select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2018-03-19 12:57:44 |
+---------------------+
set time_zone = '+00:00'; select utc_timestamp();
+---------------------+
| utc_timestamp() |
+---------------------+
| 2018-03-19 12:57:44 |
+---------------------+
当设置MySql时区为'+08:00'的前提下
set time_zone = '+08:00'; select now();
+---------------------+
| now() |
+---------------------+
| 2018-03-19 21:08:42 |
+---------------------+
set time_zone = '+08:00'; select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2018-03-19 21:08:42 |
+---------------------+
set time_zone = '+08:00'; select utc_timestamp();
+---------------------+
| utc_timestamp() |
+---------------------+
| 2018-03-19 13:08:42 |
+---------------------+
3.
清空
delete from test_time ;
存入
SET @@session.time_zone = '+00:00';
当前now()时间为2018-03-19 13:29:07,用NOW代替
INSERT INTO test_time (timestamp, insert_time_zone, insert_function, insert_timestamp)
VALUES (current_timestamp(), '+00:00', 'current_timestamp', date_format(current_timestamp(), get_format(datetime, 'ISO')));
查询
SET @@session.time_zone = '+00:00';
select * from test_time;
+----+---------------------+------------------+-------------------+---------------------+
| id | timestamp | insert_time_zone | insert_function | insert_timestamp |
+----+---------------------+------------------+-------------------+---------------------+
| 1 | 2018-03-19 13:00:42 | +00:00 | current_timestamp | 2018-03-19 13:00:42 |
+----+---------------------+------------------+-------------------+---------------------+
查询
SET @@session.time_zone = '+08:00';
select * from test_time;
+----+---------------------+------------------+-------------------+---------------------+
| id | timestamp | insert_time_zone | insert_function | insert_timestamp |
+----+---------------------+------------------+-------------------+---------------------+
| 1 | 2018-03-19 21:13:18 | +00:00 | current_timestamp | 2018-03-19 13:13:18 |
+----+---------------------+------------------+-------------------+---------------------+
发现以'+00:00'时区存入的current_timestamp()时间NOW，在以'+00:00'时区查出时为NOW，在以'+08:00'时区查出时为NOW+8
4.
清空
delete from test_time ;
存入
SET @@session.time_zone = '+00:00';
当前now()时间为2018-03-19 13:29:07,用NOW代替
INSERT INTO test_time (timestamp, insert_time_zone, insert_function, insert_timestamp)
VALUES (utc_timestamp(), '+00:00', 'utc_timestamp', date_format(utc_timestamp(), get_format(datetime, 'ISO')));
查询
SET @@session.time_zone = '+00:00';
select * from test_time;
+----+---------------------+------------------+-------------------+---------------------+
| id | timestamp | insert_time_zone | insert_function | insert_timestamp |
+----+---------------------+------------------+-------------------+---------------------+
| 1 | 2018-03-19 13:00:42 | +00:00 | utc_timestamp | 2018-03-19 13:00:42 |
+----+---------------------+------------------+-------------------+---------------------+
查询
SET @@session.time_zone = '+08:00';
select * from test_time;
+----+---------------------+------------------+-------------------+---------------------+
| id | timestamp | insert_time_zone | insert_function | insert_timestamp |
+----+---------------------+------------------+-------------------+---------------------+
| 1 | 2018-03-19 21:17:49 | +00:00 | utc_timestamp | 2018-03-19 13:17:49 |
+----+---------------------+------------------+-------------------+---------------------+
发现以'+00:00'时区存入的utc_timestamp()时间NOW，在以'+00:00'时区查出时为NOW，在以'+08:00'时区查出时为NOW+8
5.
清空
delete from test_time ;
存入
SET @@session.time_zone = '+08:00';
当前now()时间为2018-03-19 21:26:24,用NOW代替
INSERT INTO test_time (timestamp, insert_time_zone, insert_function, insert_timestamp)
VALUES (current_timestamp(), '+08:00', 'current_timestamp', date_format(current_timestamp(), get_format(datetime, 'ISO')));
查询
SET @@session.time_zone = '+00:00';
select * from test_time;
+----+---------------------+------------------+-------------------+---------------------+
| id | timestamp | insert_time_zone | insert_function | insert_timestamp |
+----+---------------------+------------------+-------------------+---------------------+
| 1 | 2018-03-19 13:23:43 | +08:00 | current_timestamp | 2018-03-19 21:23:43 |
+----+---------------------+------------------+-------------------+---------------------+
SET @@session.time_zone = '+08:00';
select * from test_time;
+----+---------------------+------------------+-------------------+---------------------+
| id | timestamp | insert_time_zone | insert_function | insert_timestamp |
+----+---------------------+------------------+-------------------+---------------------+
| 1 | 2018-03-19 21:23:43 | +08:00 | current_timestamp | 2018-03-19 21:23:43 |
+----+---------------------+------------------+-------------------+---------------------+
发现以'+08:00'时区存入的current_timestamp()时间NOW，在以'+00:00'时区查出时为NOW-8，在以'+08:00'时区查出时为NOW
6.
清空
delete from test_time ;
存入
SET @@session.time_zone = '+08:00';
当前now()时间为2018-03-19 21:26:24,用NOW代替
INSERT INTO test_time (timestamp, insert_time_zone, insert_function, insert_timestamp)
VALUES (utc_timestamp(), '+08:00', 'utc_timestamp', date_format(utc_timestamp(), get_format(datetime, 'ISO')));
查询
SET @@session.time_zone = '+00:00';
select * from test_time;
+----+---------------------+------------------+-------------------+---------------------+
| id | timestamp | insert_time_zone | insert_function | insert_timestamp |
+----+---------------------+------------------+-------------------+---------------------+
| 1 | 2018-03-19 05:31:44 | +08:00 | utc_timestamp | 2018-03-19 13:31:44 |
+----+---------------------+------------------+-------------------+---------------------+
SET @@session.time_zone = '+08:00';
select * from test_time;
+----+---------------------+------------------+-------------------+---------------------+
| id | timestamp | insert_time_zone | insert_function | insert_timestamp |
+----+---------------------+------------------+-------------------+---------------------+
| 1 | 2018-03-19 13:31:44 | +08:00 | utc_timestamp | 2018-03-19 13:31:44 |
+----+---------------------+------------------+-------------------+---------------------+
发现以'+08:00'时区存入的utc_timestamp()时间NOW，在以'+00:00'时区查出时为NOW-16，在以'+08:00'时区查出时为NOW-8
======================================
时间戳的保存与查询：
保存：MySQL 将一个时间戳保存到数据库时，会认为这个时间戳是当前时区下的，在保存前会将其转换为 UTC 时间戳保存。
查询：从数据库中查询一个时间戳时，会将这个时间戳作为 UTC 时间戳，然后转换为当前数据库当前时区下的时间戳返回。
======================
实验过程记录
================
SET @@session.time_zone = '+00:00';
select now();2018-03-21 07:47:29
select current_timestamp();2018-03-21 07:47:29
select utc_timestamp();2018-03-21 07:47:29
==============
SET @@session.time_zone = '+08:00';
select now();2018-03-21 16:16:47
select current_timestamp();2018-03-21 16:16:47
select utc_timestamp();2018-03-21 08:16:47
====================
对于current_timestamp()函数
=======================
SET @@session.time_zone = '+00:00';
INSERT INTO test_time (timestamp) VALUES (current_timestamp());存入时间为2018-03-21 08:13:38
SET @@session.time_zone = '+00:00';
select * from test_time; 查询时间为2018-03-21 08:13:38
SET @@session.time_zone = '+08:00';
select * from test_time; 查询时间为2018-03-21 16:13:38
==========================
SET @@session.time_zone = '+08:00';
INSERT INTO test_time (timestamp) VALUES (current_timestamp());存入时间为2018-03-21 16:16:47
SET @@session.time_zone = '+00:00';
select * from test_time; 查询时间为2018-03-21 08:17:40
SET @@session.time_zone = '+08:00';
select * from test_time; 查询时间为2018-03-21 16:17:40
=============
发现规律
假设(1)时区偏移为x,(2)在当前时区下使用utc_timestamp()获取的时间为UTCTIME
对于current_timestamp()函数,其值为UTCTIME+x。首先获得current_timestamp()的值为A,然后结合时区偏移，A-x小时得到时间B，然后再将B存入数据库。注意：这个B是我们看不见的，只有查询表才能计算后返回给界面
当查询表中的时间时，从表中取出时间B，然后结合时区偏移,B+x小时得到时间C,然后将时间C返回给用户
==============================
对于utc_timestamp()函数
=================
SET @@session.time_zone = '+00:00';
INSERT INTO test_time (timestamp) VALUES (utc_timestamp());存入时间为2018-03-21 08:13:38
SET @@session.time_zone = '+00:00';
select * from test_time; 查询时间为2018-03-21 08:54:01
SET @@session.time_zone = '+08:00';
select * from test_time; 查询时间为2018-03-21 16:54:01
============
SET @@session.time_zone = '+08:00';
INSERT INTO test_time (timestamp) VALUES (utc_timestamp()); 存入时间为2018-03-21 08:16:47
SET @@session.time_zone = '+00:00';
select * from test_time;查询时间为2018-03-21 00:55:43
SET @@session.time_zone = '+08:00';
select * from test_time; 查询时间为2018-03-21 08:55:43
==============

发现规律

假设(1)时区偏移为x,(2)在当前时区下使用utc_timestamp()获取的时间为UTCTIME

对于utc_timestamp()函数，其值为UTCTIME。首先获得utc_timestamp()的值为A,然后结合时区偏移，A-x小时得到时间B，然后再将B存入数据库。注意：这个B是我们看不见的，只有查询表才能计算后返回给界面

当查询表中的时间时，从表中取出时间B，然后结合时区偏移,B+x小时得到时间C,然后将时间C返回给用户

==================

综合上面2个规律总结出一个终极规律：

假设时区偏移为x

对于functiontime()函数。首先获得functiontime()的值为A,然后结合时区偏移，A-x小时得到时间B，然后再将B存入数据库。注意：这个B是我们看不见的，只有查询表才能计算后返回给界面

当查询表中的时间时，从表中取出时间B，然后结合时区偏移,B+x小时得到时间C,然后将时间C返回给用户

=============

完！