Want to convert timestamp to date format in hive

want to convert this number ‘20210412070422’ to date format ‘2021-04-12’ in hive

I am trying but this returns null value

from_unixtime(unix_timestamp(eap_as_of_dt, ‘MM/dd/yyyy’))

Answer

The best methoid is to do without unix_timestamp/from_unixtime if possible and in your case it is possible. date() can be removed, string in yyyy-MM-dd format is compatible with date type:

select date(concat_ws('-',substr(ts,1,4),substr(ts,5,2),substr(ts,7,2)))
from
(
select '20210412070422' as ts
)s

Result:

2021-04-12

Another efficient method using regexp_replace:

select regexp_replace(ts,'^(\d{4})(\d{2})(\d{2}).*','$1-$2-$3')

If you prefer using unix_timestamp/from_unixtime

select date(from_unixtime(unix_timestamp(ts, 'yyyyMMddHHmmss')))
from
(
select '20210412070422' as ts
)s

But it is more complex, slower (SimpleDateFormat class is involved) and error prone because will not work if data is not exactly in expected format, for example ‘202104120700’

Of course you can make it more reliable by taking substring of required length and using yyyyMMdd template:

select date(from_unixtime(unix_timestamp(substr(ts,1,8), 'yyyyMMdd')))
from
(
select '20210412070422' as ts
)s

It makes it even more complex.

Use unix_timestamp/from_unixtime only if simple substr or regexp_replace do not work for data format like ‘2021Apr12blabla’.