hive equivalent for TD_WEEK_OF_CALENDAR

Question

I am trying to implement a query already written for Teradata in Hive and was using weekofyear() until now as a replacement of TD_WEEK_OF_CALENDAR method which returns an INTEGER value representing the number of full weeks since and including the week of 01/01/1900, where the first partial week is 0.

I couldn't find any other predefined UDF in Hive related to this method. Even for writing a custom UDF in Java, I was not able to get the exact logic of TD_WEEK_OF_CALENDAR.

Can someone please help on this?

possible duplicate of [Hive date function to achieve day of week](http://stackoverflow.com/questions/22982904/hive-date-function-to-achieve-day-of-week) — D3V, Apr 27 '15 at 20:28
Thought someone who knows the functionality of TD_WEEK_OF_CALENDAR would have answered. It calculates the total number of COMPLETE weeks between 1900-01-01 and the date string given to it as parameter. The method given in the provided link only takes difference of days, divides it by 7 and gives the result which wont be an exact match to the answer — sunil kancharlapalli, Apr 27 '15 at 21:21

score 0 · Answer 1 · edited Mar 06 '17 at 17:38

This can be achieved using the Joda time functions. But Hive doesn't support Joda time Jars and you need to explicitly add the joda-time jars to your hive lib folder.

The function TD_WEEK_OF_CALENDAR treats Sunday as first day of week and Saturday as last whereas the joda-time function getDayOfWeek() treats Sunday as last day of the week giving its number as 7 which pulls Sunday into the same week.

This below code would to the needful

public Text evaluate(Text input) {
    if(null != input){
        String date = input.toString();
        StringTokenizer st = new StringTokenizer(date, "-");
        int year = Integer.parseInt(st.nextToken());
        int month = Integer.parseInt(st.nextToken());
        int day = Integer.parseInt(st.nextToken());
        DateTime dateTime1 = new DateTime(1900, 1, 1, 0, 0, 0, 0);
        DateTime dateTime2 = new DateTime(year, month, day, 0, 0, 0, 0);
        int weeksDiff = dateTime2.getDayOfWeek() == 7 ? Weeks.weeksBetween(
                dateTime1, dateTime2).getWeeks() + 1 : Weeks.weeksBetween(
                dateTime1, dateTime2).getWeeks();
        String weeks = weeksDiff + "";
        return new Text(weeks);
} else {
    return null;
}

hive equivalent for TD_WEEK_OF_CALENDAR

1 Answers1