Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

timestamp not supported in HIVE

avatar
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

avatar
Master Guru
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
3 REPLIES 3

avatar
Master Guru
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login

avatar
Master 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 🙂


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