CODEWITHSUNDEEP
dateapache-pig
user4896486
user4896486

Reputation: 1

PigLatin-Finding MonthEnd date for a given date

In Pig Latin, is there a built-in function to find the Month End date for a given date ? For example, if the given date is '2015-03-15', the month end date returned should be '2015-03-31' and if given date is '2015-04-15', the month end date should be '2015-04-30'.

Upvotes: 0

Views: 763

Answers (2)

Zach Beniash
Zach Beniash

Reputation: 302

This is how you do it:

REGISTER /usr/lib/pig/piggybank.jar;

DEFINE ISOToMonth org.apache.pig.piggybank.evaluation.datetime.truncate.ISOToMonth();

%declare END_OF_MONTH SubtractDuration(AddDuration(ToDate(ISOToMonth('2015-03-15')),'P1M'),'P1D')

A = LOAD 'DummyFileWithOneRow.txt' USING PigStorage(',') AS (f1:chararray, f2:chararray);

result = FOREACH A GENERATE
        f1 AS f1,
        $END_OF_MONTH AS end_of_month;

DUMP result

The result of this run is: (1,2015-03-31T00:00:00.000Z).

  • You can now convert this result to your desired format.
  • You can do this calculation as part of the foreach on the loaded values.

Upvotes: 3

Mike Robinson
Mike Robinson

Reputation: 8945

The ordinary way to do such things, if you do not find that the language in question already has a built-in set of functions to "do such things," is to ... in this case:

  • Determine the first day of the current month. ("Month/01/Year" This is the only step that you "do by hand.")
  • Add "one month" to that. (There should be some kind of "DateAdd()" function in your language...)
  • Finally, using the same function, "subtract one day."

December 15th => December 1st => January 1st (of next year) => December 31st (of this year).

But first, look carefully. "Accountants want to do this sort of thing all the time." There is usually a pretty-good, sometimes very-good, set of functions to do date-manipulation. (And if they're not built-in to the language, there's often a contributed library of "goodies" that someone else already wrote and perfected.)

Upvotes: 1

Related Questions