4

How I can substract a number of days of a date, having as a result another date, for example: 01/12/2016 - 10 = 21/11/2016

David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
Diego Arias
  • 93
  • 2
  • 6
  • Where? Like in a HiveQL query? – TehTris Feb 21 '17 at 23:18
  • 1
    Welcome to SO! Folks here are excited and eager to help you with your question but it's important to help them help you. Consider building a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). It's important because it shows what you've already tried, your thought process, and what you're hoping to achieve. Also consider using [backticks](http://stackoverflow.com/editing-help#comment-formatting) for code sections, it increases readability. For example `41 + 1 = 42` is easier to read than 41 + 1 = 42. Again welcome and good skills moving forward :) – mbigras Feb 21 '17 at 23:27

2 Answers2

3

(date argument)

hive> select date_sub(date '2016-12-01',10);
OK
2016-11-21

or

(string argument)

hive> select date_sub('2016-12-01',10);
OK
2016-11-21

date_sub(date/timestamp/string startdate, tinyint/smallint/int days)

Subtracts a number of days to startdate: date_sub('2008-12-31', 1) = '2008-12-30'. Prior to Hive 2.1.0 (HIVE-13248) the return type was a String because no Date type existed when the method was created.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
1

there exist a hive udf to substract days to the hive datehttps://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions, you have two options, transform your date to the following format to use the udf directly 

yyyy-MM-dd

or you can transform your current date to timestamp and apply the udf, for example

date_sub(from_unixtime(unix_timestamp('12/03/2010' , 'dd/MM/yyyy')), 10) -- subs 10 days

I hope it helps, regards!

hlagos
  • 7,690
  • 3
  • 23
  • 41