MySQL 和 Hive 中两种日期格式的转换

在解析数据的时可能会遇到下面的两种日期格式：

• yyyy-MM-dd
• yyyyMMdd

这两种格式的转换通常有两种思路：

1. 使用时间处理函数；
2. 使用字符串处理函数。

假设现在的日期为2020年2月2日，需要实现20200202与2020-02-02两种形式的相互转换。下面将介绍在 MySQL 和 Hive SQL 中分别是如何实现的。

MySQL

1.使用时间处理函数

unix_timestamp():

• 根据日期或时间获取 unix 时间戳；
• 返回距离 1970-01-01 08:00:00的秒数（与百度百科定义的不同，但不影响），注意：
  • MySQL默认 1970-01-01 00:00:00 ~ 1970-01-01 08:00:00，unix时间为都0，是从8点开始计算的；
• MySQL 的 unix_timestamp 函数只有一个参数，支持传入日期或时间，如果传入的是日期，默认时分秒为00:00:00。

---

from_unixtime():

• 时间格式转换函数，将 unix 时间戳转为指定时间格式；
  在 MySQL 中默认第二个参数是 ‘%Y-%m-%d %h:%i:%s’

-- 20200202 to 2020-02-02
-- 第一步：使用时间戳函数 unix_timestamp 获取 unix 时间戳
select unix_timestamp('20200202')    -- 返回 1580572800

-- 第二步：在第一步的基础上，使用时间格式转换函数 from_unixtime，
select from_unixtime(unix_timestamp('20200202'), '%Y-%m-%d')
-- 返回 2020-02-02 

-- 2020-02-02 to 2020
select from_unixtime(unix_timestamp('2020-02-02'), '%Y%m%d')
-- 返回 20200202

2.使用字符串处理函数

substr(string, start_num, len_num):

• 字符串截取函数；
• 返回字符串（string）中第几个元素起（start_num），长度为几（len_num）的元素；
• 如果 start_num 在字符串长度范围内，len_num 超出了字符串长度范围，返回自 start_num 开始至该字符串最后一个元素的字符串；
• 如果 start_num 在字符串之外，或者len_num 为0，会返回空字符串;
• 注：MySQL的字符串是从1开始算的。

---

concat(string1, string2, …):

• 字符串拼接函数；
• 支持多个参数，每个参数都是字符串，将这些字符串拼成1个字符串。

-- 20200202 to 2020-02-02
select concat(substr('20200202', 1, 4), '-', substr('20200202', 5, 2), '-', substr('20200202', 7, 2))

-- 2020-02-02 to 20200202
select concat(substr('2020-02-02', 1, 4), substr('2020-02-02', 6, 2), substr('2020-02-02', 9, 2))

Hive SQL

1.使用时间处理函数

unix_timestamp():

• 根据日期或时间获取 unix 时间戳；
• 若是中国时区，即返回距离1970-01-01 08:00:00 的秒数（之前的时间unix时间戳是负值）；
• 区别于MySQL，Hive 的 unix_timestamp 函数有 2 个参数：
  • 第一个为需要转化的时间或日期（可以是字符串形式），第二个为第一个参数中传入的时间或日期的格式，用于识别，默认为 ‘yyyy-MM-dd HH:mm:ss’；
  • 所以，不传入第二个参数时，传入第一个参数的日期只能按标准的‘yyyy-MM-dd HH:mm:ss’形式，不然会返回 NULL

---

from_unixtime():

• 时间格式转换函数，将 unix 时间戳转为指定时间格式；
  在 Hive 中默认第二个参数是 ‘yyyy-MM-dd HH:mm:ss’

-- 20200202 to 2020-02-02
-- 第一步：使用时间戳函数 unix_timestamp 获取 unix 时间戳
select unix_timestamp('20200202')     -- 返回 1580572800

-- 第二步：在第一步的基础上，使用时间格式转换函数 from_unixtime，
select cast(from_unixtime(unix_timestamp('20200202', 'yyyyMMdd'), 'yyyy-MM-dd') as string)
-- 返回 2020-02-02 
-- 因为hive存储时间的时候通常用string，所以加了个cast强转为字符串。

-- 2020-02-02 to 2020
select from_unixtime(unix_timestamp('2020-02-02', 'yyyy-MM-dd'), 'yyyyMMdd')
-- 返回 20200202

2.使用字符串处理函数

Hive SQL 中字符串处理函数 substr 与 concat 与 MySQL 基本一致
看 MySQL部分 即可。