[jira] [Commented] (HIVE-3790) UDF to introduce an OFFSET(day,month or year) for a given date or timestamp
[ https://issues.apache.org/jira/browse/HIVE-3790?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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 -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|00003|0.99|1.00|0.99 2012-12-02|110001|00008|0.99|0.00|0.00 2012-12-03|110001|00009|0.99|0.00|0.00 2012-12-04|110001|001112002|0.99|0.00|0.00 2012-12-05|110001|001112003|0.99|0.00|0.00 2012-12-06|110001|001112006|0.99|1.00|0.99 2012-12-07|110001|001112007|0.99|0.00|0.00 2012-12-08|110001|001112008|0.99|0.00|0.00 2012-12-09|110001|001112009|0.99|0.00|0.00 2012-12-10|110001|001112010|0.99|0.00|0.00 2012-12-11|110001|001113003|0.99|0.00|0.00 2012-12-12|110001|001113006|0.99|0.00|0.00 2012-12-13|110001|001113008|0.99|0.00|0.00 2012-12-14|110001|001113010|0.99|0.00|0.00 2012-12-15|110001|001114002|0.99|0.00|0.00 2012-12-16|110001|001114004|0.99|1.00|0.99 2012-12-17|110001|001114005|0.99|0.00|0.00 2012-12-18|110001|001121004|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)
[jira] [Commented] (HIVE-3790) UDF to introduce an OFFSET(day,month or year) for a given date or timestamp
[ https://issues.apache.org/jira/browse/HIVE-3790?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13752149#comment-13752149 ] Jithin John commented on HIVE-3790: --- could some one review this and provide comments? 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 -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|00003|0.99|1.00|0.99 2012-12-02|110001|00008|0.99|0.00|0.00 2012-12-03|110001|00009|0.99|0.00|0.00 2012-12-04|110001|001112002|0.99|0.00|0.00 2012-12-05|110001|001112003|0.99|0.00|0.00 2012-12-06|110001|001112006|0.99|1.00|0.99 2012-12-07|110001|001112007|0.99|0.00|0.00 2012-12-08|110001|001112008|0.99|0.00|0.00 2012-12-09|110001|001112009|0.99|0.00|0.00 2012-12-10|110001|001112010|0.99|0.00|0.00 2012-12-11|110001|001113003|0.99|0.00|0.00 2012-12-12|110001|001113006|0.99|0.00|0.00 2012-12-13|110001|001113008|0.99|0.00|0.00 2012-12-14|110001|001113010|0.99|0.00|0.00 2012-12-15|110001|001114002|0.99|0.00|0.00 2012-12-16|110001|001114004|0.99|1.00|0.99 2012-12-17|110001|001114005|0.99|0.00|0.00 2012-12-18|110001|001121004|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
[jira] [Commented] (HIVE-3790) UDF to introduce an OFFSET(day,month or year) for a given date or timestamp
[ https://issues.apache.org/jira/browse/HIVE-3790?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13530658#comment-13530658 ] Jithin John commented on HIVE-3790: --- hi Sun Rui, currently we could acheive the scenario by nesting the fuction like date_offset(date_offset(date,1,'year'),3,'month') we could consider whether SQL inteval type is possible or not? 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 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 -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|00003|0.99|1.00|0.99 2012-12-02|110001|00008|0.99|0.00|0.00 2012-12-03|110001|00009|0.99|0.00|0.00 2012-12-04|110001|001112002|0.99|0.00|0.00 2012-12-05|110001|001112003|0.99|0.00|0.00 2012-12-06|110001|001112006|0.99|1.00|0.99 2012-12-07|110001|001112007|0.99|0.00|0.00 2012-12-08|110001|001112008|0.99|0.00|0.00 2012-12-09|110001|001112009|0.99|0.00|0.00 2012-12-10|110001|001112010|0.99|0.00|0.00 2012-12-11|110001|001113003|0.99|0.00|0.00 2012-12-12|110001|001113006|0.99|0.00|0.00 2012-12-13|110001|001113008|0.99|0.00|0.00 2012-12-14|110001|001113010|0.99|0.00|0.00 2012-12-15|110001|001114002|0.99|0.00|0.00 2012-12-16|110001|001114004|0.99|1.00|0.99 2012-12-17|110001|001114005|0.99|0.00|0.00 2012-12-18|110001|001121004|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
[jira] [Commented] (HIVE-3790) UDF to introduce an OFFSET(day,month or year) for a given date or timestamp
[ https://issues.apache.org/jira/browse/HIVE-3790?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13530666#comment-13530666 ] Jithin John commented on HIVE-3790: --- hi georgy, since we are using java calendar , leap year scenario will work. 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 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 -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|00003|0.99|1.00|0.99 2012-12-02|110001|00008|0.99|0.00|0.00 2012-12-03|110001|00009|0.99|0.00|0.00 2012-12-04|110001|001112002|0.99|0.00|0.00 2012-12-05|110001|001112003|0.99|0.00|0.00 2012-12-06|110001|001112006|0.99|1.00|0.99 2012-12-07|110001|001112007|0.99|0.00|0.00 2012-12-08|110001|001112008|0.99|0.00|0.00 2012-12-09|110001|001112009|0.99|0.00|0.00 2012-12-10|110001|001112010|0.99|0.00|0.00 2012-12-11|110001|001113003|0.99|0.00|0.00 2012-12-12|110001|001113006|0.99|0.00|0.00 2012-12-13|110001|001113008|0.99|0.00|0.00 2012-12-14|110001|001113010|0.99|0.00|0.00 2012-12-15|110001|001114002|0.99|0.00|0.00 2012-12-16|110001|001114004|0.99|1.00|0.99 2012-12-17|110001|001114005|0.99|0.00|0.00 2012-12-18|110001|001121004|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
[jira] [Commented] (HIVE-3790) UDF to introduce an OFFSET(day,month or year) for a given date or timestamp
[ https://issues.apache.org/jira/browse/HIVE-3790?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13529609#comment-13529609 ] Sun Rui commented on HIVE-3790: --- Have you considered adding support to the SQL interval type? So, for example, if you want to add '1 year 3 month' to a date, you don't have to have two calls, first is to call date1=date_offset(date, 1, 'YEAR'), then to call date_offset (date1,3, 'MONTH'). 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 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 -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|00003|0.99|1.00|0.99 2012-12-02|110001|00008|0.99|0.00|0.00 2012-12-03|110001|00009|0.99|0.00|0.00 2012-12-04|110001|001112002|0.99|0.00|0.00 2012-12-05|110001|001112003|0.99|0.00|0.00 2012-12-06|110001|001112006|0.99|1.00|0.99 2012-12-07|110001|001112007|0.99|0.00|0.00 2012-12-08|110001|001112008|0.99|0.00|0.00 2012-12-09|110001|001112009|0.99|0.00|0.00 2012-12-10|110001|001112010|0.99|0.00|0.00 2012-12-11|110001|001113003|0.99|0.00|0.00 2012-12-12|110001|001113006|0.99|0.00|0.00 2012-12-13|110001|001113008|0.99|0.00|0.00 2012-12-14|110001|001113010|0.99|0.00|0.00 2012-12-15|110001|001114002|0.99|0.00|0.00 2012-12-16|110001|001114004|0.99|1.00|0.99 2012-12-17|110001|001114005|0.99|0.00|0.00 2012-12-18|110001|001121004|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