[jira] [Updated] (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:all-tabpanel
]
Jithin John updated HIVE-3790:
--
Fix Version/s: 0.9.1
Status: Patch Available (was: Open)
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
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] [Updated] (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:all-tabpanel
]
Jithin John updated HIVE-3790:
--
Status: Open (was: Patch Available)
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
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] [Updated] (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:all-tabpanel
]
Jithin John updated HIVE-3790:
--
Attachment: HIVE-3790.patch
Attaching patch for the same. Please review it..
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] [Updated] (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:all-tabpanel
]
Georgy B Abraham updated HIVE-3790:
---
Summary: UDF to introduce an OFFSET(day,month or year) for a given date or
timestamp (was: UDF to find DATE OFFSET for a given date or timestamp )
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
