timestamp not supported in HIVE

[#1]

Hi,

Data is in files in the following format 2019-06-15T15:43:12 ( yyyy-MM-ddThh:mm:ss )

When I do a select * on the table , it is displayed as NULL

Hive version - 1.2.1

ALTER TABLE table SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss"); - did not help

Followed the links,

https://issues.apache.org/jira/browse/HIVE-9298

https://www.ericlin.me/2017/03/alternative-timestamp-support-in-hive-iso-8601/

Could you please help on this. Thanks

[#2 · Accepted solution]

@Sampath Kumar

ALTER TABLE table SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss");

Works only in case of Textformat,CSV format tables.

If you are having other format table like orc..etc then set serde properties are not got to be working.

-

Tested by creating text format table:

Data:

1,2019-06-15T15:43:12
2,2019-06-15T15:43:19

create table i(id int,ts timestamp) row format delimited fields terminated by ',' stored as textfile;
ALTER TABLE i SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss");
 select * from i;
1       2019-06-15 15:43:12
2       2019-06-15 15:43:19

-

incase if we have orc file with 2019-06-15T15:43:12 format then altering the serde properties still results null format for timestamp field.

[#3]

@Sampath Kumar

Hive Timestamp type accepts format as yyyy-MM-dd HH:mm:ss[.SSS]

hive> select timestamp("2019-06-15 15:43:12");
2019-06-15 15:43:12
hive> select timestamp("2019-06-15 15:43:12.988");
2019-06-15 15:43:12.988

hive> select timestamp("2019-06-15T15:43:12")
NULL

If you are thinking to have timestamp type rather than text format tables then you use from_unixtime,unix_timestamp functions to remove "T" from the data and then you can have timestamp type in all formats.

-

If the answer is helpful to resolve the issue, Login and Click on Accept button below to close this thread.This will help other community users to find answers quickly 🙂

[#4]

@Shu: Thank you. Could you please let us know, If we are using file format other than Textformat, CSV, how to handle null for timestamp filed?