Metabase 自定义表达式 datetimeDiff 完全指南计算两个日期时间之差【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabasedatetimeDiff是 Metabase 自定义表达式Custom Expressions中最常用的时间函数之一用于按指定时间单位年、季度、月、周、天、小时、分钟、秒计算两个日期时间值之间的差值。本文以docs/questions/query-builder/expressions/datetimediff.md为骨架结合 Metabase 开源仓库MBQL 表达式 Schema、SQL 查询处理器 及 前端表达式注册表的源码实现为你完整讲解该函数的语法、参数、实战用法、底层原理与跨语言等价实现读完后你可以在「自定义列 / 自定义聚合」中熟练用datetimeDiff完成年龄计算、时长统计、周期分析等任务。函数语法与返回值datetimeDiff的语法如下语法示例datetimeDiff(datetime1, datetime2, unit)datetimeDiff(2022-02-01, 2022-03-01, month)计算两个日期时间之间的差值datetime2 减去 datetime1使用指定时间单位返回1即datetimeDiff会返回datetime2 - datetime1的结果并按unit指定的单位取整。注意差值按整单位计算——例如从 2022-02-01 到 2022-03-01 恰好是 1 个月因此返回1但如果时间差不足一个完整单位结果会向零取整而不是四舍五入详见下文「整单位计算的细节」。从前端表达式注册表 clauses.ts 可以看到datetimeDiff的返回类型被声明为number描述为 Get the difference between two datetime values ($datetime2minus$datetime1) using the specified unit of time。而在后端 MBQL 层面temporal.cljc 将该子句的返回类型声明为:type/Integer即无论传入的时间粒度如何datetimeDiff最终都返回整数。参数详解datetimeDiff共接收三个参数datetime1 与 datetime2datetime1和datetime2可以是时间戳列名如[Created At]、[Aging Start]返回日期时间datetime的自定义表达式例如datetimeAdd([Shipped At], 3, day)这类表达式的结果日期时间字符串字面量格式为YYYY-MM-DD或YYYY-MM-DDTHH:MM:SS如datetimeDiff(2022-02-01, 2022-03-01, month)。需要注意的是两个参数在 MBQL 层面都被约束为::expression/temporal类型见 temporal.cljc即必须是时间类型表达式。在生成 SQL 时query_processor.clj 中的datetime-diff-check-args还会进一步校验如果参数带有数据库类型database-type且不是以timestamp或date开头会抛出datetimeDiff only allows datetime, timestamp, or date types的查询错误。这也与文档「Accepted data types」表格中「String、Number、Boolean、JSON 均不适用」的说明一致。unit 时间单位unit可以是以下 8 种取值之一yearquartermonthweekdayhourminutesecond这 8 种单位在源码中由 ::datetime-diff-unit 枚举精确定义且不包含millisecond毫秒。对应的 Schema 测试 temporal_test.cljc 验证了上述 8 种单位均为合法输入而:millisecond会被判定为非法;; 合法的 datetime-diff 子句 [:datetime-diff default-options 2024-01-01 2024-01-02 :year] [:datetime-diff default-options 2024-01-01 2024-01-02 :month] [:datetime-diff default-options 2024-01-01T10:20:30 2024-01-02T20:30:40 :second] ;; 非法的 datetime-diff 子句毫秒不被支持 [:datetime-diff default-options 2024-01-01T10:20:30 2024-01-02T20:30:40 :millisecond]在 Metabase 查询构建器的表达式编辑器中unit参数会以下拉选择的形式出现可选值正是文档列出的这 8 项见 clauses.ts。实战示例计算奶酪陈化月龄原文档用了一个非常直观的「奶酪陈化」示例。假设你是一位奶酪制造商需要跟踪奶酪的成熟过程CheeseAging StartAging EndMature Age (Months)Provolone2022-01-192022-03-171Feta2022-01-252022-05-033Monterey Jack2022-01-272022-10-118其中Mature Age (Months)是一个自定义列表达式为datetimeDiff([Aging Start], [Aging End], month)可以看到Provolone 从 1 月 19 日到 3 月 17 日约 57 天按月计算得1Feta 约 98 天得3Monterey Jack 约 257 天得8。这个例子清晰体现了「整单位取整」的特性——不足一个完整月的部分会被舍弃只统计跨越的完整月份数。整单位计算的细节之所以 Provolone 57 天只算 1 个月是因为datetimeDiff采用整单位截断语义它先计算两个时间点之间跨越了多少个完整的指定单位再返回该整数。这一点从各数据库驱动的实现中可以得到印证在 postgres.clj 中month单位的实现是先取两个日期按天截断之间的age间隔再从间隔中提取年份差 × 12 月份差得到的都是整数在 h2.clj 与 sqlite.clj 中year、quarter、week等单位进一步由month或day的结果做整数除法得到例如month ÷ 12 year、day ÷ 7 week小数部分被直接截断。因此当差值不足一个单位时结果可能为0。例如datetimeDiff(2022-02-01, 2022-02-20, month)会返回0不足 1 个完整月而不会四舍五入为1。如果需要更高精度的时长可以改用更小的单位如day、hour或组合多个datetimeDiff表达式。结合 now 计算当前年龄如果要用「今天」作为截止时间动态计算当前年龄可以直接使用now作为第二个日期时间参数。计算奶酪当前陈化月龄datetimeDiff([Aging Start], now, month)计算奶酪当前陈化天数datetimeDiff([Aging Start], now, day)这种写法适用于任何从某时间点至今的时长场景例如会员注册时长datetimeDiff([Signup Date], now, year)订单超时判断datetimeDiff([Order Created At], now, hour)工单处理时长datetimeDiff([Issue Created At], now, day)。now会随查询执行时间动态取值因此每次刷新问题结果时都能得到最新的年龄/时长。支持的数据类型datetimeDiff对数据类型的要求如下数据类型是否可用于datetimeDiffString❌Number❌Timestamp✅Boolean❌JSON❌文档中的 timestamp 与 datetime 泛指 Metabase 支持的所有时间类型。关于时间类型在 Metabase 中的更多说明可参考 Timezones 文档。如果你的时间戳以字符串或数字形式存储在数据库中需要管理员先在「表元数据Table Metadata」页面将它们转换为时间戳类型之后才能正确参与datetimeDiff计算。这与上文提到的datetime-diff-check-args类型校验逻辑query_processor.clj相对应SQL 查询处理器会在生成 SQL 前校验参数类型非日期/时间类型会直接报错。底层实现原理从表达式到 SQL理解datetimeDiff的底层实现有助于你判断它在不同数据库上的行为差异。驱动能力声明datetimeDiff是 Metabase 的**驱动能力driver feature**之一。在 driver.clj 中:datetime-diff被声明为驱动能力项前端表达式注册表 clauses.ts 也通过requiresFeature: datetime-diff声明只有声明支持该能力的数据库驱动才会在表达式编辑器中开放datetimeDiff选项。各内置驱动在features中启用该能力例如postgres.clj:datetime-diff truemysql.clj:datetime-diff trueh2.clj:datetime-diff truesqlite.clj:datetime-diff trueHoneySQL 编译路径当查询构建器生成问题后:datetime-diff子句会经由 SQL 查询处理器 -honeysql [:sql :datetime-diff] 编译为数据库方言先对两个参数调用-honeysql转换为表达式再调用datetime-diff-check-args做类型校验最后分发到对应驱动的datetime-diff多方法multimethod上。各驱动的实现策略分为两类原生函数派如 MySQL 使用TIMESTAMPDIFF/DATEDIFFmysql.clj表达式推导派如 PostgreSQL 使用AGE间隔的EXTRACT组合postgres.cljH2、SQLite 则基于基础单位month/day做整数除法推导更大单位h2.clj、sqlite.clj。这意味着datetimeDiff在不同数据库上生成的具体 SQL 可能不同但结果语义整单位差值保持一致。这也是为什么文档中相关函数一节建议直接查阅各数据库原生日期函数的原因。限制Druid 不支持datetimeDiff目前不适用于 Druid 数据库。由于驱动未声明:datetime-diff能力使用 Druid 连接时表达式编辑器中不会出现该函数也无法通过该驱动编译此类查询。如果你的数据存储在 Druid 上建议在数据源层面预先计算好差值或将数据同步到其他支持的数据库后再使用。等价实现SQL / 电子表格 / Python如果你需要在datetimeDiff之外的其他工具中复现相同逻辑可以参照以下等价实现。以下示例均以奶酪示例中的数据为背景。SQL当使用查询构建器Query Builder运行问题时Metabase 会把图形化的查询设置筛选、汇总等转换成 SQL 并发送给数据库执行。如果奶酪数据存储在 PostgreSQL 中SELECT DATE_PART(month, AGE(aging_end, aging_start)) AS mature_age_months FROM cheese等价于 Metabase 表达式datetimeDiff([Aging Start], [Aging End], month)需要注意的是不同数据库的日期函数命名差异很大Snowflake 和 BigQuery 分别支持DATEDIFF、DATE_DIFF这类函数。如果你以原生 SQL 方式编写查询请查阅目标数据库的日期函数文档Metabase 官方文档的 SQL 参考指南提供了常用数据库的对照说明。电子表格如果奶酪数据在电子表格中「Aging Start」位于 B 列、「Aging End」位于 C 列则DATEDIF(B1, C1, M)产生的结果与datetimeDiff([Aging Start], [Aging End], month)一致。注意是DATEDIF只有一个 F而不是DATEDIFF。Excel / Google Sheets 的DATEDIF同样返回整单位差值与 Metabase 的整单位取整语义吻合。Pythonpandas假设奶酪数据在pandas的 DataFrame 中可以直接对日期列做减法并借助numpy的timedelta64将差值转换为月数df[Mature Age (Months)] (df[Aging End] - df[Aging Start]) / np.timedelta64(1, M)等价于datetimeDiff([Aging Start], [Aging End], month)在实际使用中np.timedelta64(1, M)返回的是浮点数月数可能带小数如需与 Metabase 的整单位语义完全一致可以对其取整如astype(int)或使用//整除。使用建议综合文档与源码使用datetimeDiff时有几点建议明确单位语义返回值永远是整单位差若需要精确到天的部分请使用更小单位或组合多个表达式先检查数据类型字符串 / 数字形式的时间字段需要先由管理员在表元数据中转换为时间戳否则查询会因类型校验失败而报错留意数据库差异不同数据库对月的定义可能不同例如按日历月还是按 30 天Metabase 各驱动已尽力统一语义但极端场景月末、闰年、夏令时下的行为请以实际数据库为准动态时间用now需要截至当前的时长时把第二个参数换成nowDruid 用户需绕行Druid 数据源不支持该表达式需在数据侧预先计算。进一步阅读自定义表达式Custom Expressions总览now 表达式返回当前日期时间datetimeAdd 表达式向日期时间增加指定单位datetimeSubtract 表达式从日期时间减去指定单位Metabase 中的时区与时间数据类型在表元数据中将字段转换为时间戳【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabase创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
