Support Questions
Find answers, ask questions, and share your expertise

Hive - Convert Formatted String to Timestamp

Highlighted

Hive - Convert Formatted String to Timestamp

New Contributor

Hi Team,

I have a hive table column under this format, 2019-Oct-14 20:00:01.027898 as string but I want to insert in to another table as timestamp.

I tried using hive date functions but its cutting down the milliseconds, or its rounding up. I want the string to be stored exactly as it is in the table.

 

Could someone please provide me the easiest solution.

 

Thanks,
RNN

1 REPLY 1

Re: Hive - Convert Formatted String to Timestamp

Contributor

Hi @RNN 

 

The best solution is to convert the Monthes to integers like:

 

-Oct- => -10-

-Dec- =>-12-

 

So that is what i tested as you can see my file below:

$ hdfs dfs -cat /lab/helmi/test_timestamp_MM.txt
1,2019-10-14 20:00:01.027898
2,2019-12-10 21:00:01.023
3,2019-11-25 20:00:01.03
4,2019-01-06 20:00:01.123

 

Create a Hive table :

hive> CREATE EXTERNAL TABLE ttime(id int, t string)

ROW FORMAT DELIMITED FIELDS

TERMINATED BY ','

STORED AS TEXTFILE

LOCATION '/lab/helmi/';

 

hive> select * from ttime;
OK
1 2019-10-14 20:00:01.027898
2 2019-12-10 21:00:01.023
3 2019-11-25 20:00:01.03
4 2019-01-06 20:00:01.123

Time taken: 0.566 seconds, Fetched: 4 row(s)

 

Finally i created another table with the right format:

hive> create table mytime as select id, from_utc_timestamp(date_format(t,'yyyy-MM-dd HH:mm:ss.SSSSSS'),'UTC') as datetime from ttime;

 

hive_datetime.png

 

 

Best,

Helmi KHALIFA