Get timestamp in milliseconds in Impala

[#1]

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 · Accepted solution]

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

[#3]

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

?

[#4]

@chophouseI wonder if the example I posted here would solve your problem: http://community.cloudera.com/t5/Interactive-Short-cycle-SQL/Why-not-from-unixtime-function-handles-...

[#5]

Thanks Tim.  That works for my use case

[#6]

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 ?