Apache Hive: Query Current Date and Time

The current date and time in Hive can be queried using the following functions:

CURRENT_DATE(): This function returns the current date in the default Hive date format, which is YYYY-MM-DD.
CURRENT_TIMESTAMP(): This function returns the current date and time in the default Hive timestamp format, which is YYYY-MM-DD HH:MM:SS.SSS.
FROM_UNIXTIME(): This function returns the date and time in custom format.

CURRENT_DATE()

The following is an example of how to query the current date in Hive:

SELECT CURRENT_DATE();

This query will return the current date in the default Hive date format, which is YYYY-MM-dd (eg: 2023-08-17)

CURRENT_TIMESTAMP()

The following is an example of how to query the current date and time in Hive:

SELECT CURRENT_TIMESTAMP();

This query will return the current date and time in the default Hive timestamp format, which is YYYY-MM-DD HH:MM:SS.SSS (eg: 2023-08-17 06:51:40.985)

FROM_UNIXTIME()

You can also specify the format of the output date and timestamp using the FROM_UNIXTIME()function.

In Hive’s FROM_UNIXTIME function, the format specifiers for date formatting are similar to those used in the Java SimpleDateFormat class. Here are the common format specifiers you can use:

yy or yyyy: Year (2 or 4 digits)
MM: Month (01 to 12)
dd: Day of the month (01 to 31)
HH: Hour in 24-hour format (00 to 23)
mm: Minutes (00 to 59)
ss: Seconds (00 to 59)
SSS: Milliseconds
EEE: Day of the week (short name, e.g., “Mon”, “Tue”)
EEEE: Day of the week (full name, e.g., “Monday”, “Tuesday”)
a: AM/PM marker

Date in Custom Format:

For example, the following query will return the current date in the format YYYY-MM-dd (e.g.: 2023-08-17)

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(current_date), 'YYYY-MM-dd');

The following query will return the current date in the format dd-MM-YY (e.g.: 17-08-23)

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(current_date), 'dd-MM-YY');

The following query will return the current date along with the day in the format dd-MM-YY EEE (e.g.: 17-08-23 Thu)

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(current_date), 'dd-MM-YY EEE');

The FROM_UNIXTIME function provides different format specifiers, but for current_date, only a few are applicable: year (yy or yyyy), month (MM), date (dd), and day (EEE or EEEE). Using format specifiers that aren’t relevant to current_date, such as hour (HH), will result in zero-filled values.

In the example below, HH-mm-ss values are replaced with zeros (e.g.: 17-08-23 00-00-00).

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(current_date), 'dd-MM-YY HH-mm-ss');

To obtain the time in custom format, refer to the below section.

Time in Custom Format:

You can specify the format of the timestamp using the FROM_UNIXTIME() and UNIX_TIMESTAMP() functions.

The following query will return the current date and time in the format HH:MM:SS (eg: 2023-08-17 06:52:47).

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd HH:mm:ss');