Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

timestamp not supported in HIVE

Solved Go to solution

timestamp not supported in HIVE

Expert Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: timestamp not supported in HIVE

Super Guru

@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 REPLIES 3

Re: timestamp not supported in HIVE

Super Guru

@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

Super Guru

@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 :-)


Re: timestamp not supported in HIVE

Expert Contributor

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

Don't have an account?
Coming from Hortonworks? Activate your account here