Search code examples
datetimeapache-pigto-datelatin

How to convert chararray to datetime with milliseconds in pig latin

I wish to convert following value which is a chararray in pig

2016-05-11 23:59:57.628197

to

2016-05-11T23:59:57.628-05:00

How can I do it ?

Following is what I tried considering alias 'a2' contains list of datetime values in chararray in the column named 'input_date_value' 

FOREACH a2 GENERATE input_date_value AS input_date:chararray,
                       ToDate(input_date_value,'YYYY-MM-DD HH:mm:ss.SSSSSS') AS modification_datetime:datetime;

For input -

2002-07-11 16:58:40.249764

Output is -

2002-01-11T16:58:40.249-05:00

The month values like '07' are not getting picked up, The created timestamp has month set to 01' i.e. January everytime for all dates.

Can someone help. What am I doing wrong ?

Solution

  • Use lowercase character d instead of uppercase D for parsing date values.

    Now, I have managed to fix it myself on (In Pig 0.11)

    Apparently Pig 0.11 does not support the date format components I used earlier for parsing the month and date.

    I found below inference which hints on the incompatibility as mentioned https://www.w3.org/TR/NOTE-datetime

    Use:

    'YYYY-MM-dd HH:mm:ss.SSSSSS'

    instead of 'YYYY-MM-DD HH:mm:ss.SSSSSS'

    It now gives correct output.

    Input:

    2001-11-28 16:04:49.22388

    Output:

    2001-11-28T16:04:49.223-05:00