[
https://issues.apache.org/jira/browse/HIVE-21117?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16740495#comment-16740495
]
Matthias commented on HIVE-21117:
---------------------------------
The issue can not only happen into one direction, but into the other direction
too. Here a rather simple query that demonstrates this:
{code:java}
SELECT
c.createddatetime,
year(c.createddatetime) as created_year,
month(c.createddatetime) as created_month,
weekofyear(c.createddatetime) as created_week,
if (weekofyear(c.createddatetime) = 1, TRUE, FALSE) as first_week,
if (weekofyear(c.createddatetime) > 50 AND MONTH(c.createddatetime) = 1,
TRUE, FALSE) as last_week,
if (weekofyear(c.createddatetime) = 1 AND MONTH(c.createddatetime) = 12,
year(c.createddatetime) + 1, IF(weekofyear(c.createddatetime) > 50 AND
MONTH(c.createddatetime) = 1, year(c.createddatetime) - 1,
year(c.createddatetime))) as year_of_week
FROM (
SELECT CAST('2018-12-31 17:37:38' AS TIMESTAMP) as createddatetime
UNION
SELECT CAST('2017-01-01 17:37:38' AS TIMESTAMP) as createddatetime
) as c
;
{code}
The last column is a way on how to work around the issue, here the output:
!image-2019-01-11-16-41-27-357.png!
> A day may belong to a different year than the week it is a part of
> ------------------------------------------------------------------
>
> Key: HIVE-21117
> URL: https://issues.apache.org/jira/browse/HIVE-21117
> Project: Hive
> Issue Type: New Feature
> Affects Versions: 2.3.4
> Reporter: Zoltan Ivanfi
> Priority: Major
>
> When using the year() and weekofyear() functions in a query, their result is
> 2018 and 1 (respectively) for the day '2018-12-31'.
> The year() function returns 2018 for the input '2018-12-31', because that day
> belongs to the year 2018.
> The weekofyear() functions returns 1 for the input '2018-12-31', because that
> day belongs to the first week of 2019.
> Both functions provide sensible results on their own, but when combined, the
> result is wrong, because '2018-12-31' does not belong to week 1 of 2018.
> I suggest adding a new function yearofweek() that would return 2019 for the
> input '2018-12-31' and adding a warning to the documentation of the
> weekofyear() function about this problem.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
