How to select current date in Hive SQL

Question

How do we get the current system date in Hive? In MySQL we have select now(), can any one please help me to get the query results. I am very new to Hive, is there a proper documentation for Hive that gives the details information about the pseudo columns, and built-in functions.

Answer 1

According to the LanguageManual, you can use unix_timestamp() to get the "current time stamp using the default time zone." If you need to convert that to something more human-readable, you can use from_unixtime(unix_timestamp()).

Hope that helps.

Answer 2

Yes... I am using Hue 3.7.0 - The Hadoop UI and to get current date/time information we can use below commands in Hive:

SELECT from_unixtime(unix_timestamp()); --/Selecting Current Time stamp/

SELECT CURRENT_DATE; --/Selecting Current Date/

SELECT CURRENT_TIMESTAMP; --/Selecting Current Time stamp/

However, in Impala you will find that only below command is working to get date/time details:

SELECT from_unixtime(unix_timestamp()); --/Selecting Current Timestamp /

Hope it resolves your query :)

Answer 3

To fetch only current date excluding time stamp:

in lower versions, looks like hive CURRENT_DATE is not available, hence you can use (it worked for me on Hive 0.14)

select TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()));

In higher versions say hive 2.0, you can use :

select CURRENT_DATE;

Answer 4

The functions current_date and current_timestamp are now available in Hive 1.2.0 and higher, which makes the code a lot cleaner.

Answer 5

select from_unixtime(unix_timestamp(current_date, 'yyyyMMdd'),'yyyy-MM-dd');

current_date - current date

yyyyMMdd - my systems current date format;

yyyy-MM-dd - if you wish to change the format to a diff one.

Answer 6

To extract the year from current date

SELECT YEAR(CURRENT_DATE())

IBM Netezza

extract(year from now())

HIVE

SELECT YEAR(CURRENT_DATE())