CODEWITHSUNDEEP
hadoophivehiveql
Shivpe_R
Shivpe_R

Reputation: 1080

Hive - Convert string like 'yyyy-MM-dd' to date as Datatype 'yyyy-MM-dd

I have a external table in hive, which has report_dt as column and datatype is string and has value like 2018-09-02 2018-09-03

And i want to create managed table from the query written on this external table with report_dt_1 as column with datatype as date

i have gone through some threads and i was able to query like 

create table manag_newtable as select to_date(from_unixtime(unix_timestamp(report_dt,'yyyy-MM-dd'),'yyyy-MM-dd')) as report_dt_1 from exter_table;

so the above query creates a new table, but when i desc manag_newtable ,still that report_dt_1 shows as string datatype

Upvotes: 0

Views: 3491

Answers (1)

OneCricketeer
OneCricketeer

Reputation: 191681

Prior to Hive 2.1.0, TO_DATE returns a string.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

Perhaps you want to try a CAST.
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-date

Otherwise, I suggest leaving the column as epoch time because 1) with your current format you lose timezone information, although you never had it so you are assuming UTC? 2) Hive requires less deserialization time for numbers, making queries faster. If you have to format the epoch, do that at the reporting layer, not the storage layer

Upvotes: 1

Related Questions