Metabase datetimeAdd 自定义表达式时间加减运算的完整实战指南【免费下载链接】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导读datetimeAdd是 Metabase 自定义表达式Custom Expressions中用于时间算术的核心函数它接收一个日期/时间值并按指定单位加上一定数量。无论是计算会话或订阅这类开始/结束标记的时间序列数据的截止日期还是判断某个时刻是否落在某个时间区间内datetimeAdd都是最直接的解决方案。阅读本文后你将掌握datetimeAdd的完整语法、参数约束、数据类型要求、底层实现原理以及它与 SQL、电子表格、Python 中同类操作的对应关系。函数定义与语法datetimeAdd接收一个日期时间值并为其加上指定的时间单位数量。它尤其适合处理带有开始与结束标记的时间序列数据例如会话时长、订阅周期、保质期等场景。语法示例datetimeAdd(column, amount, unit)datetimeAdd(2021-03-25, 1, month)接收一个时间戳或日期值并为其加上指定数量的时间单位2021-04-25在查询构建器中你可以通过创建自定义列Create a custom column的方式在表达式编辑器中直接输入datetimeAdd(...)也可以在其他自定义表达式如case中嵌套使用它。参数详解column被加减的时间值可以是以下任意一种一个时间戳timestamp列的名称例如[Opened On]一个返回 日期时间 的自定义表达式例如now或datetimeAdd本身一个字符串字面量格式为YYYY-MM-DD或YYYY-MM-DDTHH:MM:SS如上方示例所示。unit时间单位可以是以下任意一种year年quarter季度month月day天hour小时minute分钟second秒millisecond毫秒amount数量需要满足以下约束必须是整数。不能使用小数例如不能加半年0.5可以为负数datetimeAdd(2021-03-25, -1, month)将返回2021-02-25。这也意味着datetimeAdd本身即可实现减法语义。从源码层面看以上约束由 src/metabase/lib/schema/expression/temporal.cljc 中的 MBQL 子句 schema 强制执行(doseq [op [:datetime-add :datetime-subtract]] (mbql-clause/define-tuple-mbql-clause op #_expr [:ref ::expression/temporal] ;; 第一个参数必须是 temporal 类型 #_amount :int ;; 数量必须是整数 #_unit [:ref ::temporal-bucketing/unit.date-time.interval]))其中unit的合法取值集合定义在 src/metabase/lib/schema/temporal_bucketing.cljc日期类单位包括day、week、month、quarter、year时间类单位包括millisecond、second、minute、hour二者取并集后即为:unit.date-time.interval。因此datetimeAdd支持的 8 个单位与文档完全一致并且year是被特别纳入的——在截断truncation场景中year通常被解释为提取extraction操作而这里它作为合法的间隔单位单独加入见源码注释。实战场景一计算结束日期假设你是一位咖啡爱好者想要跟踪咖啡豆的保鲜期。你有一张表记录每袋咖啡的开封日期Opened On想要计算需要在何时之前喝完Finish ByCoffeeOpened OnFinish ByDAK Honey Dude2022-10-312022-11-14NO6 Full City Espresso2022-11-072022-11-21Ghost Roaster Giakanja2022-11-272022-12-11其中Finish By是一个自定义列表达式为datetimeAdd([Opened On], 14, day)即开封日期 14 天。这是datetimeAdd最常见的用法用一个列名 一个固定数量 一个单位快速派生截止时间。实战场景二判断当前时间是否落在区间内接着上面的场景假设今天是 2022 年 12 月 1 日你想判断每袋咖啡是否仍然新鲜CoffeeOpened OnFinish ByStill Fresh TodayDAK Honey Dude2022-10-312022-11-14NoNO6 Full City Espresso2022-11-072022-11-21NoGhost Roaster Giakanja2022-11-272022-12-11Yes其中Finish By依然使用datetimeAdd([Opened On], 14, day)计算而Still Fresh Today则用case判断当前日期now是否 betweenOpened On与Finish By之间case(between(now, [Opened On], [Finish By]), Yes, No)这个组合模式非常通用datetimeAdd负责锚定一个区间的终点betweennow负责判断当前时刻是否落入该区间适用于订阅是否到期、促销活动是否进行中、任务是否超时等一切当前是否在窗口内的判断需求。关于datetimeAdd中时间值的类型推导src/metabase/lib/schema/expression/temporal.cljc 中有专门的实现说明由于日期算术的结果必然是时间类型当第一个参数的类型可能是一组候选类型例如格式化字符串会被推断为#{:type/String :type/DateTime}时Metabase 会求交集并收敛为:type/Date或:type/DateTime从而保证后续的类型推导如finish_by列的元数据是准确的。接受的数据类型数据类型是否可用于datetimeAddString字符串❌Number数字❌Timestamp时间戳✅Boolean布尔值❌JSON❌Metabase 使用 timestamp 和 datetime 来泛指其支持的一切时间数据类型。关于这些数据类型在 Metabase 中的详细说明参见 时间时区文档。如果你的时间戳在数据库中以字符串或数字形式存储可以让管理员在表元数据Table Metadata页面将其 转换为时间戳 后再使用datetimeAdd。这一点与前面 schema 中::expression/temporal的类型约束相呼应——第一参数必须是时间类型字符串字面量会被解析但存储为文本的列不会自动被当作日期。底层实现从表达式到数据库查询datetimeAdd在前端被编译为 MBQLMetabase Query Language中的:datetime-add子句格式为[:datetime-add {} expr amount unit]。在服务端SQL 类驱动的翻译逻辑位于 src/metabase/driver/sql/query_processor.clj(defmethod -honeysql [:sql :datetime-add] [driver [_ _opts arg amount unit]] (add-interval-honeysql-form driver (-honeysql driver arg) amount (check-interval-unit unit))) (defmethod -honeysql [:sql :datetime-subtract] [driver [_ _opts arg amount unit]] (add-interval-honeysql-form driver (-honeysql driver arg) (- amount) (check-interval-unit unit)))可以看到两个关键细节datetime-add与datetime-subtract共享同一套翻译逻辑datetime-subtract只是把amount取负后调用add-interval-honeysql-form这印证了文档中二者可互换的结论check-interval-unit会校验单位合法性定义在 src/metabase/driver/sql/query_processor.clj随后通过add-interval-honeysql-form生成对应数据库的INTERVAL表达式。MongoDB 驱动的实现与版本限制MongoDB 驱动的翻译逻辑位于 modules/drivers/mongo/src/metabase/driver/mongo/query_processor.clj它会把:datetime-add翻译为 MongoDB 聚合管道中的$dateAdd操作(mu/defmethod -rvalue :datetime-add [query stage-number [_ _opts inp amount unit] :- :mbql.clause/datetime-add] (check-date-operations-supported query) {$dateAdd {:startDate (-rvalue query stage-number inp) :unit unit :amount amount}})而$dateAdd/$dateSubtract这类日期算术操作符只在 MongoDB 5.0 及以上版本中可用因此check-date-operations-supported会读取数据库主版本号并在版本低于 5 时抛出异常(defn- check-date-operations-supported [metadata-providerable] (let [{mongo-version :version, [major-version] :semantic-version} (get-mongo-version metadata-providerable)] (when (and major-version ( major-version 5)) (throw (ex-info Date arithmetic not supported in versions before 5 {:database-version mongo-version})))))这正是文档中如果你使用 MongoDBdatetimeAdd只在 5.0 及以上版本生效这一限制的源码依据。测试验证仓库中的测试用例对datetimeAdd的行为有充分覆盖例如 test/metabase/lib/expression_test.cljc 验证了负数数量与自动命名(let [clause [:datetime-add {} (lib.tu/field-clause :checkins :date {:base-type :type/Date}) -1 :day]] (is ( DATE_minus_1_day (lib/column-name (lib.tu/venues-query) -1 clause))) (is ( Date - 1 day (lib/display-name (lib.tu/venues-query) -1 clause))))同文件中的类型推导测试test/metabase/lib/expression_test.cljc也断言(lib/datetime-add dt-field 1 :month)的结果类型为:type/DateTime进一步验证了日期算术的返回类型语义。限制与注意事项MongoDB 版本限制datetimeAdd在 MongoDB 上仅支持 5.0 及以上版本详见上文源码分析amount必须是整数这是由:intschema 强制约束的无法表达0.5 年这类分数列类型必须为时间类型存储在文本或数字列中的时间戳需先在表元数据中转换为时间戳列。相关函数与跨工具对照这一节涵盖与 MetabasedatetimeAdd表达式行为相同的函数与公式并说明如何为你的场景选择最合适的方案。datetimeSubtract减法版本datetimeSubtract与datetimeAdd完全可以互换因为amount支持负数。不过实践中应尽量避免双重否定例如减去一个负数datetimeSubtract([Opened On], -14, day)与下面这条表达式结果相同datetimeAdd([Opened On], 14, day)从 src/metabase/driver/sql/query_processor.clj 可以看到datetime-subtract本质上就是对amount取负后的datetime-add两条路径最终都编译为同一个数据库INTERVAL表达式。SQL 中的等价写法当你使用查询构建器运行问题时Metabase 会把图形化的查询设置过滤条件、汇总等转换为 SQL 查询并在数据库上执行。假设上文 咖啡样例数据 存储在 PostgreSQL 中SELECT opened_on INTERVAL 14 days AS finish_by FROM coffee等价于 Metabase 表达式datetimeAdd([Opened On], 14, day)不同数据库对 interval 加法的语法略有差异而datetimeAdd的价值正在于它把不同数据库的方言统一成一个一致的表达式语法由驱动层如-honeysql的add-interval-honeysql-form负责方言转换。电子表格Spreadsheets中的等价写法如果 咖啡样例数据 在电子表格中且 Opened On 位于日期格式的 A 列那么电子表格公式A:A 14产生与下面相同的结果datetimeAdd([Opened On], 14, day)大多数电子表格工具要求针对不同时间单位使用不同函数例如加月需要另一个函数而datetimeAdd把所有时间单位统一为单一、一致的语法降低了跨工具迁移的心智负担。Python 中的等价写法假设 咖啡样例数据 位于名为df的 pandas DataFrame 列中你可以导入datetime模块并使用timedelta函数df[Finish By] df[Opened On] datetime.timedelta(days14)与下面等价datetimeAdd([Opened On], 14, day)注意datetime.timedelta原生支持的是天/秒/微秒级别的运算若需按月或季度等单位运算通常需要借助dateutil.relativedelta之类的扩展库——这正是datetimeAdd在 Metabase 中按统一单位直接表达的便利之处。进一步阅读自定义表达式文档表达式列表总览时间序列分析文档原文指向 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），仅供参考