Reply
New Contributor
Posts: 2
Registered: ‎10-24-2017
Accepted Solution

Get timestamp in milliseconds in Impala

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

Cloudera Employee
Posts: 347
Registered: ‎07-29-2015

Re: Get timestamp in milliseconds in Impala

Hi Majuyell,

  I think the problem is your use of unix_timestamp(), which returns a value in seconds precision.

 

You probably want to convert directly to timestamp. It looks like your timestamps are in a regular format so you can probably cast directly.

 

> select cast("2005-05-04 11:12:54.297" as timestamp);
+----------------------------------------------+
| cast('2005-05-04 11:12:54.297' as timestamp) |
+----------------------------------------------+
| 2005-05-04 11:12:54.297000000                |
+----------------------------------------------+

Otherwise you can use to_timestamp() with a timestamp format string:

 

> select to_timestamp("2005-05-04 11:12:54.297", "yyyy-MM-dd HH:mm:ss.SSS");
+--------------------------------------------------------------------+
| to_timestamp('2005-05-04 11:12:54.297', 'yyyy-mm-dd hh:mm:ss.sss') |
+--------------------------------------------------------------------+
| 2005-05-04 11:12:54.297000000                                      |
+--------------------------------------------------------------------+


Then you can use from_timestamp to format it in your desired way:

 

> select from_timestamp(to_timestamp("2005-05-04 11:12:54.297", "yyyy-MM-dd HH:mm:ss.SSS"), "yyyy-MM-dd HH:mm:ss.SSS");

+---------------------------------------------------------------------------------------------------------------+
| from_timestamp(to_timestamp('2005-05-04 11:12:54.297', 'yyyy-mm-dd hh:mm:ss.sss'), 'yyyy-mm-dd hh:mm:ss.sss') |
+---------------------------------------------------------------------------------------------------------------+
| 2005-05-04 11:12:54.297                                                                                       |
+---------------------------------------------------------------------------------------------------------------+

 

Explorer
Posts: 11
Registered: ‎03-25-2017

Re: Get timestamp in milliseconds in Impala

How would you do this in reverse:

 

given a unixtime value of 13949501310621, how would you convert to :

 

2014-03-16 00:08:51.621

 

?

Cloudera Employee
Posts: 347
Registered: ‎07-29-2015

Re: Get timestamp in milliseconds in Impala

Explorer
Posts: 11
Registered: ‎03-25-2017

Re: Get timestamp in milliseconds in Impala

Thanks Tim.  That works for my use case

Announcements