Search code examples
hadoopapache-pig

Apache Pig: Calculate number of days between a date and current date

I have a list of movies in the form (#,title,year,rating,duration):

1,The Nightmare Before Christmas,1993,3.9,4568
2,The Mummy,1932,3.5,4388
3,Orphans of the Storm,1921,3.2,9062
4,The Object of Beauty,1991,2.8,6150
5,Night Tide,1963,2.8,5126
6,One Magic Christmas,1985,3.8,5333
7,Muriel's Wedding,1994,3.5,6323
8,Mother's Boys,1994,3.4,5733
9,Nosferatu: Original Version,1929,3.5,5651
10,Nick of Time,1995,3.4,5333
...

I have the year in each tuple, which I need to treat it as 1st Jan of each year.

I need to calculate the number of days between this date and current date

My approach:

movies = LOAD 'movies_data.csv' USING PigStorage(',') as (id,name,year,rating,duration);
daysbetween_data = foreach movies generate DaysBetween(ToDate(year,'<WHAT FORMAT TO GIVE HERE>'), ToDate(<CURRENT DATE HERE>));

Any idea how to do this?

Solution

  • Load the year to a chararray field,Use CONCAT to append 01-01- to the year field so that you get the format 'MM-dd-yyyy' and then use the ToDate and DaysBetween.

    movies = LOAD 'movies_data.csv' USING PigStorage(',') as (id:int,name:chararray,year:chararray,rating:double,duration:int);
daysbetween_data = foreach movies generate DaysBetween(ToDate(CONCAT('01-01-',year),'MM-dd-yyyy'),CurrentTime());