70.仿简道云公式函数实战-日期函数-DATEDIF

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);
    }

}

运行结果