question Re: timestamp not supported in HIVE in Support Questions

timestamp not supported in HIVE

sampathkumar_ma — Sun, 16 Jun 2019 00:38:52 GMT

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

Re: timestamp not supported in HIVE

Shu_ashu — Mon, 17 Jun 2019 06:02:48 GMT

@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.

Re: timestamp not supported in HIVE

sampathkumar_ma — Wed, 19 Jun 2019 01:43:43 GMT

@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?

Re: timestamp not supported in HIVE

Shu_ashu — Wed, 19 Jun 2019 08:00:01 GMT

@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 🙂