ToDate function provides unexpected output

Question

I used the ToDate(userinput, format) function to covert my chararray field. I used the ToDate(userinput, 'MM/dd/yyyy') to covert the field from chararray to date but looks like i am not seeing the output as i had expected.

Here is the code:

l_dat = load 'textfile' using PigStorage('|') as  (first:chararray,last:chararray,dob:chararray);

c_dat = foreach l_dat generate ToDate(dob,'MM/dd/yyyy') as mydate;
describe c_dat;
dump c_dat;

data looks like this:

(firstname1,lastname1,02/02/1967)
(John,deloy,05/26/1967)
(frank,fun,05/18/1967)

Output looks like this:

c_dat: {mydate: datetime}
(1967-05-26T00:00:00.000-04:00)
(1967-05-18T00:00:00.000-04:00)
(1967-02-02T00:00:00.000-05:00)

The output i was expecting was dateObjects with data as shown below:

(05/26/1967)
(05/18/1967)
(02/02/1967)

Please advise if i am doing anything wrong?

maybe add another for loop like they did on http://stackoverflow.com/questions/26933900/human-readable-string-date-converted-to-date-using-pig — NendoTaka, May 29 '15 at 19:40

Murali Rao · Accepted Answer · 2015-05-29T20:58:24.940

Ref : http://pig.apache.org/docs/r0.12.0/func.html#to-date, the return type of ToDate function is DateTime object. You can observe that in the schema description shared in output

c_dat: {mydate: datetime}

If you are having the date in the required format, you need not do any conversion.

c_dat = foreach l_dat generate dob as mydate;

If you are interested in converting the chararray date to any other format then you have to use ToString() function after getting the DateTime object.

  Step 1: Convert date chararray to Date Time Ojbect using ToDate(datesstring, inutformat)
  Step 2 : Use ToString(DateTime object, required format) to get the string date in the required format.

This can be achieved in a single step as below.

ToString(ToDate(date,inputformat),requiredformat);

Ref : http://pig.apache.org/docs/r0.12.0/func.html#to-string for details.

ToDate function provides unexpected output

1 Answers1