Section 9:100. Date Manipulation – Extracting information using Year,month,day, etc

In Hive, you can work with Unix timestamps using the built-in from_unixtime() and unix_timestamp() functions.

The unix_timestamp() function converts a string to a Unix timestamp, which is the number of seconds since January 1, 1970, 00:00:00 UTC. The syntax of the unix_timestamp() function is as follows:

unix_timestamp(string date, string pattern)

Here, date is the input date string, and pattern is the format string used to parse the input string. If the pattern argument is not specified, the function assumes the input string is in the default format ‘yyyy-MM-dd HH:mm:ss’.

For example, to convert the string ‘2022-03-05 15:30:00’ to a Unix timestamp, you can use the following 

SELECT unix_timestamp(‘2022-03-05 15:30:00’, ‘yyyy-MM-dd HH:mm:ss’);

This will return the integer value 1646499000, which is the Unix timestamp for ‘2022-03-05 15:30:00’.

The from_unixtime() function does the opposite: it converts a Unix timestamp to a string representation of the corresponding date and time. The syntax of the from_unixtime() function is as follows:

from_unixtime(bigint unixtime, string format)

Here, unixtime is the input Unix timestamp, and format is the format string used to format the output string. If the format argument is not specified, the function returns the timestamp in the default format ‘yyyy-MM-dd HH:mm:ss’.

For example, to convert the Unix timestamp 1646499000 to a string representation of the corresponding date and time, you can use the following query:

SELECT from_unixtime(1646499000, ‘yyyy-MM-dd HH:mm:ss’);

This will return the string ‘2022-03-05 15:30:00’.

You can use these functions to perform various operations on Unix timestamps, such as adding or subtracting time intervals or comparing timestamps. For example, to add 1 day to a Unix timestamp, you can use the following 

SELECT from_unixtime(unix_timestamp(‘2022-03-05 15:30:00’, ‘yyyy-MM-dd HH:mm:ss’) + 86400, ‘yyyy-MM-dd HH:mm:ss’);

This will add 86400 seconds (1 day) to the Unix timestamp for ‘2022-03-05 15:30:00’, resulting in the string ‘2022-03-06 15:30:00