Perfect , it worked thanks steve.:) b = foreach a generate (SUBSTRING(trial_dt,0,10) == 'NULL' ? '1876-01-01' : CustomFormatToISO(SUBSTRING(trial_dt,0,10),'YYYY-MM-dd')) as trial_dt:chararray,(SUBSTRING(attach_dt,0,10) == 'NULL' ? '1876-01-01' : CustomFormatToISO(SUBSTRING(attach_dt,0,10),'YYYY-MM-dd')) as attach_dt:chararray;
c = FOREACH b GENERATE ISODaysBetween(attach_dt,trial_dt) as daydiff:int; illustrate c; ---------------------------------------------------------------------------- | a | id:int | trial_dt:chararray | attach_dt:bytearray | ---------------------------------------------------------------------------- | | 2 | 2013-09-25 12:01:25.2 | 2013-11-25 13:01:20.1 | ---------------------------------------------------------------------------- --------------------------------------------------------------- | b | trial_dt:chararray | attach_dt:chararray | --------------------------------------------------------------- | | 2013-09-25T00:00:00.000Z | 2013-11-25T00:00:00.000Z | --------------------------------------------------------------- ---------------------------- | c | daydiff:int | ---------------------------- | | 61 | ---------------------------- Thanks Krishnan On Mon, Oct 7, 2013 at 1:48 PM, Steve Bernstein <steve.bernst...@deem.com>wrote: > Oh, one more thing, per > http://joda-time.sourceforge.net/api-release/org/joda/time/format/DateTimeFormat.html, > your custom format ought to be: > 'YYYY-MM-dd' --lower case for day-of-month. DD means day of year. > > > -----Original Message----- > From: Steve Bernstein > Sent: Monday, October 07, 2013 1:44 PM > To: user@pig.apache.org > Subject: RE: Need to get date from datetime. > > Have you tried: > CustomFormatToISO(SUBSTRING(trial_dt,0,10),'YYYY-MM-DD') > > Assuming you care only about the date part, and not the time portion. > > -----Original Message----- > From: Krishnan Narayanan [mailto:krishnan.sm...@gmail.com] > Sent: Monday, October 07, 2013 1:03 PM > To: user@pig.apache.org > Subject: Need to get date from datetime. > > Hi, > > can some one help me getting date from date time.I need to find data > difference between two date. > > (1,2013-09-25 13:01:20.1) > (2,2013-09-25 12:01:25.2) > (3,NULL) > > a = load '/home/pig/sample.txt' using PigStorage(',') AS > (id:int,trial_dt:chararray); > > DEFINE CustomFormatToISO > org.apache.pig.piggybank.evaluation.datetime.convert.CustomFormatToISO(); > > b = foreach a generate > CustomFormatToISO(SUBSTRING(trial_dt,1,19),'YYYY-MM-DD') as > Trail_dt:chararray; > > dump b; > > Error: > > java.lang.Exception: java.lang.IllegalArgumentException: Invalid format: > "013-09-25 13:01:20" is malformed at " 13:01:20" > at > org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:404) > Caused by: java.lang.IllegalArgumentException: Invalid format: "013-09-25 > 13:01:20" is malformed at " 13:01:20" > at > > org.joda.time.format.DateTimeFormatter.parseDateTime(DateTimeFormatter.java:683) > > > Thanks > Krishnan >
