I have a query wherein I need to extract timestamp with milliseconds to match data from Source. Below is sample data from SQL server (Source) and column is defined as Datetime
U_Date
2007-10-26 10:07:00.000
2005-05-04 11:12:54.297
2004-05-29 03:56:24.792
Target is Hadoop through Impala and defined as Timestamp
U_Date
2007-10-26 10:07:00
2005-05-04 11:12:54.297000000
2013-05-31 04:07:00
2013-11-01 15:34:00
I am looking for query to get timestamp as displayed in Source with millisecond. I have put together my query as under, but it defaults milliseconds to 000.
select from_unixtime(unix_timestamp(u_date),'yyyy-MM-dd HH:mm:ss.SSS') from table A
Generated result set is as below:
2007-10-26 10:07:00.000
2005-05-04 11:12:54.000 (Millisecond .297 defaulted to 000)
2013-05-31 04:07:00.000
2013-11-01 15:34:00.000
Kindly share your inputs
To get a formatted timestamp up to 3 digit milliseconds, you can use the from_timestamp function
SELECT from_timestamp(u_date, 'yyyy-MM-dd HH:mm:ss.SSS') FROM table;
the function unix_timestamp somehow discards the millisecond information while the function from_unixtime doesn't seem to interpret unix timestamps with milliseconds well either
(tested on Impala v2.9.0)