- last edited on
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.
The best solution is to convert the Monthes to integers like:
-Oct- => -10-
So that is what i tested as you can see my file below:
$ hdfs dfs -cat /lab/helmi/test_timestamp_MM.txt1,2019-10-14 20:00:01.0278982,2019-12-10 21:00:01.0233,2019-11-25 20:00:01.034,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
hive> select * from ttime;OK1 2019-10-14 20:00:01.0278982 2019-12-10 21:00:01.0233 2019-11-25 20:00:01.034 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;