Hive equivalent of Teradata statement

Question

I am trying to convert a Teradata query to Hive

WHERE visit_date BETWEEN (CURRENT_DATE-194) AND  (CURRENT_DATE)

where visit_date is a string of format yyyy-mm-dd.

CURRENT_DATE is valid in Hive but CURRENT_DATE-194 is giving error.

How can I do it in Hive?

use `unix_timestamp()` to compare dates – Vikrant Dec 28 '16 at 06:47 — Vikrant, Dec 28 '16 at 06:47

score 1 · Answer 1 · answered Dec 28 '16 at 07:15

1

Got the solution by using

visit_date BETWEEN date_sub(CURRENT_DATE,194) AND CURRENT_DATE

answered Dec 28 '16 at 07:15

ayush nigam

Right, unix_timestamp() is non-deterministic and prevents query from being optimized. You solution is better. See also: http://stackoverflow.com/a/41140298/2700344 – leftjoin Dec 28 '16 at 09:08

Vikrant · Answer 2 · 2016-12-28T07:11:51.000

0

To get data of past 194 days in Hive;
Try below query:

select * from table_1 where visit_date > date_sub(from_unixtime(unix_timestamp()), 194);

Note: TIMESTAMP is milliseconds
unix_timestamp is in seconds

edited Dec 28 '16 at 07:11

answered Dec 28 '16 at 06:53

Vikrant

2 Answers2