Hive中时间戳与日期转换与MongoDB中UTC时间处理

1.时间戳转成日期

select distinct from_unixtime(1441565203,‘yyyy/MM/dd HH:mm:ss’) from test_date;

2.日期转成时间戳

select distinct unix_timestamp(‘20111207 13:01:03’) from test_date; // 默认格式为“yyyy-MM-dd HH:mm:ss“

select distinct unix_timestamp(‘20111207 13:01:03’,‘yyyyMMdd HH:mm:ss’) from test_date;

yyyymmdd和yyyy-mm-dd日期之间的切换
方法1: from_unixtime+ unix_timestamp
–20181205转成2018-12-05
select from_unixtime(unix_timestamp(‘20181205’,‘yyyymmdd’),‘yyyy-mm-dd’) from dual;

–2018-12-05转成20181205
select from_unixtime(unix_timestamp(‘2018-12-05’,‘yyyy-mm-dd’),‘yyyymmdd’) from dual;

方法2: substr + concat
–20181205转成2018-12-05
select concat(substr(‘20181205’,1,4),’-’,substr(‘20181205’,5,2),’-’,substr(‘20181205’,7,2)) from dual;

–2018-12-05转成20181205
select concat(substr(‘2018-12-05’,1,4),substr(‘2018-12-05’,6,2),substr(‘2018-12-05’,9,2)) from dual;

3.MongoDB非东八区时间处理

将mongodb的UTC时间转化为GMT+8时间

hive (default)> select from_unixtime(unix_timestamp(regexp_replace('2019-09-28T15:59:50.534944805Z', 'T|Z', ' '))+28800,'yyyy-MM-dd HH:mm:ss');
OK
_c0
2019-09-28 23:59:50
Time taken: 0.041 seconds, Fetched: 1 row(s)

28800/3600=8 小时

4.Hive中获取当前系统时间

hive (default)> select from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss');
unix_timestamp(void) is deprecated. Use current_timestamp instead.
OK
_c0
2020-11-20 11:42:55
Time taken: 0.038 seconds, Fetched: 1 row(s)

hive (default)> select current_timestamp;
OK
_c0
2020-11-20 11:43:26.053
Time taken: 0.032 seconds, Fetched: 1 row(s)

5.hive中计算时间差

hive (default)> select datediff(current_timestamp,from_unixtime(1603798755,'yyyy-MM-dd HH:mm:ss'))
              > ;
OK
_c0
24
Time taken: 0.54 seconds, Fetched: 1 row(s)
hive (default)> select datediff(current_timestamp,'2020-11-19')
              > ;
OK
_c0
1
Time taken: 0.034 seconds, Fetched: 1 row(s)
hive (default)> select datediff(current_timestamp,'2020-11-18 00:00:99');
OK
_c0
2
Time taken: 0.03 seconds, Fetched: 1 row(s)

6.注意

1.值得注意的是，时间戳有可能是毫秒级的，然后这时候直接使用from_unixtime(1441565203,‘yyyy/MM/dd HH:mm:ss’)的话就会得到很奇怪的日期了，这时候要这样from_unixtime(cast(151331629920/1000 as int))，同样的，时间转成毫秒级的时间戳也要乘以1000，如：unix_timestamp(‘2018-12-18 00:38:50’)*1000

2.如何区分时间戳是秒级还是毫秒级呢？一般来说，常见的时间戳是10位数的，13位数的时间戳就是毫秒级的

7.shell脚本中遇到时间

  tommorrow=`date -d "+1 day" "+%Y-%m-%d"`
  $HIVE_HOME/bin/hive -e "
  SELECT  DATEDIFF('$tommorrow','2020-12-03');
 "
 注意：时间要  "  ' 时间  '   " ，这样在hive中才是字符串时间