[jira] [Commented] (HIVE-21117) A day may belong to a different year than the week it is a part of

["Matthias (JIRA)" <ji...@apache.org>]

    [ 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)