[jira] [Created] (HIVE-3790) UDF to find DATE OFFSET for a given date or timestamp

["Jithin John (JIRA)" <ji...@apache.org>]

Jithin John created HIVE-3790:
---------------------------------

             Summary: UDF to find DATE OFFSET for a given date or timestamp 
                 Key: HIVE-3790
                 URL: https://issues.apache.org/jira/browse/HIVE-3790
             Project: Hive
          Issue Type: New Feature
          Components: UDF
    Affects Versions: 0.9.0
            Reporter: Jithin John


Current releases of Hive lacks a  generic function which would find the date offset to a date / timestamp. Current releases have date_add (date) and date_sub(date) which allows user to add or substract days only.But we could not use year or month as a unit.

    
The Function DATE_OFFSET(date,offset,unit) returns the date offset value from start_date according to the unit. Here the unit can be year , month and day.

The function could be used for date range queries and is more flexible than the existing functions.

Functionality :-

Function Name: DATE_OFFSET(date,offset,unit)
	 
Add a offset value to the unit part of the date.
Returns  the date in the format of "yyyy-MM-dd" .
Example: hive> select date_offset('2009-07-29', -1 ,'MONTH' ) FROM src LIMIT 1
-> 2009-06-29
Usage :-

Case  : To calculate the expiry date of a item from manufacturing date 
Table :- ITEM_TAB
 Manufacturing_date      |item id|store id    |value|unit|price
  2012-12-01|110001|0011111111003|0.99|1.00|0.99
  2012-12-02|110001|0011111111008|0.99|0.00|0.00
  2012-12-03|110001|0011111111009|0.99|0.00|0.00
  2012-12-04|110001|0011111112002|0.99|0.00|0.00
  2012-12-05|110001|0011111112003|0.99|0.00|0.00
  2012-12-06|110001|0011111112006|0.99|1.00|0.99
  2012-12-07|110001|0011111112007|0.99|0.00|0.00
  2012-12-08|110001|0011111112008|0.99|0.00|0.00
  2012-12-09|110001|0011111112009|0.99|0.00|0.00
  2012-12-10|110001|0011111112010|0.99|0.00|0.00
  2012-12-11|110001|0011111113003|0.99|0.00|0.00
  2012-12-12|110001|0011111113006|0.99|0.00|0.00
  2012-12-13|110001|0011111113008|0.99|0.00|0.00
  2012-12-14|110001|0011111113010|0.99|0.00|0.00
  2012-12-15|110001|0011111114002|0.99|0.00|0.00
  2012-12-16|110001|0011111114004|0.99|1.00|0.99
  2012-12-17|110001|0011111114005|0.99|0.00|0.00
  2012-12-18|110001|0011111121004|0.99|0.00|0.00 

QUERY:
select man_date , date_offset(man_date ,5 ,'year') as expiry_date from item_tab;


RESULT:
2012-12-01      2017-12-01
2012-12-02      2017-12-02
2012-12-03      2017-12-03
2012-12-04      2017-12-04
2012-12-05      2017-12-05
2012-12-06      2017-12-06
2012-12-07      2017-12-07
2012-12-08      2017-12-08
2012-12-09      2017-12-09
2012-12-10      2017-12-10
2012-12-11      2017-12-11
2012-12-12      2017-12-12
2012-12-13      2017-12-13
2012-12-14      2017-12-14
2012-12-15      2017-12-15
2012-12-16      2017-12-16
2012-12-17      2017-12-17
2012-12-18      2017-12-18



--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira