1

I have a date column as a string data type in MMMM Do YYYY, HH:mm:ss.SSS

(December 16th 2019, 21:30:22.000) format.

I'm trying to convert this into a timestamp data type in hive but couldn't able to achieve it because this format is not available in unixtime.

Is there any way to convert this in hive?

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Sathyaraj
  • 189
  • 6
  • 21

2 Answers2

1

This method will preserve millisecond precision. First extract only parts compatible with SimpleDateFormat pattern using regex, then convert to datetime, concat with milliseconds (milliseconds lost after unix_timestamp conversion) and convert to timestamp:

select timestamp(concat(from_unixtime(unix_timestamp(dt,'MMM dd yyyy HH:mm:ss.SSS')),'.',split(dt,'\\.')[1]))
from
(select regexp_replace('December 16th 2019, 21:30:22.001','([A-Za-z]+ \\d{1,2})[a-z]{0,2} (\\d{4}), (\\d{2}:\\d{2}:\\d{2}\\.\\d+)','$1 $2 $3') as dt --returns December 16 2019 21:30:22.001
) s;
OK
2019-12-16 21:30:22.001
Time taken: 0.09 seconds, Fetched: 1 row(s)
leftjoin
  • 36,950
  • 8
  • 57
  • 116
0

Try this

SELECT from_unixtime(unix_timestamp) as new_timestamp from data ...

That converts a unix timestamp into a YYYY-MM-DD HH:MM:SS format, then you can use the following functions to get the year, month, and day:

SELECT year(new_timestamp) as year, month(new_timestamp) as month, day(new_timestamp) as day

Putti
  • 37
  • 4
  • I need to convert the given timestamp(MMMM Do YYYY, HH:mm:ss.SSS) to unix timestamp. Your function won't work in this case. – Sathyaraj Dec 19 '19 at 04:09