Hive date/timestamp column

Question

I have some data on HDFS that I am trying to setup to be queried via hive. The data is in the form of comma separated text files. One of the columns in the file is the date/time column as follows:

Wed Aug 29 16:16:58 CDT 2018

When I try to read the Hive table created using the following script, I get NULL as the value being read for this column..

use test_db;
drop table ORDERS;

create external table ORDERS(
    SAMPLE_DT_TM TIMESTAMP
    ...
)
row format delimited
fields terminated by ',' 
stored as textfile
location '/user/data';

When I replace TIMESTAMP by STRING, I am able to read the column value. But not sure how to read it as an appropriate date format supported by Hive...

Answer 1

From Hive 1.2, you can set your date format like this.

ALTER TABLE ORDERS SET SERDEPROPERTIES ("timestamp.formats"="EEE MMM dd HH:mm:ss zzz yyyy");

From docs.

On the table level, alternative timestamp formats can be supported by providing the format to the SerDe property "timestamp.formats" (as of release 1.2.0 with HIVE-9298). For example, yyyy-MM-dd'T'HH:mm:ss.SSS,yyyy-MM-dd'T'HH:mm:ss.