本文主要总结一下hive/mysql里常用的时间函数:
FROM_UNIXTIME() //unixtime–>time
UNIX_TIMESTAMP() //time–>unixtime
1.1 FROM_UNIXTIME()的用法:
FROM_UNIXTIME(unix_timestamp),
FROM_UNIXTIME(unix_timestamp,format)
ps: unix_timestamp可以是字段名,也可以直接是Unix 时间戳,format主要是将返回值格式化。
eg:
mysql>SELECT FROM_UNIXTIME( 1249488000, ‘%Y%m%d’ )
->20071120
mysql>SELECT FROM_UNIXTIME( 1249488000, ‘%Y年%m月%d’ )
->2007年11月20
mysql> Select FROM_UNIXTIME(875996580);
-> ‘1997-10-04 22:23:00’
mysql> Select FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
1.2 UNIXTIME()中列名的用法:
FROM_UNIXTIME(ctime, ‘yyyyMMdd’) = ‘20160611’
eg:
SELECT ctime AS pushtime, FROM_UNIXTIME(ctime, ‘yyyyMMdd’) as pushtime2
FROM DB
WHERE FROM_UNIXTIME(ctime, ‘yyyyMMdd’) = ‘20160611’ ;
2.UNIX_TIMESTAMP的用法:
UNIX_TIMESTAMP()