Error in timestamp conversion in HiveQL

Question

I use the following query to convert string to timestamp in hiveql but it is not getting converted as expected.
There is difference in date and time after conversion
It totally ignore milliseconds part of the timestamp.
Is there any other way to do this correctly?

select RQSTSENTTM,
from_unixtime(unix_timestamp(RQSTSENTTM,"dd-MMM-yy HH.mm.ss.SSSSSSSSS a") 
,"yyyy-MM-dd  HH.mm.ss.SSSSSSSSS a")
from log limit 50;



     RQSTSENTTM                   After_conversion
05-JAN-16 05.29.17.449000000 PM 2016-01-10 10.12.37.000000000 AM
06-JAN-16 12.24.59.143000000 PM 2016-01-08 04.08.19.000000000 AM
06-JAN-16 12.24.59.215000000 PM 2016-01-09 00.08.19.000000000 AM
08-JAN-16 03.13.25.609000000 PM 2016-01-15 04.23.25.000000000 AM
08-JAN-16 03.13.25.732000000 PM 2016-01-16 14.33.25.000000000 PM
08-JAN-16 03.26.51.416000000 PM 2016-01-12 23.00.11.000000000 PM
08-JAN-16 03.26.51.462000000 PM 2016-01-13 11.46.51.000000000 AM
08-JAN-16 03.33.49.984000000 PM 2016-01-19 12.53.49.000000000 PM
08-JAN-16 03.33.50.046000000 PM 2016-01-08 16.20.30.000000000 PM
08-JAN-16 03.37.21.740000000 PM 2016-01-16 17.10.41.000000000 PM
08-JAN-16 03.37.21.796000000 PM 2016-01-17 08.44.01.000000000 AM
08-JAN-16 03.37.58.752000000 PM 2016-01-16 20.31.18.000000000 PM
08-JAN-16 03.37.58.820000000 PM 2016-01-17 15.24.38.000000000 PM
08-JAN-16 03.39.25.877000000 PM 2016-01-18 07.16.05.000000000 AM
08-JAN-16 03.39.25.954000000 PM 2016-01-19 04.39.25.000000000 AM

This appears to be hiveql, not hql. Did you see this question? http://stackoverflow.com/questions/31701847/hives-unix-timestamp-and-from-unixtime-functions — carbontax, Apr 14 '16 at 21:34
@carbontax I saw the question in the link. The problem is in the millisecond part of the string, there you don't have milliseconds.Anyway tried it not working getting null. — Balajee, Apr 15 '16 at 04:25

Error in timestamp conversion in HiveQL

0 Answers0