CODEWITHSUNDEEP
sqlhadoophiveteradata-sql-assistant
Deepu298
Deepu298

Reputation: 75

Hadoop Data- Convert Sting timestamp into Hadoop date in SQL Assistant

I have a fields Name| ID | Timestamp

Timestamp is string like '06/29/2000 00:00:00' Now I have to filter the table based on date- let say

    Select Name
           ,ID
           ,Timestamp
   From Table Where **Function**(Timestamp)= '2000-06-29' (or 2000/06/29 or 06/29/2000)

I am using SQL assistant as UI tool with Hadoop HI I tried TO_DATE and couple of other functions. Please advise

Upvotes: 0

Views: 3510

Answers (3)

Deepu298
Deepu298

Reputation: 75

This helped me and we can still play around:

Select Name ,ID ,Timestamp From Table Where TO_DATE(from_unixtime(unix_timestamp(Timestamp,'MM/dd/yyyy HH:m:ss'),'yyyy-MM-dd'))>= '2018-07-10'

Upvotes: 0

roh
roh

Reputation: 1053

You can first change your timestamp format and apply the to_date function to trim the time from timestamp.

You can convert the timestamp format as below.

select from_unixtime(unix_timestamp('06/29/2000 00:00:00' ,'dd/MM/yyyy HH:mm:SS'), 'yyyy-MM-dd HH:mm:SS') from table;

Apply to_date function to the above sql. 

Select Name
           ,ID
           ,Timestamp
   From Table Where to_date(from_unixtime(unix_timestamp('06/29/2000 00:00:00' ,'dd/MM/yyyy HH:mm:SS'), 'yyyy-MM-dd HH:mm:SS'))= '2000-06-29'

I haven't tried the above solution, as i don't have environment with me right now. Let me know if you ran into any errors.

Upvotes: 1

Hanebambel
Hanebambel

Reputation: 121

You say Timestamp is as string? Have you tried to compare strings?

Select Name
       ,ID
       ,Timestamp
From Table 
where SUBSTR(Timestamp, 1, 10) = '06/29/2000'

Upvotes: 0

Related Questions