question Re: Get timestamp in milliseconds in Impala in Archives of Support Questions (Read Only)

Get timestamp in milliseconds in Impala

Manjuyell — Fri, 16 Sep 2022 12:26:14 GMT

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

Re: Get timestamp in milliseconds in Impala

Tim Armstrong — Tue, 24 Oct 2017 18:38:45 GMT

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

chophouse — Fri, 05 Jan 2018 21:51:56 GMT

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

Tim Armstrong — Fri, 05 Jan 2018 22:47:03 GMT

@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-an-unix-timestamp-in/m-p/63182#M3969

Re: Get timestamp in milliseconds in Impala

chophouse — Wed, 14 Mar 2018 22:06:46 GMT

Thanks Tim. That works for my use case

Re: Get timestamp in milliseconds in Impala

Manan — Fri, 14 Jun 2019 09:06:52 GMT

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 ?