两个日期时间计算工作日，扣除周末，节假日，调休，节假日和周末重叠。
这个解决方法网上是没有，只是一些片段。以下以2017年全年为例。
准备工作将2017年的所有假期，调休，节假日和周末重叠的部分挑出来
2017年法定节假日统计 元旦: 2017.1.1---1.2 ###3天 1.1---重叠周末1 春节： 1.27，1.28，1.29，1.30，1.31，2.1，2.2 ###7天 1.28，1.29---重叠周末2 2月28天 清明节: 4.2,4.3,4.4 ###3天 4.2---重叠周末1 五一劳动节： 4.29，4.30，5.1 ###3天 4.29，4.30---重叠周末2 端午节: 5.28,5.29,5.30 ###3天 5.28---重叠周末1 国庆节中秋:10.1,10.2,10.3,10.4,10.5,10.6,10.7,10.8 ###8天 10.1,10.7,10.8---重叠周末3 接2018年元旦:12.30,12.31 ###3天 12.30,12.31---重叠周末2 !周末法定加班1.22,2.4,4.1,5.27,9.30,
2.创建一个数据表
写java代码运行并生成上面创建的数据表里数据。生成的数据是2017全年的周末，节假日，调休，节假日和周末重叠等
package yuanma; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.List; import java.util.UUID; /** * 将一年内的所有假日插入到假日表里 * @author ch * @time 2017-1-15 下午6:06:11 */ public class InsertHolidayUtil3 { public static void main(String[] args){ //驱动程序名 String driver = "com.mysql.jdbc.Driver"; //要插入的数据库，表 String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8"; String user = "root"; String password = ""; try { //加载驱动程序 Class.forName(driver); //连续MySQL 数据库 Connection conn = DriverManager.getConnection(url, user, password); if(!conn.isClosed()) System.out.println("Succeeded connecting to the Database!"); //statement用来执行SQL语句 Statement statement = conn.createStatement(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); java.util.Date start = sdf.parse("2017-01-01");//开始时间 java.util.Date end = sdf.parse("2017-12-31");//结束时间 List<Date> lists = dateSplit(start, end); //-------------------插入周末时间--------------- if (!lists.isEmpty()) { for (Date date : lists) { Calendar cal = Calendar.getInstance(); cal.setTime(date); if(cal.get(Calendar.DAY_OF_WEEK)==Calendar.SATURDAY||cal.get(Calendar.DAY_OF_WEEK)==Calendar.SUNDAY) { System.out.println("插入日期:" + sdf.format(date) + ",周末"); //--- String insertSql = "INSERT INTO pre_complain3 (id,object_type,CREATE_Time) VALUES(\'"+UUID.randomUUID()+"\',"+"\'周末\',\'"+sdf.format(date)+"\')"; statement.executeUpdate(insertSql); } } } //---------------插入节假日时间------------------ List<Days> holidays = new ArrayList<Days>(); // holidays.add(new Days(UUID.randomUUID().toString(),"元旦", sdf.parse("2017-01-01")));//--这种带//--是因为和周末重叠，所以去除 holidays.add(new Days(UUID.randomUUID().toString(),"元旦", sdf.parse("2017-01-02"))); //接2018年的元旦节日 holidays.add(new Days(UUID.randomUUID().toString(),"元旦", sdf.parse("2017-12-30"))); holidays.add(new Days(UUID.randomUUID().toString(),"元旦", sdf.parse("2017-12-31"))); holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2017-01-27"))); // holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2017-01-28")));//-- // holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2017-01-29")));//-- holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2017-01-30"))); holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2017-01-31"))); holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2017-02-01"))); holidays.add(new Days(UUID.randomUUID().toString(),"春节", sdf.parse("2017-02-02"))); holidays.add(new Days(UUID.randomUUID().toString(),"清明节", sdf.parse("2017-04-02"))); holidays.add(new Days(UUID.randomUUID().toString(),"清明节", sdf.parse("2017-04-03"))); // holidays.add(new Days(UUID.randomUUID().toString(),"清明节", sdf.parse("2017-04-04")));//-- // holidays.add(new Days(UUID.randomUUID().toString(),"劳动节", sdf.parse("2017-04-29")));//-- // holidays.add(new Days(UUID.randomUUID().toString(),"劳动节", sdf.parse("2017-04-30")));//-- holidays.add(new Days(UUID.randomUUID().toString(),"劳动节", sdf.parse("2017-05-01"))); // holidays.add(new Days(UUID.randomUUID().toString(),"端午节", sdf.parse("2017-05-28")));//-- holidays.add(new Days(UUID.randomUUID().toString(),"端午节", sdf.parse("2017-05-29"))); holidays.add(new Days(UUID.randomUUID().toString(),"端午节", sdf.parse("2017-05-30"))); // holidays.add(new Days(UUID.randomUUID().toString(),"中秋节", sdf.parse("2017-09-15"))); // holidays.add(new Days(UUID.randomUUID().toString(),"中秋节", sdf.parse("2017-09-16"))); // holidays.add(new Days(UUID.randomUUID().toString(),"中秋节", sdf.parse("2017-09-17"))); //中秋和国庆重叠 // holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2017-10-01")));//-- holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2017-10-02"))); holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2017-10-03"))); holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2017-10-04"))); holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2017-10-05"))); holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2017-10-06"))); // holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2017-10-07")));//-- // holidays.add(new Days(UUID.randomUUID().toString(),"国庆节", sdf.parse("2017-10-08")));//-- for(Days day:holidays) { //跟周末冲突的，不重复插入 String sql = "select count(1) as numbers from pre_complain3 where CREATE_Time =\'" + sdf.format(day.getDate()) + "\'"; //结果集 ResultSet rs = statement.executeQuery(sql); boolean hasRecord = false; while(rs.next()) { if(!"0".equals(rs.getString("numbers"))) { hasRecord = true; } } if(!hasRecord) { System.out.println("插入日期：" + sdf.format(day.getDate()) + "," + day.getTitle()); //---- String insertSql = "INSERT INTO pre_complain3 (id,object_type,CREATE_Time) VALUES(\'"+day.getId()+"\',"+"\'"+day.getTitle()+"\',\'"+sdf.format(day.getDate())+"\')"; statement.executeUpdate(insertSql); } } //-------------- 剔除补班时间(周末需要补班的)--------------------- List<Days> workDays = new ArrayList<Days>(); workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2017-01-22"))); workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2017-02-04"))); workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2017-04-01"))); workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2017-05-27"))); workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2017-09-30"))); // workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2017-10-08"))); // workDays.add(new Days(UUID.randomUUID().toString(),"补班", sdf.parse("2017-10-09"))); for(Days day:workDays) { System.out.println("剔除日期：" + sdf.format(day.getDate()) + "," + day.getTitle()); String delSql = "delete from pre_complain3 where CREATE_Time =\'" + sdf.format(day.getDate()) + "\'"; statement.executeUpdate(delSql); } conn.close(); } catch(ClassNotFoundException e) { System.out.println("Sorry,can\'t find the Driver!"); e.printStackTrace(); } catch(SQLException e) { e.printStackTrace(); } catch(Exception e) { e.printStackTrace(); } } private static List<Date> dateSplit(java.util.Date start, Date end) throws Exception { if (!start.before(end)) throw new Exception("开始时间应该在结束时间之后"); Long spi = end.getTime() - start.getTime(); Long step = spi / (24 * 60 * 60 * 1000);// 相隔天数 List<Date> dateList = new ArrayList<Date>(); dateList.add(end); for (int i = 1; i <= step; i++) { dateList.add(new Date(dateList.get(i - 1).getTime() - (24 * 60 * 60 * 1000)));// 比上一天减一 } return dateList; } }
4.打印两个时间段需要多少工作日
package yuanma3; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.List; public class GetYMD { public static void main(String[] args) throws ParseException {
//这里是数据库表生成的2017年的非工作日表。我是用数组的方式实现，这个比较简单 String[] str = { "2017-01-27", "2017-07-15", "2017-10-14", "2017-12-02", "2017-09-24", "2017-10-05", "2017-07-01", "2017-10-06", "2017-07-22", "2017-04-09", "2017-07-09", "2017-06-24", "2017-08-05", "2017-05-20", "2017-12-24", "2017-11-18", "2017-10-22", "2017-05-06", "2017-07-29", "2017-01-28", "2017-05-30", "2017-05-21", "2017-11-04", "2017-01-14", "2017-01-15", "2017-01-07", "2017-03-04", "2017-06-04", "2017-06-17", "2017-07-02", "2017-07-16", "2017-06-25", "2017-05-07", "2017-01-31", "2017-01-30", "2017-10-29", "2017-01-21", "2017-03-12", "2017-04-30", "2017-06-11", "2017-01-02", "2017-02-05", "2017-09-10", "2017-10-07", "2017-12-10", "2017-03-25", "2017-10-01", "2017-04-15", "2017-07-23", "2017-03-11", "2017-02-25", "2017-03-05", "2017-08-13", "2017-12-09", "2017-10-21", "2017-05-14", "2017-02-11", "2017-02-18", "2017-10-08", "2017-05-01", "2017-04-03", "2017-05-13", "2017-11-12", "2017-04-22", "2017-05-28", "2017-11-26", "2017-11-11", "2017-12-17", "2017-08-27", "2017-09-17", "2017-07-08", "2017-09-09", "2017-10-03", "2017-02-12", "2017-08-12", "2017-04-23", "2017-12-30", "2017-12-31", "2017-03-19", "2017-11-25", "2017-03-18", "2017-02-02", "2017-12-16", "2017-06-03", "2017-11-19", "2017-10-15", "2017-10-04", "2017-04-16", "2017-03-26", "2017-01-29", "2017-02-26", "2017-06-18", "2017-06-10", "2017-01-01", "2017-02-01", "2017-04-08", "2017-10-02", "2017-08-26", "2017-10-28", "2017-08-20", "2017-12-03", "2017-09-16", "2017-04-02", "2017-02-19", "2017-04-29", "2017-05-29", "2017-09-02", "2017-08-06", "2017-08-19", "2017-01-08", "2017-11-05", "2017-09-23", "2017-12-23", "2017-07-30", "2017-09-03" }; // -------------------------- Calendar cal = Calendar.getInstance(); String start = "2017-04-01"; String end = "2017-04-30"; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date dBegin = sdf.parse(start); Date dEnd = sdf.parse(end); List<Date> lDate = findDates(dBegin, dEnd); int de = lDate.size(); int de3 = de; System.out.println("de3" + de3); for (Date date : lDate) { int i = 1; // int de; for (String v : str) { if (v.equals(sdf.format(date))) { System.out.println("000:" + sdf.format(date)); de3 -= i; } } // System.out.println(i); System.out.println(sdf.format(date)); } System.out.println(de3); } public static List<Date> findDates(Date dBegin, Date dEnd) { List lDate = new ArrayList(); lDate.add(dBegin); Calendar calBegin = Calendar.getInstance(); // 使用给定的 Date 设置此 Calendar 的时间 calBegin.setTime(dBegin); Calendar calEnd = Calendar.getInstance(); // 使用给定的 Date 设置此 Calendar 的时间 calEnd.setTime(dEnd); // 测试此日期是否在指定日期之后 while (dEnd.after(calBegin.getTime())) { // 根据日历的规则，为给定的日历字段添加或减去指定的时间量 calBegin.add(Calendar.DAY_OF_MONTH, 1); lDate.add(calBegin.getTime()); } return lDate; } }
5.scala处理原型udf
import java.text.SimpleDateFormat import java.util.Calendar import org.apache.spark.SparkConf import org.apache.spark.sql.SparkSession import scala.collection.mutable.ArrayBuffer object date { def main(args: Array[String]) { //计算时间间隔 val conf = new SparkConf().setAppName("java_date").setMaster("local") val spark = SparkSession.builder().enableHiveSupport().config(conf).getOrCreate() //产生日期序列 import java.util.Calendar import java.util.Date import java.text.SimpleDateFormat import scala.collection.mutable.ListBuffer val startTime: String = args(0) val endTime: String = args(1) val dateFormat = new SimpleDateFormat("yyyy-MM-dd") val dateFiled: Int = Calendar.DAY_OF_MONTH var beginDate = dateFormat.parse(startTime) val endDate = dateFormat.parse(endTime) val calendar = Calendar.getInstance() calendar.setTime(beginDate) val str = Array("2017-01-27", "2017-07-15", "2017-10-14", "2017-12-02", "2017-09-24", "2017-10-05", "2017-07-01", "2017-10-06", "2017-07-22", "2017-04-09", "2017-07-09", "2017-06-24", "2017-08-05", "2017-05-20", "2017-12-24", "2017-11-18", "2017-10-22", "2017-05-06", "2017-07-29", "2017-01-28", "2017-05-30", "2017-05-21", "2017-11-04", "2017-01-14", "2017-01-15", "2017-01-07", "2017-03-04", "2017-06-04", "2017-06-17", "2017-07-02", "2017-07-16", "2017-06-25", "2017-05-07", "2017-01-31", "2017-01-30", "2017-10-29", "2017-01-21", "2017-03-12", "2017-04-30", "2017-06-11", "2017-01-02", "2017-02-05", "2017-09-10", "2017-10-07", "2017-12-10", "2017-03-25", "2017-10-01", "2017-04-15", "2017-07-23", "2017-03-11", "2017-02-25", "2017-03-05", "2017-08-13", "2017-12-09", "2017-10-21", "2017-05-14", "2017-02-11", "2017-02-18", "2017-10-08", "2017-05-01", "2017-04-03", "2017-05-13", "2017-11-12", "2017-04-22", "2017-05-28", "2017-11-26", "2017-11-11", "2017-12-17", "2017-08-27", "2017-09-17", "2017-07-08", "2017-09-09", "2017-10-03", "2017-02-12", "2017-08-12", "2017-04-23", "2017-12-30", "2017-12-31", "2017-03-19", "2017-11-25", "2017-03-18", "2017-02-02", "2017-12-16", "2017-06-03", "2017-11-19", "2017-10-15", "2017-10-04", "2017-04-16", "2017-03-26", "2017-01-29", "2017-02-26", "2017-06-18", "2017-06-10", "2017-01-01", "2017-02-01", "2017-04-08", "2017-10-02", "2017-08-26", "2017-10-28", "2017-08-20", "2017-12-03", "2017-09-16", "2017-04-02", "2017-02-19", "2017-04-29", "2017-05-29", "2017-09-02", "2017-08-06", "2017-08-19", "2017-01-08", "2017-11-05", "2017-09-23", "2017-12-23", "2017-07-30", "2017-09-03") val dateArray: ArrayBuffer[String] = ArrayBuffer() while (beginDate.compareTo(endDate) <= 0) { dateArray += dateFormat.format(beginDate) calendar.add(dateFiled, 1) beginDate = calendar.getTime } val ldate = dateArray.toList // for (v <- str) println(endDate) var de3: Int = ldate.size System.out.println("de3" + de3) import scala.collection.JavaConversions._ for (date <- ldate) { val i: Int = 1 // int de; for (v <- str) { if (dateFormat.parse(v) == dateFormat.parse(date)) { System.out.println("000:" + dateFormat.parse(date)) de3-=i } } // System.out.println(i); //System.out.println(dateFormat.parse(date)) } System.out.println(de3+"个工作日") } }
6.scala的udf函数
//自定义函数计算工作日 def udf(startTime: String, endTime: String) = {//----------- import java.text.SimpleDateFormat import java.util.Calendar val stringDateBegin: String = startTime val stringDateEnd: String = endTime val dateFormat = new SimpleDateFormat("yyyy-MM-dd") val dateFiled: Int = Calendar.DAY_OF_MONTH var beginDate = dateFormat.parse(stringDateBegin) val endDate = dateFormat.parse(stringDateEnd) val calendar = Calendar.getInstance() calendar.setTime(beginDate) val str = Array("2017-01-27", "2017-07-15", "2017-10-14", "2017-12-02", "2017-09-24", "2017-10-05", "2017-07-01", "2017-10-06", "2017-07-22", "2017-04-09", "2017-07-09", "2017-06-24", "2017-08-05", "2017-05-20", "2017-12-24", "2017-11-18", "2017-10-22", "2017-05-06", "2017-07-29", "2017-01-28", "2017-05-30", "2017-05-21", "2017-11-04", "2017-01-14", "2017-01-15", "2017-01-07", "2017-03-04", "2017-06-04", "2017-06-17", "2017-07-02", "2017-07-16", "2017-06-25", "2017-05-07", "2017-01-31", "2017-01-30", "2017-10-29", "2017-01-21", "2017-03-12", "2017-04-30", "2017-06-11", "2017-01-02", "2017-02-05", "2017-09-10", "2017-10-07", "2017-12-10", "2017-03-25", "2017-10-01", "2017-04-15", "2017-07-23", "2017-03-11", "2017-02-25", "2017-03-05", "2017-08-13", "2017-12-09", "2017-10-21", "2017-05-14", "2017-02-11", "2017-02-18", "2017-10-08", "2017-05-01", "2017-04-03", "2017-05-13", "2017-11-12", "2017-04-22", "2017-05-28", "2017-11-26", "2017-11-11", "2017-12-17", "2017-08-27", "2017-09-17", "2017-07-08", "2017-09-09", "2017-10-03", "2017-02-12", "2017-08-12", "2017-04-23", "2017-12-30", "2017-12-31", "2017-03-19", "2017-11-25", "2017-03-18", "2017-02-02", "2017-12-16", "2017-06-03", "2017-11-19", "2017-10-15", "2017-10-04", "2017-04-16", "2017-03-26", "2017-01-29", "2017-02-26", "2017-06-18", "2017-06-10", "2017-01-01", "2017-02-01", "2017-04-08", "2017-10-02", "2017-08-26", "2017-10-28", "2017-08-20", "2017-12-03", "2017-09-16", "2017-04-02", "2017-02-19", "2017-04-29", "2017-05-29", "2017-09-02", "2017-08-06", "2017-08-19", "2017-01-08", "2017-11-05", "2017-09-23", "2017-12-23", "2017-07-30", "2017-09-03") val dateArray: ArrayBuffer[String] = ArrayBuffer() while (beginDate.compareTo(endDate) <= 0) { dateArray += dateFormat.format(beginDate) calendar.add(dateFiled, 1) beginDate = calendar.getTime } val ldate = dateArray.toList // for (v <- str) println(endDate) var de3: Int = ldate.size System.out.println("de3" + de3) for (date <- ldate) { val i: Int = 1 // int de; for (v <- str) { if (dateFormat.parse(v) == dateFormat.parse(date)) { System.out.println("000:" + dateFormat.parse(date)) de3 -= i } } // System.out.println(i); //System.out.println(dateFormat.parse(date)) } // System.out.println(de3 + "个工作日") de3 } spark.udf.register("udf",udf(_:String,_:String)) //--------- jdbcDF.createOrReplaceTempView("Node_Test") val Node_Test=spark.sql("select udf(START_TIME,END_TIME)gongzuori, START_TIME, END_TIME from Node_Test") //注册临时表 Node_Test.show() Node_Test.write.mode(SaveMode.Overwrite).jdbc(url, "test.test3", prop)
创建表：
1154107593Za7
版权声明：本文为kaiwen1原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。