how to convert UTC time to IST using pig

Question

I have a machine data comes into hdfs as below , the 8th field is UTC time(060037) , i need to convert it into IST and make the time format as hh:mm:ss using pig

VTS,01,0097,9739965515,NM,GP,20,060037,V,0000.0000,N,00000.0000,E,0.0,0.0,061114,0068,00,4000,00,999,149,9594
VTS,01,0097,9739965515,SP,GP,33,060113,V,0000.0000,N,00000.0000,E,0.0,0.0,061114,0068,00,4000,00,999,152,B927

using string function i tried to convert it into a unix date format now i am getting time like 2014-11-06 06:01:13 its in UTC format how to convert it into IST is there any inbuild functions available to do this?

A = LOAD '/user/hue/Anas' AS (line:chararray);
B = FOREACH A {
             splitRow = TOKENIZE(line,'+++');
             GENERATE FLATTEN(splitRow) AS newList;
          }
C = FOREACH B GENERATE FLATTEN(STRSPLIT(newList,',',23));
D = FILTER C BY $1==01;
E = foreach D generate $7 as time,$15 as date;
F = foreach E generate SUBSTRING(time,0,2) as hh,SUBSTRING(time,2,4) as mm,SUBSTRING(time,4,6) as ss,SUBSTRING(date,0,2) as date,SUBSTRING(date,2,4) as month,SUBSTRING(date,4,6) as year;
G = foreach F generate CONCAT('20',CONCAT(year,CONCAT('-',CONCAT(month,CONCAT('-',date))))) as date,CONCAT(hh,CONCAT(':',CONCAT(mm,CONCAT(':',ss)))) as time;
H = FOREACH G GENERATE CONCAT(date,CONCAT(' ',time)) AS UTC;
DUMP H;

1) Use Concat as CONCAT - All Caps, 2) Check if you can use inbuilt ToDate UDF to achieve your results, 3) You can also check other third party libs for similar UDF — mbaxi, Nov 06 '14 at 12:11
This discussion might be helpful for you http://stackoverflow.com/questions/19140126/pig-3rd-party-udf-clarification — mbaxi, Nov 06 '14 at 12:13
@AnasA and one other option is writing your own UDF which will do your conversion — Prasad Khode, Nov 06 '14 at 12:24

score 1 · Accepted Answer · answered Nov 15 '14 at 06:57

Please add the below 3 lines to your existing code, it will work

I = FOREACH H GENERATE ToDate(UTC,'yyyy-MM-dd HH:mm:ss','UTC') AS UTCTime;
J = FOREACH I GENERATE ToDate(ToString(UTCTime,'yyyy-MM-dd HH:mm:ss.SSSZ'),'yyyy-MM-dd HH:mm:ss.SSSZ','Asia/Kolkata') AS ISTTime;
DUMP J

Output of UTC time:

(2014-11-06 06:00:37)
(2014-11-06 06:01:13)

Output of IST time:

(2014-11-06T11:30:37.000+05:30)
(2014-11-06T11:31:13.000+05:30)

This ISTTime is in datetime object, now you can use all the build-in functions (GetDay(),GetTime() etc).

score 0 · Answer 2 · answered Nov 06 '14 at 12:20

Have you tried using piggybank?

I think that the format function will do a lot of what you want.

https://gist.github.com/griggheo/1780912

You will probably have to write your own UDF for the IST conversion but that can be done inline with python or ruby or ....

how to convert UTC time to IST using pig

2 Answers2

Linked