「Hive」与「MySQL」中日期与时间戳转换函数：UNIX_TIMESTAMP() 和FROM_UNIXTIME()的用法与区别

     SQL中日期类型的timestamp时间戳类型为HQL专有类型，而MySQL没有此类时间戳类型，需要用长整型存储。

（一）日期转成时间戳：unix_timestamp(date[, 指定格式] ) 

• date：date可以是一个date字符串、datetime字符串、TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。

1. unix_timestamp()： 返回当前时间的时间戳

   函数无参数调用，则返回一个timestamp ('1970-01-01 00:00:00' GMT 之后的秒数) 作为无符号整数，即得到当前时间的时间戳；

2. unix_timestamp(date)： 返回对应时间的时间戳

   如果参数date满足yyyy-MM-dd HH:mm:ss形式，则只需调用date参数，即可得到参数对应的时间戳 ；

3. unix_timestamp(date, 指定格式)：返回指定格式的对应时间的时间戳

   如果参数date不满足yyyy-MM-dd HH:mm:ss形式，则需要指定date的形式，再进行转换。

• 示例：

1.   select unix_timestamp() 得到当前时间的时间戳

2.   select unix_timestamp('2018-12-05 01:10:00') 得到参数对应时间的时间戳

3.   select unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400

（二）时间戳转成日期：from_unixtime函数用法

• 参数timestamp：时间戳（bigint类型）。可以用数据库里的存储时间数据的字段。

  • timestamp是10位的时间戳值，即1970-1-1至今的秒数，而13位值的毫秒的是不可以的。

  • 对于13位时间戳，需要先截取，然后转换成bigint类型，因为from_unixtime类第一个参数只接受bigint类型。例如：

select from_unixtime(cast(substr(timestamp,1,10) as bigint),'yyyy-MM-dd HH:mm:ss')

• 语法：from_unixtime(timestamp,'yyyy-MM-dd HH:mm:ss')(这是默认格式，可以指定格式)

• 参数format：要转化的格式。

• format可以有的格式（MySQL与HQL不同）：

  • HQL：date字符串，如这里的Input Format

  • MySQL（如下）：比如 "%Y-%m-%d"格式化后的结果是 YYYY-mm-dd。

 %M 月名字(January~December)
 %W 星期名字( sunday~saturday)
 %D 有英语前缀的月份的日期(1st，2nd，3rd，等等。)
 %Y 年，数字，4位
 %y 年，数字，2位
 %a 缩写的星期名字(Sun~sat)
 %d 月份中的天数，数字(00~31)
 %e 月份中的天数，数字(0~31)
 %m 月，数字(01~12)
 %c 月，数字(1~12)
 %b 缩写的月份名字(Jan~Dec)
 %j 一年中的天数(001~366)
 %H 小时(00~23)
 %k 小时(0~23)
 %h 小时(01~12)

（三）总结

    上述两个函数可以结合使用，通过from_unixtime(unix_timestamp(string date),'yyyy-MM-dd HH:mm:ss')来规范时间的格式。例如：

select from_unixtime(unix_timestamp(reg_time,'yyyy/mm/dd'),'yyyy-MM-dd')

参考链接：from_unixtime_百度百科和 参考链接 。