mysql 时间格式转换，时区转化

女爷i 2022-03-24 18:13 414阅读 0赞

首先，为了更好的展示，我先把数据库里面存储的数据展示一下，是如下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70][]

1.时间格式转换

-- 时间'2019-01-22 15:45:06' 转换成 unix 时间戳
    select UNIX_TIMESTAMP(time) from testsssssss;

![20190122160529185.png][]

-- unix 时间戳 转换成 '2019-01-22 15:45:06'
    select FROM_UNIXTIME(timelong) from testsssssss;

![20190122160609221.png][]

-- 字符串转时间，格式化
    select str_to_date('2019-01-22','%Y-%m-%d') from testsssssss;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70 1][]

-- 时间转字符串, 格式化
    select DATE_FORMAT(time,'%Y-%m-%d %H:%i:%s') from testsssssss;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70 2][]

2. 时区转换

-- 加7小时
    select time, date_add(time,interval 7 hour) from testsssssss;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70 3][]

-- 减7小时
    select time, DATE_SUB(time,INTERVAL 7 HOUR) from testsssssss;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70 4][]

-- 转换时区
    select time, CONVERT_TZ(time,"+08:00","+01:00") from testsssssss;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70 5][]

最近在开发中常常使用时间的转换函数，所以一直都想整理一下这些函数，今天又一点点时间，所以赶快把博客给补上了，希望以后要用的时候能够有个很直接的资源，能够参考。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70]: /images/20220323/aec517b9e860406b9ea79445f9065b71.png
[20190122160529185.png]: /images/20220323/3bf80c9fcf784e8485cc3520ca36d5bb.png
[20190122160609221.png]: /images/20220323/8f72421a472e4eeeb00ef2595a6a082d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70 1]: /images/20220323/f7a825ceff334f47bd6b46a71eb228b3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70 2]: /images/20220323/2e069c64aae34195bbb9d7cd186054e4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70 3]: /images/20220323/7845f22e6e474623ab196d12bd8793d9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70 4]: /images/20220323/19bf14656ff74914a0d1533bf7d85e4c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NhaTczOTI5NTczMg_size_16_color_FFFFFF_t_70 5]: /images/20220323/c575b977fde44801a4dea54728258aa9.png