Support Questions

Find answers, ask questions, and share your expertise

Get timestamp in milliseconds in Impala

avatar
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

avatar

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

 

View solution in original post

5 REPLIES 5

avatar

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

 

avatar
Contributor

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

 

?

avatar

avatar
Contributor

Thanks Tim.  That works for my use case

avatar
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 ?