Skip to content
Advertisement

HIVE converting unix timestamp for calculation

I’m trying to perform subtraction among timestamps and would like to convert the timestamps in forms that can be converted to minutes.

I used regexp_replace to convert timestamp in such form:

The following code will convert it to seconds

I have other two timestamps that I wish to convert to seconds, such as:

How should I convert these two timestamp to seconds by using regexp_replace() or any other functions?

Thank you!

Advertisement

Answer

First of all, unix_timestamp returns seconds passed from unix epoch. It does ignore milliseconds. This is why if you want epoch time in seconds, you can extract only 'yyyy-MM-dd HH:mm:ss'.

Second, If you have all these different formats in single dataset and you want to convert them all, you can use case statement to check pattern and convert accordingly:

Returns:

You can add more patterns to the case with corresponding conversion and in such way convert all possible cases. And of course, not necessarily all cases should use regex_replace for conversion. Though regex allows to identify and parse most complex strings.

Also you can try to convert using one pattern and if it returns null then try to convert using another pattern and so on:

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement