Current timestamp in Hive with examples

Timestamp values in Hive

Timestamp values are required to capture the time along with date values. Hive provides few date functions to get the current timestamp values. The functions are current_timestamp() and unix_timestamp(). The format of the timestamp value is ‘YYYY-MM-DD HH:MM:SS.sss’.

Current_timestamp in Hive

The current_timestamp() returns the current time with the date value in Hive. Here the brackets() are optional. Both current_timestamp and current_timestamp() returns the same values.

current timestamp in Hive
current timestamp in Hive

Unix_timestamp in Hive

Unix_timestamp returns the current Unix timestamp in seconds. Here the brackets () are mandatory.

Unix timestamp in Hive
Unix timestamp in Hive

If we want to convert the seconds to readable timestamp format, we can use from_unixtime() function to do that. The function from_unixtime() is convert the seconds from unix epoch (1970-01-01 00:00:00 UTC) to a timestamp string.

From_unixtime in Hive
From_unixtime in Hive

Insert timestamp value in Hive table

Timestamp is one of the data type in Hive that we can define for the columns as below.

If we try to insert the current_timestamp value as below, it will throw an error in Hive.

FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values Expression of type TOK_FUNCTION not supported in insert/values

How to resolve this error? Only the select query returns the current timestamp value in Hive. So we need to use insert into select current_timestamp syntax that will resolve this error. In this case, we need one target table and one dummy table from which we will select the values with current_timestamp.

The dummy table test_server_actions contains the list of actions related to server such as login,logout,restart and so on.

Insert timestamp in Hive
Insert timestamp in Hive

Lets write the insert query to add server actions with timestamp into the target table test_server_log.

Output

The insert query got completed successfully and the current timestamp value is added in the target Hive table. Similarly we can use this method for other timestamp function from_unixtime(unix_timestamp()).

Example for insert current timestamp in Hive
Example for insert current timestamp in Hive