Hive - Convert Formatted String to Timestamp

[#1]

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

[#2]

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;

Best,

Helmi KHALIFA

[#3]

--show diff values if hour is 02
select '2021-03-14 02:01:03.118', cast('2021-03-14 02:01:03.118' as timestamp)
OUTPUT
_c0 _c1
1 2021-03-14 02:01:03.118 2021-03-14 03:01:03.118