Extract year from timestamp with Spark SQL in WSO2 DAS

Question

In my hive scripts if I want to extract the year from timestamp I used this:

year(from_unixtime(cast(payload_fecha/1000 as BIGINT),'yyyy-MM-dd HH:mm:ss.SSS' )) as year

now I testing the new DAS snapshot and I want to do the same but I cannot use from_unixtime. So how can I do the same in spark SQL?

Take into account that I use WSO2 DAS, so I need a solution that work with this tool, not a generic solution for another environment.

Answer 1

I found the solution in this link:

http://thanu912.blogspot.com/2015/08/using-user-defined-function-udf-in.html

My code:

package org.softdevelop.spark.UDF;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;


public class TimeUDFSpark
{
    /*
    public static void main( String[] args ) throws ParseException {

        TimeUDFSpark timeUdf = new TimeUDFSpark();

        System.out.println("Time: " + timeUdf.getTime(timeUdf.current_time(0)) );
        System.out.println("Year: " + timeUdf.getYear(timeUdf.current_time(0)));
        System.out.println("Month: " + timeUdf.getMonth(timeUdf.current_time(0)));
        System.out.println("Day: " + timeUdf.getDay(timeUdf.current_time(0)));
        System.out.println("Hours: " + timeUdf.getHour(timeUdf.current_time(0)));
        System.out.println("Minutes: " + timeUdf.getMinute(timeUdf.current_time(0)));
        System.out.println("Seconds: " + timeUdf.getSeconds(timeUdf.current_time(0)));

    }
    */

    /**
     * Convert time(ms) to DateFormat
     *
     * @param timeStamp time in ms
     * @return date as String
     */
    public String getTime(Long timeStamp) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date(timeStamp.longValue());
        return sdf.format(date);
    }


    /**
     * Return the year from timeStamp
     *
     * @param timeStamp time in ms
     * @return year as String
     * @throws ParseException
     */
    public String getYear(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int year = cal.get(Calendar.YEAR);
        return String.valueOf(year);
    }

    /**
     * Return the month from timeStamp
     *
     * @param timeStamp time in ms
     * @return month as String
     * @throws ParseException
     */
    public String getMonth(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int month = cal.get(Calendar.MONTH) + 1;
        return String.valueOf(month);
    }

    /**
     * Return the day from timeStamp
     *
     * @param timeStamp in ms
     * @return day as String
     * @throws ParseException
     */
    public String getDay(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int day = cal.get(Calendar.DAY_OF_MONTH);
        return String.valueOf(day);
    }

    /**
     * Return the hour from timeStamp
     *
     * @param timeStamp in ms
     * @return hour as String
     * @throws ParseException
     */
    public String getHour(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int hour = cal.get(Calendar.HOUR);
        return String.valueOf(hour);
    }

    /**
     * Return the minutes from timeStamp
     *
     * @param timeStamp in ms
     * @return minutes as String
     * @throws ParseException
     */
    public String getMinute(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int minute = cal.get(Calendar.MINUTE);
        return String.valueOf(minute);
    }

    /**
     * return the seconds from timeStamp
     *
     * @param timeStamp in ms
     * @return seconds as String
     * @throws ParseException
     */
    public String getSeconds(Long timeStamp) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = sdf.parse(getTime(timeStamp));
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        int seconds = cal.get(Calendar.SECOND);
        return String.valueOf(seconds);
    }


    /**
     * Get the current time in ms
     *
     * @param param
     * @return
     */
    public long current_time(int param) {
        System.out.println(System.currentTimeMillis());
        return System.currentTimeMillis();
    }

}