MySQL时间时区转换

-- 将timestamp转换为指定时区的时间，如：2018-09-21 11:48:42
select CONVERT_TZ(create_time, @@session.time_zone,'+8:00') from auth_user;

-- 将timestamp转换为指定时区的时间，并精确到天数,如：2018-09-21
select DATE_FORMAT(CONVERT_TZ(create_time, @@session.time_zone,'+8:00'),'%Y-%m-%d') from auth_user;

-- 将timestamp转换为指定时区的时间，并精确到天数后转换为timestamp，如：1537488000，精确到秒
select UNIX_TIMESTAMP(DATE_FORMAT(CONVERT_TZ(create_time, @@session.time_zone,'+8:00'),'%Y-%m-%d')) from auth_user;

-- 临时会话时区，转化为+08:00时区
SET time_zone='Asia/Shanghai';
select now();

SELECT count(*) AS amount, DATE_FORMAT(CONVERT_TZ(create_time, '+08:00','+10:00'),'%Y-%m-%d') 
AS createTime FROM auth_user WHERE DATE_FORMAT(CONVERT_TZ(create_time, '+08:00','+10:00'),'%Y-%m-%d')>= '2018-10-01'
AND DATE_FORMAT(CONVERT_TZ(create_time, '+08:00','+10:00'),'%Y-%m-%d')<= '2018-10-10' 
GROUP BY DATE_FORMAT(CONVERT_TZ(create_time, '+08:00','+10:00'),'%Y-%m-%d') 
ORDER BY DATE_FORMAT(CONVERT_TZ(create_time, '+08:00','+10:00'),'%Y-%m-%d') DESC

-- 查询结果
-- 1	2018-10-10
-- 2	2018-10-09
-- 1	2018-10-08
-- 1	2018-10-05
-- 11	2018-10-04
-- 15	2018-10-03
-- 2	2018-10-02
-- 5	2018-10-01

-- 转化为UTC时区，即　+00:00时区

SET time_zone='UTC';
select now();
SELECT count(*) AS amount, DATE_FORMAT(CONVERT_TZ(create_time, '+00:00','+10:00'),'%Y-%m-%d') 
AS createTime FROM auth_user WHERE DATE_FORMAT(CONVERT_TZ(create_time, '+00:00','+10:00'),'%Y-%m-%d')>=  '2018-10-01'
AND DATE_FORMAT(CONVERT_TZ(create_time,  '+00:00','+10:00'),'%Y-%m-%d')<= '2018-10-10' 
GROUP BY DATE_FORMAT(CONVERT_TZ(create_time,  '+00:00','+10:00'),'%Y-%m-%d')
 ORDER BY DATE_FORMAT(CONVERT_TZ(create_time,  '+00:00','+10:00'),'%Y-%m-%d') DESC 

-- 1	2018-10-10
-- 2	2018-10-09
-- 1	2018-10-08
-- 1	2018-10-05
-- 11	2018-10-04
-- 15	2018-10-03
-- 2	2018-10-02
-- 5	2018-10-01

> set global time_zone = '+8:00';  ##修改mysql全局时区为北京时间，即我们所在的东8区
> set time_zone = '+8:00';  ##修改当前会话时区
> flush privileges;  #立即生效