Created on 05-08-2018 07:14 AM - edited 09-16-2022 06:11 AM
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
--------------------------------
Created 05-31-2018 11:38 AM
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
Created 05-31-2018 10:00 AM
HI @VJM
You shold use the cast function:
select cast(s.conferencedatetime as TIMESTAMP) as starttime from session s;
Good luck.
Created 05-31-2018 11:38 AM
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
Created 01-23-2019 12:01 AM
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
Created 04-01-2019 01:30 PM
Hi @kunalkhadse11 ,
Can you share the sample data, the complete query and the actual, expected output?
Thanks
Created 05-07-2019 03:58 AM
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. ....