1. DATEDIF函数
DATEDIF 函数可用于计算两个时间的差值。
2. 函数用法
DATEDIF(start_timestamp, end_timestamp, [unit])
其中各参数的含义如下:
-
start_timestamp:必需, 开始时间;
-
end_timestamp:必需,结束时间;
-
Unit:可选,默认为 “d”,可设置的参数如下:
-
“y”:年数
-
“M”:月数
-
“d”:天数
-
“h”:小时数
-
“m”:分钟数
-
“s”:秒数
-
注:如结束日期小于开始日期,则计算不出结果。
3. 函数示例
通过 DATEDIF 函数计算时间差的年数、月数、天数等,可应用于计算项目的消耗天数、活动的举办天数等。
4. 代码实战
首先我们在function包下创建text包,在text包下创建DateDifFunction类,代码如下:
package com.ql.util.express.self.combat.function.date;
import com.ql.util.express.Operator;
import com.ql.util.express.self.combat.exception.FormulaException;
import java.time.*;
/**
* 类描述: DATEDIF函数
*
* @author admin
* @version 1.0.0
* @date 2023/11/27 9:22
*/
public class DateDifFunction extends Operator {
public DateDifFunction(String name) {
this.name = name;
}
@Override
public Object executeInner(Object[] lists) throws Exception {
if (lists.length <= 0 || lists.length >3 || lists.length == 1) {
throw new FormulaException("操作数异常");
}
long startTimestamp = Long.parseLong(lists[0].toString());
long endTimestamp = Long.parseLong(lists[1].toString());
String unit = "";
if (lists.length == 2) { //第三个值默认为d
unit = "d";
} else {
unit = lists[2].toString();
}
Object rst = handle(startTimestamp,endTimestamp,unit);
return rst;
}
private Object handle(long startTimestamp, long endTimestamp, String unit) {
// 异常判断 结束日期小于开始日期,则计算不出结果
if (endTimestamp < startTimestamp) {
return null;
}
Object rst = null;
if ("y".equals(unit)) {
rst = period(startTimestamp,endTimestamp,unit);
} else if ("M".equals(unit)) {
rst = period(startTimestamp,endTimestamp,unit);
} else if ("d".equals(unit)) {
rst = period(startTimestamp,endTimestamp,unit);
} else if ("h".equals(unit)) {
rst = duration(startTimestamp,endTimestamp,unit);
} else if ("m".equals(unit)) {
rst = duration(startTimestamp,endTimestamp,unit);
} else if ("s".equals(unit)) {
rst = duration(startTimestamp,endTimestamp,unit);
} else {// unit枚举参数传错误 默认按照d处理
rst = period(startTimestamp,endTimestamp,unit);
}
return rst;
}
private static LocalDate localDate(long timestamp) {
// 将时间戳转换为Instant对象
Instant instant = Instant.ofEpochMilli(timestamp);
// 将Instant对象转换为LocalDate对象
LocalDate localDate = instant.atZone(ZoneId.systemDefault()).toLocalDate();
return localDate;
}
private static Object period(long startTimestamp, long endTimestamp, String unit) {
Object rst = null;
LocalDate startLocalDate = localDate(startTimestamp);
LocalDate endLocalDate = localDate(endTimestamp);
Period period = Period.between(startLocalDate, endLocalDate);
if ("y".equals(unit)) {
int years = period.getYears();
rst = years;
} else if ("M".equals(unit)) {
int months = period.getMonths();
rst = months;
} else if ("d".equals(unit)) {
int days = period.getDays();
rst = days;
} else {
int days = period.getDays();
rst = days;
}
return rst;
}
private static Object duration(long startTimestamp, long endTimestamp, String unit) {
Object rst = null;
LocalDateTime dateTime1 = LocalDateTime.ofInstant(Instant.ofEpochMilli(startTimestamp), ZoneId.systemDefault());
LocalDateTime dateTime2 = LocalDateTime.ofInstant(Instant.ofEpochMilli(endTimestamp), ZoneId.systemDefault());
Duration duration = Duration.between(dateTime1, dateTime2);
if ("h".equals(unit)) {
long hours = duration.toHours() % 24;
rst = hours;
} else if ("m".equals(unit)) {
long minutes = duration.toMinutes() % 60;
rst = minutes;
} else if ("s".equals(unit)) {
long seconds = duration.getSeconds() % 60;
rst = seconds;
}
return rst;
}
}
把DateDifFunction类注册到公式函数入口类中,代码如下:
package com.ql.util.express.self.combat.ext;
import com.ql.util.express.ExpressRunner;
import com.ql.util.express.IExpressResourceLoader;
import com.ql.util.express.parse.NodeTypeManager;
import com.ql.util.express.self.combat.function.date.DateDeltaFunction;
import com.ql.util.express.self.combat.function.date.DateDifFunction;
import com.ql.util.express.self.combat.function.date.DateFunction;
import com.ql.util.express.self.combat.function.logic.*;
import com.ql.util.express.self.combat.function.math.*;
import com.ql.util.express.self.combat.function.text.*;
/**
* 类描述: 仿简道云公式函数实战入口类
*
* @author admin
* @version 1.0.0
* @date 2023/11/21 15:29
*/
public class FormulaRunner extends ExpressRunner {
public FormulaRunner() {
super();
}
public FormulaRunner(boolean isPrecise, boolean isTrace) {
super(isPrecise,isTrace);
}
public FormulaRunner(boolean isPrecise, boolean isStrace, NodeTypeManager nodeTypeManager) {
super(isPrecise,isStrace,nodeTypeManager);
}
public FormulaRunner(boolean isPrecise, boolean isTrace, IExpressResourceLoader iExpressResourceLoader, NodeTypeManager nodeTypeManager) {
super(isPrecise,isTrace,iExpressResourceLoader,nodeTypeManager);
}
@Override
public void addSystemFunctions() {
// ExpressRunner 的内部系统函数
super.addSystemFunctions();
// 扩展公式函数
this.customFunction();
}
/***
* 自定义公式函数
*/
public void customFunction() {
// 逻辑公式函数
this.addLogicFunction();
// 数学公式函数
this.addMathFunction();
// 文本函数
this.addTextFunction();
// 日期函数
this.addDateFunction();
}
public void addDateFunction() {
// DATE函数
this.addFunction("DATE",new DateFunction("DATE"));
// DATEDELTA函数
this.addFunction("DATEDELTA",new DateDeltaFunction("DATEDELTA"));
// DATEDIF函数
this.addFunction("DATEDIF",new DateDifFunction("DATEDIF"));
}
public void addTextFunction() {
// CHAR函数
this.addFunction("CHAR",new CharFunction("CHAR"));
// CONCATENATE函数
this.addFunction("CONCATENATE",new ConcatenateFunction("CONCATENATE"));
// EXACT函数
this.addFunction("EXACT",new ExactFunction("EXACT"));
// IP函数
this.addFunction("IP",new IpFunction("IP"));
// ISEMPTY函数
this.addFunction("ISEMPTY",new IsEmptyFunction("ISEMPTY"));
// JOIN函数
this.addFunction("JOIN",new JoinFunction("JOIN"));
// LEFT函数
this.addFunction("LEFT",new LeftFunction("LEFT"));
// LEN函数
this.addFunction("LEN",new LenFunction("LEN"));
// LOWER函数
this.addFunction("LOWER",new LowerFunction("LOWER"));
// MID函数
this.addFunction("MID",new MidFunction("MID"));
// REPLACE函数
this.addFunction("REPLACE",new ReplaceFunction("REPLACE"));
// REPT函数
this.addFunction("REPT",new ReptFunction("REPT"));
// RIGHT函数
this.addFunction("RIGHT",new RightFunction("RIGHT"));
// RMBCAP函数
this.addFunction("RMBCAP",new RmbCapFunction("RMBCAP"));
// SEARCH函数
this.addFunction("SEARCH",new SearchFunction("SEARCH"));
// SPLIT函数
this.addFunction("SPLIT",new SplitFunction("SPLIT"));
// TEXT函数
this.addFunction("TEXT",new TextFunction("TEXT"));
// TRIM函数
this.addFunction("TRIM",new TrimFunction("TRIM"));
// UNION函数
this.addFunction("UNION",new UnionFunction("UNION"));
// UPPER函数
this.addFunction("UPPER",new UpperFunction("UPPER"));
// VALUE函数
this.addFunction("VALUE",new ValueFunction("VALUE"));
}
public void addLogicFunction() {
// AND函数
this.addFunction("AND",new AndFunction("AND"));
// IF函数
this.addFunction("IF",new IfFunction("IF"));
// IFS函数
this.addFunction("IFS",new IfsFunction("IFS"));
// XOR函数
this.addFunction("XOR",new XorFunction("XOR"));
// TRUE函数
this.addFunction("TRUE",new TrueFunction("TRUE"));
// FALSE函数
this.addFunction("FALSE",new FalseFunction("FALSE"));
// NOT函数
this.addFunction("NOT",new NotFunction("NOT"));
// OR函数
this.addFunction("OR",new OrFunction("OR"));
}
public void addMathFunction() {
// ABS函数
this.addFunction("ABS",new AbsFunction("ABS"));
// AVERAGE函数
this.addFunction("AVERAGE",new AvgFunction("AVERAGE"));
// CEILING函数
this.addFunction("CEILING",new CeilingFunction("CEILING"));
// RADIANS函数
this.addFunction("RADIANS",new RadiansFunction("RADIANS"));
// COS函数
this.addFunction("COS",new CosFunction("COS"));
// COT函数
this.addFunction("COT",new CotFunction("COT"));
// COUNT函数
this.addFunction("COUNT",new CountFunction("COUNT"));
// COUNTIF函数
this.addFunction("COUNTIF",new CountIfFunction("COUNTIF"));
// FIXED函数
this.addFunction("FIXED",new FixedFunction("FIXED"));
// FLOOR函数
this.addFunction("FLOOR",new FloorFunction("FLOOR"));
// INT函数
this.addFunction("INT",new IntFunction("INT"));
// LARGE函数
this.addFunction("LARGE",new LargeFunction("LARGE"));
// LOG函数
this.addFunction("LOG",new LogFunction("LOG"));
// MAX函数
this.addFunction("MAX",new MaxFunction("MAX"));
// MIN函数
this.addFunction("MIN",new MinFunction("MIN"));
// MOD函数
this.addFunction("MOD",new ModFunction("MOD"));
// POWER函数
this.addFunction("POWER",new PowerFunction("POWER"));
// PRODUCT函数
this.addFunction("PRODUCT",new ProductFunction("PRODUCT"));
// RAND函数
this.addFunction("RAND",new RandFunction("RAND"));
// ROUND函数
this.addFunction("ROUND",new RoundFunction("ROUND"));
// SIN函数
this.addFunction("SIN",new SinFunction("SIN"));
// SMALL函数
this.addFunction("SMALL",new SmallFunction("SMALL"));
// SQRT函数
this.addFunction("SQRT",new SqrtFunction("SQRT"));
// SUM函数
this.addFunction("SUM",new SumFunction("SUM"));
// SUMIF函数
this.addFunction("SUMIF",new SumIfFunction("SUMIF"));
// SUMIFS函数
this.addFunction("SUMIFS",new SumIfsFunction("SUMIFS"));
// SUMPRODUCT函数
this.addFunction("SUMPRODUCT",new SumProductFunction("SUMPRODUCT"));
// TAN函数
this.addFunction("TAN",new TanFunction("TAN"));
}
}
创建测试用例
package com.ql.util.express.self.combat;
import com.ql.util.express.DefaultContext;
import com.ql.util.express.self.combat.ext.FormulaRunner;
import org.junit.Test;
/**
* 类描述: 实战测试类
*
* @author admin
* @version 1.0.0
* @date 2023/11/21 15:45
*/
public class CombatTest {
@Test
public void DATEDIF() throws Exception{
FormulaRunner formulaRunner = new FormulaRunner(true,true);
// 创建上下文
DefaultContext<String, Object> context = new DefaultContext<>();
String express = "DATEDIF(1689311708000,3690780508000,'y')";
Object object = formulaRunner.execute(express, context, null, true, true);
System.out.println(object);
}
}
运行结果