CODEWITHSUNDEEP
sqlhiveqlhue
Devesh Sharma
Devesh Sharma

Reputation: 1

Hive query to get the oldest string timestamp in a table

Hi I want to find the oldest date from a string date column in format 20180209 00:00:00.

I am using the following query to get the string column in date format

select  from_unixtime(unix_timestamp(acc_last_change_date,  'yyyyMMddHHmmss')) from ACCOUNTS

but the result is returned as null.

Could you help me on same.

Upvotes: 0

Views: 1790

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Just use min():

select min(acc_last_change_date)
from accounts;

Your string is in a suitable format for using min().

If you want the entire row, you can use:

select a.*
from accounts a
order by a.acc_last_change_date
limit 1;

Upvotes: 1

Related Questions