0

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 ?

nitinr708
  • 1,393
  • 2
  • 19
  • 29

2 Answers2

1

https://pig.apache.org/docs/r0.11.1/func.html#to-date ToDate takes SimpleDateFormat only supports milliseconds http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html

The -05:00 you see is the time zone ToDate is actually truncating to 3 digits as it supports only milliseconds

Vikas Madhusudana
  • 1,482
  • 1
  • 10
  • 20
  • Thank you for your answer Vikas. You mis-interpreted the question my friend. I know it will truncate the milliseconds to only three digits. The problem is with parsing the month value from chararray and generating it into datetime of the particular month only. Can you please revisit and provide an answer now ? – nitinr708 May 17 '16 at 10:18
  • Got it i think this has something to do with parsing in ToDate let me check and update – Vikas Madhusudana May 17 '16 at 10:27
  • Looks like a bug whatever month you pass it shows as 1 what i am having is pig .012 version what version you are having? – Vikas Madhusudana May 17 '16 at 11:19
  • I am using Pig 0.11 – nitinr708 May 17 '16 at 12:11
0

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

nitinr708
  • 1,393
  • 2
  • 19
  • 29