DATEDIFF的多种用法

1、减出来是日历中两个日期的差值，与时分秒无关

例如2023-03-16 23:00:00 到 2023-03-28 00:00:00，减出来是28-16=22天

    DATEDIFF(
        CAST(FROM_UNIXTIME(CAST(buyers.value_date / 1000 AS BIGINT)) AS DATE),
        CAST(FROM_UNIXTIME(regist.registertime) AS DATE)
    )

2、1是UTC对应的日期的间隔，以下是转换为北京时间对应的日期的间隔

DATEDIFF(
        CAST(FROM_UNIXTIME(CAST((c.purchase_time / 1000+8*60*60) AS BIGINT)) AS DATE),
        CAST(FROM_UNIXTIME(CAST((a.earliest_flow_time / 1000+8*60*60) AS BIGINT)) AS DATE)
    ) AS jiange,CAST(FROM_UNIXTIME(CAST((c.purchase_time / 1000+8*60*60) AS BIGINT)) AS DATE) as b1,CAST(FROM_UNIXTIME(CAST((a.earliest_flow_time / 1000+8*60*60) AS BIGINT)) AS DATE) as f1

执行结果.png

3、算的时候用到的时分秒，但是仅取天不看结果中的时分秒

例如2023-03-16 23:00:00 到 2023-03-28 00:00:00，减出来是11天零1小时的时间间隔，DATEDIFF仅取整数部分，也就是11

DATEDIFF(
    FROM_UNIXTIME(CAST( buyers.value_date / 1000 AS BIGINT )),
    FROM_UNIXTIME(CAST( regist.registertime AS BIGINT )))