question Re: Impala - Convert bigint to timestamp in Archives of Support Questions (Read Only)

Impala - Convert bigint to timestamp

VJM — Fri, 16 Sep 2022 13:11:59 GMT

I've a table called "session" and it has a column 'conferencedatetime' with datatype as 'bigint'

I want to get the output of this column in a date/timestamp format (for e.g. 2018-01-01 01:00:00)

But I'm not able to get the desired output when I tried with the 'from_unixtime' function. (it returns NULL as output)

Can you please advise what is the correct way to get the output ?

---------------------
select s.conferencedatetime as starttime from session s ;
1500778867943

select from_unixtime(s.conferencedatetime, "yyyy-MM-dd HH:mm:ss") as starttime from session s ;
NULL
--------------------------------

Re: Impala - Convert bigint to timestamp

AcharkiMed — Thu, 31 May 2018 17:00:21 GMT

HI @VJM

You shold use the cast function:

select cast(s.conferencedatetime as TIMESTAMP) as starttime from session s;

Good luck.

Re: Impala - Convert bigint to timestamp

VJM — Thu, 31 May 2018 18:38:52 GMT

Hi @AcharkiMed - Thanks for the response. But that query also returns NULL as the output.

But I got a resolution for the issue

select from_timestamp(CAST(CAST(s.conferencedatetime as decimal(30,0))/1000 AS timestamp), "yyyy-MM-dd HH:mm:ss.SSS") as starttime from session s

Re: Impala - Convert bigint to timestamp

kunalkhadse11 — Wed, 23 Jan 2019 08:01:15 GMT

I am inserting the data using below column,also myoutput table column has dataype timestamp.

But its not casting it into the timestamp.

from_timestamp(cast(T1.converted_start_dt as timestamp),'yyyy-MM-dd HH:mm:ss.SSSSSSSSS') start_dt

Please suggestsolution

Re: Impala - Convert bigint to timestamp

Gomathinayagam — Mon, 01 Apr 2019 20:30:35 GMT

Hi @kunalkhadse11 ,

Can you share the sample data, the complete query and the actual, expected output?

Thanks

Re: Impala - Convert bigint to timestamp

NVidak — Tue, 07 May 2019 10:58:56 GMT

The same workaround worked for me too - also getting null when selecting from_unixtime(starttime). starttime is bigint. Is this a bug in Impala or ... ?

Also, in hive the following query normally works:

SELECT cast(starttime as TIMESTAMP) from dynatracelogs ORDER BY starttime desc LIMIT 100

but in Impala it returns nulls. ....