1

The data in the table as below. The column jobdate data type is string.

jobdate
1536945012211.kc
1536945014231.kc
1536945312809.kc

I want to convert it to time stamp as the format 2018-12-205 06:15:10.505

I have tried the following queries but returning NULL.

select jobdate,from_unixtime(unix_timestamp(substr(jobdate,1,14),'YYYY-MM-DD HH:mm:ss.SSS')) from job_log;
select jobdate,from_unixtime(unix_timestamp(jobdate,'YYYY-MM-DD HH:mm:ss.SSS')) from job_log;
select jobdate,cast(date_format(jobdate,'YYYY-MM-DD HH:mm:ss.SSS') as timestamp) from job_log;

Please help me.

Thanks in advance

leftjoin
  • 36,950
  • 8
  • 57
  • 116
ramkasa
  • 21
  • 2
  • Related, maybe duplicated https://stackoverflow.com/questions/31701847/hives-unix-timestamp-and-from-unixtime-functions – Cleptus Dec 05 '18 at 14:45

1 Answers1

1

Original timestamps are too long, use 10 digits:

hive> select from_unixtime(cast(substr('1536945012211.kc',1,10) as int),'yyyy-MM-DD HH:mm:ss.SSS');
OK
2018-09-257 10:10:12.000
Time taken: 0.832 seconds, Fetched: 1 row(s)
hive> select from_unixtime(cast(substr('1536945012211.kc',1,10) as int),'yyyy-MM-dd HH:mm:ss.SSS');
OK
2018-09-14 10:10:12.000
Time taken: 0.061 seconds, Fetched: 1 row(s)
hive>
leftjoin
  • 36,950
  • 8
  • 57
  • 116