Hive create query with timestamp having format AM/PM

Question

I have a file with only one date column which contains date in format '10/2/2017 10:56:00 AM'

I want to create a external hive table on this file but due to the AM/PM format hive timestamp is not able to recognize it, any pointers to this?

Answer 1

You can create hive table with timestamp format then in table properties map your data timestamp format.

Example:

i have a txt file with below data in it:

bash$ cat t1.txt
      9/1/2016 11:31:21 AM
      10/2/2017 10:56:00 AM

Create a hive table with:

hive> create external table i(ts timestamp)
      row format delimited
      fields terminated by ',' 
      stored as textfile
      TBLPROPERTIES("timestamp.formats"="MM/dd/yyyy hh:mm:ss a");

select from the table:

hive> select * from i;
+------------------------+--+
|          i.ts          |
+------------------------+--+
| 2016-09-01 11:31:21.0  |
| 2017-10-02 10:56:00.0  |
+------------------------+--+

Answer 2

Instead of using timestamp as datatype you can use string as a dataype and try it

Answer 3

Ok fine.In my view whether your datatype is in timestamp or in string format it is not going to make much difference.Because whatever you are trying to achieve by using timestamp format we can achieve using string format as well