You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@pig.apache.org by Krishnan Narayanan <kr...@gmail.com> on 2013/10/07 22:02:34 UTC
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
Re: Need to get date from datetime.
Posted by Krishnan Narayanan <kr...@gmail.com>.
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 <St...@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.smile@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
>
RE: Need to get date from datetime.
Posted by Steve Bernstein <St...@deem.com>.
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.smile@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
RE: Need to get date from datetime.
Posted by Steve Bernstein <St...@deem.com>.
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.
____________________________________________________________
Steve Bernstein | VP, Analytics | Rearden Commerce, Inc. | 408.499.0961
-----Original Message-----
From: Krishnan Narayanan [mailto:krishnan.smile@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