Hive DateTime Functions – UNIX_TIMESTAMP() and FROM_UNIXTIME()

In this article, we will address the frequently utilized Hive date and time functions that convert the date to BIGINT and from BIGINT to the string or the desired date time format.

UNIX_TIMESTAMP():
This function returns the current date and current date time in seconds. The return value will be in BIGINT.

Note: This function is not deterministic and its value is not set for the scope of the query execution and thus avoids proper optimization of queries-which has been discontinued after 2.0 in favor of the constant CURRENT TIMESTAMP.

Examples: 
SELECT UNIX_TIMESTAMP();
The usage of the above statement should be avoided as UNIX_TIMESTAMP(void) is deprecated. Either CURRENT_DATE or CURRENT_TIMESTAMP should be used.

However it still calculates and returns the number of seconds between Unix epoch (1970-01-01 00:00:00 UTC) and the CURRENT_TIMESTAMP.

SELECT UNIX_TIMESTAMP(CURRENT_DATE);
The above command returns the total number of seconds between Unix epoch to current date.

SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP);
The above command returns the total number of seconds between Unix epoch to current date and time (this means as of now).

FROM_UNIXTIME():
Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of “1970-01-01 00:00:00”. In other words, this function transforms the BIGINT value that has been converted from the date or date time back to the desired date (and time) format.

Examples:
SELECT FROM_UNIXTIME(1605898212);
Result:
2020-11-20 10:50:12

SELECT FROM_UNIXTIME(1605898235);
Result:
2020-11-20 10:50:35

SELECT FROM_UNIXTIME(1605898235, ‘dd/MM/yyyy hh:mm:ss’);
Result:
20/11/2020 10:50:35

SELECT FROM_UNIXTIME(1605898235, ‘yyyy-MM-dd’);
Result:
2020-11-20

SELECT FROM_UNIXTIME(1605898235, ‘yyyy:MM:dd’);
Result:
2020:11:20