[ https://issues.apache.org/jira/browse/HIVE-3790?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13528836#comment-13528836 ]
Georgy B Abraham commented on HIVE-3790: ---------------------------------------- UDF would be useful in manipulating the day , month or year of any given date by the offset passed. eg: date_offset('2009-07-29', -1 ,'MONTH' ) would give us the date 2009-06-29. Should have to consider Leapyears Februvary scenario too > 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/timestamp. > 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