Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Get timestamp in milliseconds in Impala

SOLVED Go to solution

Get timestamp in milliseconds in Impala

New Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Get timestamp in milliseconds in Impala

Master Collaborator

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                                                                                       |
+---------------------------------------------------------------------------------------------------------------+

 

5 REPLIES 5

Re: Get timestamp in milliseconds in Impala

Master Collaborator

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                                                                                       |
+---------------------------------------------------------------------------------------------------------------+

 

Re: Get timestamp in milliseconds in Impala

Explorer

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

 

?

Re: Get timestamp in milliseconds in Impala

Master Collaborator

Re: Get timestamp in milliseconds in Impala

Explorer

Thanks Tim.  That works for my use case

Highlighted

Re: Get timestamp in milliseconds in Impala

New Contributor

how to do the same in unique way for all the columns, i mean inspite of providing numeric values what if i give the db.column_name ?