-- 将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; #立即生效
