在解析数据的时可能会遇到下面的两种日期格式:
- yyyy-MM-dd
- yyyyMMdd
这两种格式的转换通常有两种思路:
- 使用时间处理函数;
- 使用字符串处理函数。
假设现在的日期为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部分 即可。