Search code examples
sqldatetimehadoophivehiveql

Hive: Convert string with format yyyyyMMddHmmss.SSSSSSS to timestamp type

I am modifying the data type to move the data to the hive. The data is in the following format:

20221002060113.5896430
20221002102706.1747960
20221002094410.7570130
20221002082244.3088750
20221002070653.5202430
20221002052930.1846300
20221002070419.3333240
20221002091636.2612740
20221002061255.2212480
20221002130224.1436890
20221002050019.3692290
20221002075419.9681030
20221002081900.3694800
20221002071155.1969230
20221002100025.2322450
20221002045419.1629670
20221002043305.5806050

It needs to be transferred to the timestamp format, but it doesn't work as a function I know.

Below is the code I wrote:

SELECT 
cast(substring(test1, 1,4)||'-'||substring(test1, 5,2)||'-'||substring(test1, 7,2)||' '||substring(test1, 9,2)||':'||substring(test1, 11,2)||':'||substring(test1, 13,2)as timestamp) as tst1
FROM TEST.TESTTB

Write it in this way, I can convert it to timestamp format, but there are some missing values. Besides, this code is very dirty.

Solution

  • I think you're almost there.

    SELECT 
cast(substring(test1, 1,4)||'-'||substring(test1, 5,2)||'-'||substring(test1, 7,2)||' '||substring(test1, 9,2)||':'||substring(test1, 11,2)||':'||substring(test1, 13,2) ||substr(test1, 15) as timestamp) as tst1
FROM TEST.TESTTB

    Pls note, i have added last substring. Cast will convert yyyy-MM-dd hh:mm:ss.SSSS to a timestamp. Pls validate if it can convert whole milliseconds. If it cant, you need to trim number of milliseconds.