Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

how to convert a date from different format to one particular formate in hive?

avatar
Contributor

i have a data in my HDFS,my date format in that file is Tue Dec 20 10:04:31 +0000 2016,when i create a hive table with field name created time and datatype date it store a null value. how to change that date formate in hive?

1 ACCEPTED SOLUTION

avatar
Super Guru

this looks a timestamp not date, you can store this as string in hive table and retrive it using to_date() funtion,

or you can run some date transformation before inserting into hive table, it looks you are having RFC822 timestamp which you can convert into some hive known transformation like this, I am using a java program to

public class RFC822TimeStampConverter {
    public static void main(String[] args) {
        String rfcDate = "Tue, Dec 20 10:04:31 2016";
        String pattern = "EEE, MMM DD HH:mm:ss YYYY";
        SimpleDateFormat format = new SimpleDateFormat(pattern);
        try {
            Date javaDate = format.parse(rfcDate);
            System.out.println(javaDate.toString());
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }
}

View solution in original post

1 REPLY 1

avatar
Super Guru

this looks a timestamp not date, you can store this as string in hive table and retrive it using to_date() funtion,

or you can run some date transformation before inserting into hive table, it looks you are having RFC822 timestamp which you can convert into some hive known transformation like this, I am using a java program to

public class RFC822TimeStampConverter {
    public static void main(String[] args) {
        String rfcDate = "Tue, Dec 20 10:04:31 2016";
        String pattern = "EEE, MMM DD HH:mm:ss YYYY";
        SimpleDateFormat format = new SimpleDateFormat(pattern);
        try {
            Date javaDate = format.parse(rfcDate);
            System.out.println(javaDate.toString());
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }
}