Get current unix_timestamp in Hive

Question

As the post How to select current date in Hive SQL, to get the current date in Hive, unix_timestamp can be used.

But I tried

select unix_timestamp();

and just,

unix_timestamp();

both give the error messages

FAILED: ParseException line 1:23 mismatched input '<EOF>' expecting FROM near ')' in from clause
FAILED: ParseException line 1:0 cannot recognize input near 'unix_timestamp' '(' ')'

respectively.

How can I use unix_timestamp properly in Hive?

Answer 1

As Hive doesn't expose a dual table, you may want to create a single lined table, and use that table for that kind of querys.

You'll then be able to execute queries like

select unix_timestamp() from hive_dual;

A workaround is to use any existing table, with a LIMIT 1 or a TABLESAMPLE clause, but, depending on the size of your table, it will be less efficient.

# any_existing_table contains 10 lines
# hive_dual contains 1 line

select unix_timestamp() from any_existing_table LIMIT 1; 
# Time taken: 17.492 seconds, Fetched: 1 row(s)
select unix_timestamp() from any_existing_table TABLESAMPLE(1 ROWS);
# Time taken: 15.273 seconds, Fetched: 1 row(s)

select unix_timestamp() from hive_dual ;
# Time taken: 16.144 seconds, Fetched: 1 row(s)
select unix_timestamp() from hive_dual LIMIT 1; 
# Time taken: 14.086 seconds, Fetched: 1 row(s)
select unix_timestamp() from hive_dual TABLESAMPLE(1 ROWS);
# Time taken: 16.148 seconds, Fetched: 1 row(s)

Update

No need to pass any table name and limit statement. Hive does support select unix_timestamp() now.

More details :

Does Hive have something equivalent to DUAL?

BLOG POST : dual table in hive

Answer 2

To get the date out of timestamp use to_date function.

Try the below

select to_date(FROM_UNIXTIME(UNIX_TIMESTAMP())) as time from table_name;