Get milisecond from Timestamp im Impala

0
votes

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

2
timestampimpala

2 Answers

0
votes

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)

0
votes

If you have a Unix Timestamp stored in your Database with milliseconds

Something like this 1525245791828

and you want to remove the milliseconds from this field so that you can do Unix Timestamp comparisons.

You can do something like this:

cast((you_unix_timestamp_with_milliseconds_column/1000) as BIGINT)