Mailing-List: contact dev-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hive.apache.org
Date: Thu, 11 Sep 2014 17:54:34 +0000 (UTC)
From: "ratnakar (JIRA)" <jira@apache.org>
To: hive-dev@hadoop.apache.org
Message-ID: <JIRA.12623350.1355217399000.11274.1410458074479@Atlassian.JIRA>
In-Reply-To: <JIRA.12623350.1355217399000@Atlassian.JIRA>
References: <JIRA.12623350.1355217399000@Atlassian.JIRA>
 <JIRA.12623350.1355217399281@arcas>
Subject: [jira] [Commented] (HIVE-3790) UDF to introduce an OFFSET(day,month
 or year) for a given date or timestamp
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/HIVE-3790?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14130390#comment-14130390 ] 

ratnakar commented on HIVE-3790:
--------------------------------

Could you someone update status on this function

> UDF to introduce an OFFSET(day,month or year) 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
>             Fix For: 0.9.1
>
>         Attachments: HIVE-3790.patch
>
>
> 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 was sent by Atlassian JIRA
(v6.3.4#6332)