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.

score 93 · Answer 1 · edited Dec 24 '22 at 22:48

93

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

edited Dec 24 '22 at 22:48

starball

20,030
7
43
238

answered Jul 29 '13 at 09:30

Lukas Vermeer

5,920
2
16
19

3

Thank you Lukas, works perfectly, should be selected as the correct answer. – Waleed Asender Aug 26 '14 at 07:22
1

You can get complete information from https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF – GihanDB Aug 15 '17 at 04:41
1

Note that the current documentation entry (LanguageManual, 2019-03-20) for unix_timestamp() says "this has been deprecated since 2.0 in favour of CURRENT_TIMESTAMP constant." – Rob Garrison Mar 20 '19 at 15:55

score 55 · Answer 2 · edited Jun 21 '16 at 06:28

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

score 23 · Answer 3 · answered Dec 11 '15 at 10:23

23

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

answered Dec 11 '15 at 10:23

Gunnar Tångring

395
2
7

2

The Hive 1.2 in the distribution Cloudera doesn't contains the function current_date. However it contains the function unix_timestamp() – veve Jan 18 '16 at 19:37

Aditya · Answer 4 · 2016-08-06T02:49:38.160

21

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;

edited Aug 06 '16 at 02:49

answered Aug 03 '16 at 04:42

Aditya

2,385
18
25

just to mention that `current_date`is avialalble starting from Hive 1.2.0 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions – user1314742 Mar 27 '17 at 12:36

score 11 · Answer 5 · edited Jan 18 '18 at 16:24

11

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.

edited Jan 18 '18 at 16:24

picciano

22,341
9
69
82

answered Jan 18 '18 at 15:20

DrSD

151
2
12

This query worked for me: select from_unixtime(unix_timestamp(), 'yyyyMMddHHMMSS'); – TawabG Oct 14 '22 at 13:07

score 8 · Answer 6 · edited Jun 28 '17 at 06:59

8

To extract the year from current date

SELECT YEAR(CURRENT_DATE())

IBM Netezza

extract(year from now())

HIVE

SELECT YEAR(CURRENT_DATE())

edited Jun 28 '17 at 06:59

ImAtWar

1,073
3
11
23

answered Jun 28 '17 at 06:18

Saranga

154
1
3

2

has something changed since 2013? What version do you use now? Specify if something has changed. – ImAtWar Jun 28 '17 at 06:22

How to select current date in Hive SQL

6 Answers6

Linked