to convert date from one format to another.

[#1]

I want to convert format of date from 01/05/2017 15:29:19.4980 to YYYY-MM-DD HH:MM:SS in pig.

kindly help.

[#2 · Accepted solution]

@manpreet kaur

this is a nice little riddle of a task. First of all, I had to figure out why I wasn't able to parse the string using Java as it was less obvious in Pig.

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author aervits
 */
public class NewClass1 {
 public static void main(String [] args) {

     String formatStr = "MM/dd/yyyy HH:mm:ss";
     SimpleDateFormat format = new SimpleDateFormat(formatStr);
     
     String dateStr = "01/05/2017 15:29:19.4980";
     
     try {
         Date formatted = format.parse(dateStr);
         System.out.println("Date: " + formatted);
         
         String formatStr1 = "yyyy-MM-dd HH:mm:ss";
         format = new SimpleDateFormat(formatStr1);
         String format2 = format.format(formatted);
         System.out.println("Date: " + format2);
     } catch (ParseException ex) {
         Logger.getLogger(NewClass.class.getName()).log(Level.SEVERE, ex.getMessage());
     }
 }   
}

Date: Thu Jan 05 15:29:19 EST 2017
Date: 2017-01-05 15:29:19

So I had to make two hoops to get it to the format you expect. Then I applied the same logic in Pig to get to this

a = load 'date' using PigStorage('.') as (dateStr:chararray);
b = foreach a generate ToDate(dateStr, 'MM/dd/yyyy HH:mm:ss') as (dateobj:DateTime);
c = foreach b generate ToString(dateobj, 'yyyy-MM-dd HH:mm:ss') as (dateStr2:chararray);

You can also rewrite the code in two lines

a = load 'date' using PigStorage('.') as (dateStr:chararray);
b = foreach a generate ToString(ToDate(dateStr, 'MM/dd/yyyy HH:mm:ss'), 'yyyy-MM-dd HH:mm:ss') as (dateStr2:chararray);

(2017-01-05 15:29:19)

The problem is in your string having millisecond precision, notice my load statement, I truncated it by using PigStorage('.'). Then I take your string and create a DateTime object from it with a SimpleDateFormat that understands your string, then I'm converting the DateTime object back to String with format I expect. I didn't spend a lot of time on this to figure out how to parse your String in one shot via Java and hence via Pig but it gets you at least where you want to be.