CODEWITHSUNDEEP
hivehiveql
Elingela
Elingela

Reputation: 819

How to select current date in Hive SQL

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.

Upvotes: 79

Views: 315654

Answers (6)

Lukas Vermeer
Lukas Vermeer

Reputation: 5940

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()).

Upvotes: 93

DrSD
DrSD

Reputation: 151

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.

Upvotes: 11

Saranga
Saranga

Reputation: 144

To extract the year from current date 

SELECT YEAR(CURRENT_DATE())

IBM Netezza 

extract(year from now())

HIVE 

SELECT YEAR(CURRENT_DATE())

Upvotes: 7

Aditya
Aditya

Reputation: 2415

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;

Upvotes: 21

Aniket Asati
Aniket Asati

Reputation: 561

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 :)

Upvotes: 56

Gunnar Tångring
Gunnar Tångring

Reputation: 395

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

Upvotes: 23

Related Questions