计算除Hive外的星期天数 [英] Calculate number of days excluding sunday in Hive

问题描述

我有两个时间戳记作为输入.我想计算这些时间戳之间的小时差，不包括周日.

I have two timestamps as input. I want to calculate the time difference in hours between those timestamps excluding Sundays.

我可以使用蜂巢中的 datediff 函数获得天数.

I can get the number of days using datediff function in hive.

我可以使用 from_unixtime(unix_timestamp(startdate)，'EEEE')获取特定日期的日期.

I can get the day of a particular date using from_unixtime(unix_timestamp(startdate), 'EEEE').

但是我不知道如何关联这些功能来满足我的要求，或者还有其他简单的方法可以实现这一目标.

But I dont know how to relate those functions to achieve my requirement or is there any other easy way to achieve this.

预先感谢.

推荐答案

您可以编写一个自定义UDF，该UDF将包含日期的两列作为输入，并计算不包括星期日的日期之间的差额.

You can write one custom UDF which takes two columns containing the dates as inputs and counts the difference between the dates excluding sundays.

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;

public class IsoYearWeek extends UDF {

  public LongWritable evaluate(Text dateString,Text dateString1) throws ParseException { //takes the two columns as inputs
    SimpleDateFormat date = new SimpleDateFormat("dd/MM/yyyy");
/*  String date1 = "20/07/2016";
    String date2 = "28/07/2016";
*/  int count=0;

    List<Date> dates = new ArrayList<Date>();

    Date  startDate = (Date)date.parse(dateString.toString()); 
    Date  endDate = (Date)date.parse(dateString1.toString());
    long interval = 24*1000 * 60 * 60; // 1 hour in millis
    long endTime =endDate.getTime() ; // create your endtime here, possibly using Calendar or Date
    long curTime = startDate.getTime();
    while (curTime <= endTime) {
        dates.add(new Date(curTime));
        curTime += interval;
    }
    for(int i=0;i<dates.size();i++){
        Date lDate =(Date)dates.get(i);
        if(lDate.getDay()==0){
            count+=1;  //counts the number of sundays in between
        }
    }

        long days_diff = (endDate.getTime()-startDate.getTime())/(24 * 60 * 60 * 1000)-count; //displays the days difference excluding sundays
        return new LongWritable(days_diff);

  }

}

这篇关于计算除Hive外的星期天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！