Created on
10-24-2019
07:42 AM
- last edited on
10-24-2019
10:59 AM
by
VidyaSargur
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
Created 10-25-2019 01:18 AM
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
Created 03-24-2021 01:47 PM
--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