CODEWITHSUNDEEP
hadoophive
Shakile
Shakile

Reputation: 343

how to manage Date interval in hive

I'm new in Hive-Hadoop. I have some problem with Date interval management.

In Postgresql, I can get the "6 days" before a given date :

select max(datejour) + INTERVAL '-6 day' as maxdate from table

e.g : if max(datejour) = 2015-08-22 ==> my query returns 2015-08-15

Does somebody can help me on how could I do it in Hive?

thanks.

Upvotes: 3

Views: 19392

Answers (4)

s.polam
s.polam

Reputation: 10362

You can use Hive INTERVAL to achieve this.

select (max(datejour) - INTERVAL '6' DAY) as maxdate from table

Above query should return 2015-08-15

You can find more details - https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types

Upvotes: 3

Ankit Agrahari
Ankit Agrahari

Reputation: 379

Since updating records using UPDATE command is not possible in hive and adding columns through alter command is not recommended as you have to insert values in it through same table.

create external table test(
  fields1 string,
  field2 string)

create external table test(
  fields1 string,
  field2 string,
  h01 string
  )

Insert overwrite table table2 
select 
fields1,
field2,
case when fields1 = '' then 'OK' else 'KO' end as h01 from table1 where your_condition;

Upvotes: 0

Ankit Agrahari
Ankit Agrahari

Reputation: 379

You can use DATE_SUB function to get your requirement. Query may look like this(in your case):

select DATE_SUB(from_unixtime(unix_timestamp(cast(MAX(t1.max_date) AS string) ,'yyyy-MM-dd'), 'yyyy-MM-dd'), 6) from (select MAX(datejour) as max_date from table) t1 group by t1.max_date;

Upvotes: 1

Pradeep Bhadani
Pradeep Bhadani

Reputation: 4721

You can use Hive Date builtin function to achieve this

select date_sub('2015-08-22', 6) from table

Above query should return 2015-08-15

You can find more Hive built-in function here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

Hope his helps

Upvotes: 2

Related Questions