尧图网站建设 
1. 三个库的时间函数先把底子摸清楚人大金仓、MySQL、达梦这三家放在一起做时间查询是很多做信创改造的团队绕不开的场景。MySQL 的DATE_SUB、人大金仓 KingbaseES 的interval运算、达梦 DM8 的add_days与trunc单看名字只是函数不同实际语义差得挺远。我在一个报表项目里就吃过亏同一句近一个月的 SQL 从 MySQL 迁到人大金仓数据没问题再切到达梦之后条数突然少了几条排查大半天最后发现是月末日期进位规则不一致导致的。所以这篇东西主要写给两类人一类是需要同时维护 MySQL、人大金仓、达梦三种库的开发和运维另一类是正在做数据库迁移、需要把时间类 SQL 逐条改写的人。文中涉及的写法都是我在实际库里跑过或者验证过的取数口径、参数怎么算、为什么这么算我尽量都讲透。新手看能照着抄老手看能对着排查。1.1 为什么同一句时间 SQL 换个库就报错先说清楚一件事SQL 标准里对时间类型的定义其实很宽真正落到实现层各家都加了自己的方言。最典型的三处分歧几乎每次迁移都会撞上。第一处是当前时间函数。MySQL 用NOW()和CURDATE()人大金仓跟着 PostgreSQL 走用now()、current_date、current_timestamp达梦更偏 Oracle 一路SYSDATE、CURRENT_DATE都能用NOW()在部分版本里也能识别但行为未必和 MySQL 一致。这里有个细节要特别注意MySQL 的NOW()取的是语句开始执行的时间同一条 SQL 里调用多次结果完全一样而SYSDATE()是实时取每调用一次重新读一次系统时间。生产里做主从复制或者批量写入打时间戳这两个混用会出现同一条记录里两个时间字段对不上的诡异现象。第二处是日期加减的写法。MySQL 是DATE_ADD(d, INTERVAL 7 DAY)这种带关键字的写法人大金仓直接支持d interval 7 day的算术运算达梦用的是函数式ADD_DAYS(d, 7)、ADD_MONTHS(d, 1)。三种语法互不兼容硬套必然报错。第三处是日期截断。你要算本季度的起始日期MySQL 得绕一下用MAKEDATE配合QUARTER人大金仓用date_trunc(quarter, ...)达梦用TRUNC(d, Q)。这三个函数不仅名字不同返回值的类型也不同——MySQL 返回日期字符串人大金仓返回 timestamp达梦返回 date。类型不同后续再参与运算就又可能出错。提示迁移前先确认目标库处于哪种兼容模式。人大金仓和达梦都提供多种兼容模式同一个函数在不同模式下的可用性、返回值都可能不一样这一步不做后面全白干。1.2 三家常用时间函数对照表我整理了一张对照表覆盖日常取数最常用的十来组场景。表里的写法都是最简形式实际使用时字段类型要匹配比如date和timestamp在参与比较时行为是有差异的。需求MySQL人大金仓 KingbaseES达梦 DM8当前日期时间NOW()/SYSDATE()now()/current_timestampSYSDATE/NOW()当前日期CURDATE()current_dateCURRENT_DATE/TRUNC(SYSDATE)加 N 天DATE_ADD(d, INTERVAL n DAY)d interval n dayADD_DAYS(d, n)加 N 月DATE_ADD(d, INTERVAL n MONTH)d interval n monthADD_MONTHS(d, n)减 N 天DATE_SUB(d, INTERVAL n DAY)d - interval n dayADD_DAYS(d, -n)两日期相差天数DATEDIFF(d1, d2)d1 - d2date 类型DATEDIFF(DAY, d2, d1)两日期相差月数TIMESTAMPDIFF(MONTH, d1, d2)extract(year from age(d1,d2))*12 extract(month from age(d1,d2))MONTHS_BETWEEN(d1, d2)截断到月首DATE_FORMAT(d, %Y-%m-01)date_trunc(month, d)TRUNC(d, MM)截断到季首MAKEDATE(YEAR(d),1) INTERVAL QUARTER(d)-1 QUARTERdate_trunc(quarter, d)TRUNC(d, Q)截断到年首DATE_FORMAT(d, %Y-01-01)date_trunc(year, d)TRUNC(d, YYYY)当月最后一天LAST_DAY(d)(date_trunc(month, d) interval 1 month - 1 day)::dateLAST_DAY(d)看着挺规整实际上每一行都可能藏雷。举两个我在项目里真实碰到的例子。第一个是DATEDIFF的参数顺序。MySQL 的DATEDIFF(d1, d2)返回d1 - d2达梦的DATEDIFF(part, d1, d2)返回的是d2 - d1。方向正好反了。我见过一个统计逾期天数的口径迁移之后所有负数变成了正数报表显示无一逾期差点当成业务好转上报还好被复核拦下了。第二个是ADD_MONTHS的月末进位。Oracle 系达梦也沿用了这套规则的ADD_MONTHS(2024-01-31, 1)返回2024-02-29会自动收缩到当月最后一天MySQL 的DATE_ADD(2024-01-31, INTERVAL 1 MONTH)同样返回2024-02-29看着一致但换成2024-03-31减一个月MySQL 给2024-02-29而某些库的实现给的是2024-02-28或者直接报错。做对账类业务时这种一天的差别足够让整张报表对不平。1.3 兼容模式这个坑先确认再写代码人大金仓的兼容模式不是一个小开关它在初始化数据库实例时就要定下来后面改起来非常麻烦。大致分两类一类是偏 PostgreSQL 的行为interval运算、date_trunc、age这些函数是原生支持的另一类是偏 Oracle 的行为ADD_MONTHS、MONTHS_BETWEEN、TRUNC、SYSDATE这些能直接用。达梦的情况类似虽然默认更贴近 Oracle 语法但也提供了与其他数据库兼容的参数。实际项目中如果代码里混着写两套语法一定会有某些函数在某个模式下不存在。我的建议很直接在项目启动阶段就把目标库的模式问清楚写进技术方案文档然后在测试库里跑一遍你的全部时间 SQL。别等上线前一夜才发现某个函数不存在。验证方法很简单把本文后面几节的 SQL 拿到你的实例上逐条执行一遍能跑通基本就没大问题。这里还有一个容易忽略的点函数存在不等于语义一致。有些库为了兼容会提供一个同名函数但内部实现不同。比如date_trunc的week参数PostgreSQL 系默认按周一开始某些兼容实现按周日开始。这类差异不会报错只会让数据悄悄偏掉只能靠构造边界日期做验证。2. 取近期数据的写法近几天、一周、一月、季度、一年这一节是实操主体。我把近 N 天本周本月本季度本年这些高频需求按三个库分别写一遍同时把口径问题讲清楚。2.1 先定口径滚动区间还是自然周期这是最容易被跳过、事后扯皮最多的一步。近一周这三个字至少有两种理解。一种是滚动区间从当前时刻往前推 7 天比如今天是 3 月 15 日 10 点那就是 3 月 8 日 10 点 到 3 月 15 日 10 点。这种口径常见于监控、风控、实时看板好处是不受日期边界影响任意时刻查询结果都是稳定的 7 天。另一种是自然周期本周从周一到今天或者本周一到周日。这种口径常见于经营报表、考勤、财务因为它要跟日历对齐跨周不能混算。这两种口径写出来的 SQL 完全不同前者用加减法就够了后者必须先做日期截断。我建议在需求评审阶段就用一句话把口径写死比如近 7 天指含当前时刻在内的滚动 168 小时避免后面反复改。还有一个隐藏的口径问题边界含不含。区间是[今天, 7天前]还是(今天, 7天前]如果字段是DATETIME并且经常带时分秒用闭区间很容易把前一天的最后一秒漏掉或多算一天。稳妥的做法一律用左闭右开WHERE create_time 起始 AND create_time 结束下面所有示例我都按左闭右开来写。2.2 MySQLDATE_SUB 与 INTERVAL 的组合拳MySQL 的写法最直观核心是DATE_SUB、DATE_ADD和INTERVAL关键字。先看滚动区间。-- 近 7 天滚动含今天 SELECT * FROM t_order WHERE create_time DATE_SUB(NOW(), INTERVAL 7 DAY) AND create_time NOW(); -- 近 30 天 SELECT * FROM t_order WHERE create_time DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND create_time CURDATE() INTERVAL 1 DAY; -- 近 1 年 SELECT * FROM t_order WHERE create_time DATE_SUB(CURDATE(), INTERVAL 1 YEAR);注意第二段的写法起点用CURDATE()而不是NOW()终点用CURDATE() INTERVAL 1 DAY。这样整个区间对齐到自然日避免近 30 天在上午和下午查询结果条数不一样。这是个很小但很实用的习惯报表类需求我都这么写。自然周期口径要靠日期截断实现。MySQL 没有date_trunc得自己拼。-- 本周假设周一为一周起点 SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AS week_start; -- 本月一号 SELECT DATE_FORMAT(CURDATE(), %Y-%m-01) AS month_start; -- 本季度首日 SELECT MAKEDATE(YEAR(CURDATE()), 1) INTERVAL QUARTER(CURDATE()) - 1 QUARTER AS quarter_start; -- 本年首日 SELECT DATE_FORMAT(CURDATE(), %Y-01-01) AS year_start;WEEKDAY返回 0 到 60 代表周一所以CURDATE() - WEEKDAY(CURDATE())就是本周一。如果你的业务是周日算一周开始得换成DAYOFWEEK它返回 1 到 71 是周日算法要跟着调。MAKEDATE(YEAR(CURDATE()), 1)返回当年 1 月 1 日再叠加QUARTER()-1个季度。第一季度叠加 0 个季度还是 1 月 1 日第二季度叠加 1 个季度是 4 月 1 日逻辑是对的而且这个写法不需要CASE WHEN比较干净。上周、上月、上季度、去年同期只要把上面结果整体减一个周期就行。-- 上月整月 SELECT * FROM t_order WHERE create_time DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), %Y-%m-01) AND create_time DATE_FORMAT(CURDATE(), %Y-%m-01);用字符串比较日期在这里是安全的因为%Y-%m-%d格式的字典序和日期序一致。但要注意如果create_time是DATETIME类型MySQL 会做隐式转换虽然能跑但索引有可能用不上。更稳的写法是用STR_TO_DATE显式转换或者干脆用DATE函数处理。关于索引的事第 4 节会专门讲。2.3 人大金仓走 PostgreSQL 那套 interval 运算人大金仓在 PostgreSQL 兼容模式下时间运算可以直接用interval做加减读起来比 MySQL 更接近自然语言。-- 近 7 天 SELECT * FROM t_order WHERE create_time now() - interval 7 day AND create_time now(); -- 近 1 个月 SELECT * FROM t_order WHERE create_time now() - interval 1 month; -- 近 1 个季度 SELECT * FROM t_order WHERE create_time now() - interval 3 month; -- 近 1 年 SELECT * FROM t_order WHERE create_time now() - interval 1 year;interval 1 month和interval 30 day在这里不是一回事。前者是自然月遇到 3 月 31 日减一个月会得到 2 月 28 日或 29 日后者是固定 30 天。做月度口径一定要用 month用 day 会随月份长短漂移。自然周期截断用date_trunc这是个大杀器参数支持year、quarter、month、week、day、hour。-- 本周一 SELECT date_trunc(week, current_date)::date AS week_start; -- 本季度首日 SELECT date_trunc(quarter, current_date)::date AS quarter_start; -- 本月首日 SELECT date_trunc(month, current_date)::date AS month_start; -- 本年首日 SELECT date_trunc(year, current_date)::date AS year_start;注意date_trunc返回的是timestamp加::date强制转成日期这样后续做等值比较更干净。这一步别省我见过因为类型不匹配导致BETWEEN少算一整天的案例。用截断函数拼区间-- 本月数据 SELECT * FROM t_order WHERE create_time date_trunc(month, current_date) AND create_time date_trunc(month, current_date) interval 1 month; -- 上季度数据 SELECT * FROM t_order WHERE create_time date_trunc(quarter, current_date) - interval 3 month AND create_time date_trunc(quarter, current_date);这种截断 加一个周期的写法我特别推荐因为它天然形成左闭右开区间不用去纠结最后一天是 30 还是 31 号也不用LAST_DAY那套。如果实例跑在 Oracle 兼容模式下ADD_MONTHS、TRUNC、SYSDATE也能用写法可以直接参考下一节的达梦版本。但同一个项目里两套风格混着写维护起来会很痛苦建议统一。2.4 达梦add_days / add_months / trunc 三件套达梦的函数风格偏 Oracle日期加减是函数式日期截断用TRUNC加格式串。-- 近 7 天 SELECT * FROM t_order WHERE create_time ADD_DAYS(SYSDATE, -7) AND create_time SYSDATE; -- 近 1 个月 SELECT * FROM t_order WHERE create_time ADD_MONTHS(SYSDATE, -1); -- 近 1 个季度 SELECT * FROM t_order WHERE create_time ADD_MONTHS(SYSDATE, -3); -- 近 1 年 SELECT * FROM t_order WHERE create_time ADD_MONTHS(SYSDATE, -12);ADD_DAYS的第二个参数可以为负减几天就传负数不需要单独一个减法函数。ADD_MONTHS同理。这两个函数是我在达梦里用得最多的稳定性很好。日期截断用TRUNC第二个参数是格式串支持YYYY、MM、DD、Q、WW周一为一周起始、HH24等。-- 今天零点 SELECT TRUNC(SYSDATE) FROM DUAL; -- 本月一号 SELECT TRUNC(SYSDATE, MM) FROM DUAL; -- 本季度首日 SELECT TRUNC(SYSDATE, Q) FROM DUAL; -- 本年一月一号 SELECT TRUNC(SYSDATE, YYYY) FROM DUAL; -- 本周一 SELECT TRUNC(SYSDATE, WW) FROM DUAL;DUAL表在达梦里是虚拟表做无表查询时必须带上这点和 Oracle 一致。忘了写会报语法错误是个新手常踩的坑。拼区间-- 本月数据 SELECT * FROM t_order WHERE create_time TRUNC(SYSDATE, MM) AND create_time ADD_MONTHS(TRUNC(SYSDATE, MM), 1); -- 上周数据 SELECT * FROM t_order WHERE create_time TRUNC(SYSDATE, WW) - 7 AND create_time TRUNC(SYSDATE, WW); -- 上季度数据 SELECT * FROM t_order WHERE create_time ADD_MONTHS(TRUNC(SYSDATE, Q), -3) AND create_time TRUNC(SYSDATE, Q);TRUNC(SYSDATE, WW) - 7这种写法能跑是因为达梦对 DATE 类型支持直接加减数字数字单位是天。这个特性和 Oracle 一样用起来挺方便但可读性不如ADD_DAYS团队代码规范里最好统一一种。2.5 三家通用的参数化封装思路如果项目要同时支持三个库又不是用 ORM 自动生成 SQL最省事的办法是在应用层算好区间端点再把两个Date对象传进 SQL。这样 SQL 里只剩一句WHERE create_time ? AND create_time ?三家完全通用。计算端点的代码以 Java 为例用java.time包// 近 7 天滚动 LocalDateTime end LocalDateTime.now(); LocalDateTime start end.minusDays(7); // 本月自然月 LocalDate firstDay LocalDate.now().withDayOfMonth(1); LocalDateTime start firstDay.atStartOfDay(); LocalDateTime end firstDay.plusMonths(1).atStartOfDay(); // 本季度 LocalDate today LocalDate.now(); int firstMonthOfQuarter ((today.getMonthValue() - 1) / 3) * 3 1; LocalDate quarterStart LocalDate.of(today.getYear(), firstMonthOfQuarter, 1); LocalDateTime start quarterStart.atStartOfDay(); LocalDateTime end quarterStart.plusMonths(3).atStartOfDay();这么做有三个好处一是 SQL 变得极简迁移时几乎不用改二是时间计算能力 Java 比 SQL 强得多季度、周、工作日这些算起来更灵活三是可以给区间端点加索引性能更好。坏处也有区间逻辑散在代码里每个查询都得写一遍。解决办法是抽一个工具类把近N天本月上季度这些封装成静态方法全项目复用。我见过比较成熟的做法是定义一组枚举比如RangeType.LAST_7_DAYS然后统一走一个DateRangeResolver解析成起止时间。这样以后口径要改只改一处。3. 两个日期相差多少年、月、日这是比取区间更容易出错的一块因为它涉及满不满一个月满不满一年的判定。不同库的判定规则不一样同一个库不同函数的规则也可能不一样。3.1 差值的两种口径满减口径与自然口径先分清楚两种完全不同的算法。满减口径也叫实际经历时长从 1 月 31 日到 2 月 28 日算不算一个月按满减口径没满一个月只差 28 天。MySQL 的TIMESTAMPDIFF(MONTH, ...)走的就是这个逻辑它返回的是完整月份的个数。自然口径也叫日历口径同样是从 1 月 31 日到 2 月 28 日按日历看月份从 1 变成了 2算差一个月。Oracle 系的MONTHS_BETWEEN走的是偏日期的逻辑1 月 31 日到 2 月 28 日在很多实现里返回 1但如果两端都是月末返回的可能是整数。这两种口径在人事、合同、账期这些场景里必须明确选一个。工龄计算、合同剩余期通常用满减口径月度账单、订阅周期通常用自然口径。选错了不会报错但结果没法看。3.2 MySQL 的 TIMESTAMPDIFF 与 PERIOD_DIFF 怎么配合MySQL 里算差值主力是三个函数。SELECT TIMESTAMPDIFF(YEAR, 2020-03-15, 2024-05-20) AS diff_year, TIMESTAMPDIFF(MONTH, 2020-03-15, 2024-05-20) AS diff_month, TIMESTAMPDIFF(DAY, 2020-03-15, 2024-05-20) AS diff_day, DATEDIFF(2024-05-20, 2020-03-15) AS diff_day_2;TIMESTAMPDIFF的规则是满一个单位才算一个。返回 4 年、50 个月、1527 天。注意diff_day和diff_day_2都是 1527DATEDIFF本质上就是TIMESTAMPDIFF(DAY, ...)的简写。如果要拆成几年几个月几天这种组合结果光靠TIMESTAMPDIFF不够得配合取模和日期回推。SET d1 2020-01-31; SET d2 2024-05-20; SELECT TIMESTAMPDIFF(MONTH, d1, d2) DIV 12 AS y, TIMESTAMPDIFF(MONTH, d1, d2) MOD 12 AS m, DATEDIFF(d2, DATE_ADD(d1, INTERVAL TIMESTAMPDIFF(MONTH, d1, d2) MONTH)) AS d;思路是先算出总月数除以 12 得年模 12 得月再把起始日期加上总月数用DATEDIFF算剩下的天数。结果就是 4 年 3 月 20 天。这里有个坑要提前说DATE_ADD在月末会收缩。比如d1 2020-01-31加 1 个月得到2020-02-29加 2 个月得到2020-03-31加 3 个月得到2020-04-30。这种收缩会让天数差出现 1 到 2 天的跳动。做账期计算的必须对月末日期做显式约定比如统一把起始日截断到月初或者对账时允许一天的容差。PERIOD_DIFF是另一个算月份差的函数但它只认YYYYMM格式的整数用起来不如图省事SELECT PERIOD_DIFF(DATE_FORMAT(2024-05-20,%Y%m), DATE_FORMAT(2020-03-15,%Y%m));它返回的就是 50和TIMESTAMPDIFF(MONTH, ...)结果一样但它不受小的日期偏差影响实际上更接近自然口径的月份差。如果业务要的是月份数而不是满月数用这个更合适。3.3 人大金仓 age() 与 extract 拆解人大金仓在 PostgreSQL 兼容模式下有个专门算差值的函数age()直接返回一个interval格式就是X 年 X 月 X 日非常省事。SELECT age(timestamp 2024-05-20, timestamp 2020-03-15); -- 结果4 years 3 mons 5 days这个结果已经把年月日都拆好了。要单独取值用extractSELECT extract(year from age(timestamp 2024-05-20, timestamp 2020-03-15)) AS y, extract(month from age(timestamp 2024-05-20, timestamp 2020-03-15)) AS m, extract(day from age(timestamp 2024-05-20, timestamp 2020-03-15)) AS d;extract取interval的year字段时返回的就是那个4不是总年数。想取总月数得自己算SELECT extract(year from age(d2, d1)) * 12 extract(month from age(d2, d1)) AS total_months FROM (SELECT timestamp 2020-03-15 AS d1, timestamp 2024-05-20 AS d2) t;age()的规则是满减口径和 MySQL 的TIMESTAMPDIFF思路一致月末收缩的处理也类似。我个人比较喜欢age()因为一次调用就能得到完整年月日不用来回拼。需要提醒的是age()的两个参数如果传反了返回的interval会带负号不会报错。所以参数顺序一定要按结束时间在前、开始时间在后来写或者直接在注释里标清楚。3.4 达梦 months_between 与 datediff 的脾气达梦提供了MONTHS_BETWEEN这个函数返回的是带小数的月数是 Oracle 那一套的计算方式。SELECT MONTHS_BETWEEN(DATE 2024-05-20, DATE 2020-03-15) FROM DUAL; -- 结果50.1612903整数部分 50 就是完整的月份数小数部分是按 31 天一个月折算出来的天数比例。想要整数直接TRUNC或者FLOOR。想要年月日拆解可以用下面这套组合SELECT TRUNC(MONTHS_BETWEEN(d2, d1) / 12) AS y, TRUNC(MOD(MONTHS_BETWEEN(d2, d1), 12)) AS m, d2 - ADD_MONTHS(d1, TRUNC(MONTHS_BETWEEN(d2, d1))) AS d FROM (SELECT DATE 2020-03-15 AS d1, DATE 2024-05-20 AS d2 FROM DUAL) t;达梦的日期相减直接返回天数所以最后一行不用再套函数这个挺方便的。DATEDIFF在达梦里也能用但要注意参数顺序和 MySQL 相反SELECT DATEDIFF(DAY, DATE 2020-03-15, DATE 2024-05-20) FROM DUAL; -- 1527 SELECT DATEDIFF(MONTH, DATE 2020-03-15, DATE 2024-05-20) FROM DUAL; -- 50 SELECT DATEDIFF(YEAR, DATE 2020-03-15, DATE 2024-05-20) FROM DUAL; -- 4第一个参数是单位后面两个是日期返回第二个减第一个的差值。这个顺序我反复在代码注释里标注过因为从 MySQL 迁过来的同事十有八九会写反。MONTHS_BETWEEN还有一个特性要留意两个参数如果都是月末它会返回整数。比如MONTHS_BETWEEN(2024-02-29, 2024-01-31)返回 1尽管日期数字上差了 29 天。这个行为在账期计算里经常被当作正确来用你如果不知道这个规则会以为函数算错了。3.5 手工算年月日一套跨库都能跑的算法说实话如果你的项目要同时兼容三个库最靠得住的办法是在应用层算。SQL 里只存两个日期字段差值在 Java 里做这样三家库的行为完全一致。Java 8 以后的Period类就是为这个设计的。LocalDate start LocalDate.of(2020, 3, 15); LocalDate end LocalDate.of(2024, 5, 20); Period p Period.between(start, end); System.out.println(p.getYears() 年 p.getMonths() 月 p.getDays() 天); // 输出4 年 2 月 5 天Period.between用的是满减口径逻辑清晰不受数据库影响。如果业务要的是总天数或总月数用ChronoUnitlong days ChronoUnit.DAYS.between(start, end); long months ChronoUnit.MONTHS.between(start, end); long years ChronoUnit.YEARS.between(start, end);ChronoUnit.MONTHS和 SQL 里的TIMESTAMPDIFF(MONTH, ...)结果一致DAYS也一样。这样迁移的时候只要把 SQL 里的计算字段去掉改成应用层补算对不上的风险就小很多。如果确实必须在 SQL 里算又想让三家都能跑可以退一步只算总天数。总天数是唯一在三家库里都能稳定算出来的量。查出来之后年月日的拆解交给展示层。这个折中方案我推过好几次接受度都挺高。-- MySQL SELECT DATEDIFF(d2, d1) AS total_days FROM t; -- 人大金仓 SELECT (d2::date - d1::date) AS total_days FROM t; -- 达梦 SELECT DATEDIFF(DAY, d1, d2) AS total_days FROM t;三条 SQL 结果完全相同改动量最小。4. 边界、时区与性能真正会翻车的地方功能能跑通只是及格线真正决定这套时间查询能不能上生产的是下面这三件事。4.1 区间开闭与今天算不算我处理过一起线上事故原因是今日订单数在下午两点之后开始比实际少。排查发现SQL 写的是WHERE create_time BETWEEN CURDATE() AND NOW()BETWEEN是闭区间CURDATE()是今天零点NOW()是当前时刻。看起来没问题但create_time字段在写入时如果带了毫秒或者时间字段被截断到秒某些记录会落在NOW()之后的一瞬间——因为 SQL 里的NOW()是语句开始时间而插入操作还在继续。结果是正在写入的数据永远查不到全量。正确写法是左闭右开并且把上界设成一个稳定的未来点WHERE create_time CURDATE() AND create_time CURDATE() INTERVAL 1 DAY这样不管什么时候查今天的数据都是全的不会因为语句执行时刻不同而变化。这个习惯我从那次事故之后就一直保持。还有一种情况是近 7 天到底含不含今天。含今天就是今天加上前 6 天不含今天就是前 7 天到昨天。运营口径通常是含今天财务口径通常不含。这个必须问清楚我一般直接在 SQL 注释里写明白比如-- 近7天含当天共7个自然日。4.2 函数包字段索引直接失效这是性能问题里最常见的一个。看这两条 SQL-- 写法一索引失效 SELECT * FROM t_order WHERE DATE(create_time) CURDATE(); -- 写法二走索引 SELECT * FROM t_order WHERE create_time CURDATE() AND create_time CURDATE() INTERVAL 1 DAY;写法一里DATE(create_time)是一个计算表达式数据库没法直接用create_time上的索引去定位只能全表扫描后逐行算。数据量小的时候感觉不出来上到千万级就是几秒和几十毫秒的区别。这个规则在三个库里都成立。人大金仓和达梦同样会对函数包字段的操作做全表扫描除非你专门建函数索引。所以写时间查询时把计算放在常量那一侧不要放在字段那一侧。同理下面这些写法都应该改问题写法推荐写法WHERE YEAR(create_time) 2024WHERE create_time 2024-01-01 AND create_time 2025-01-01WHERE DATE_FORMAT(create_time,%Y%m) 202405WHERE create_time 2024-05-01 AND create_time 2024-06-01WHERE TRUNC(SYSDATE) - create_time 7WHERE create_time TRUNC(SYSDATE) - 7WHERE create_time 0 ...直接比较原字段如果业务上确实需要频繁按某年某月查询另一种办法是加一个冗余列比如create_month VARCHAR(7)写入时一起写然后在这个列上建索引。空间换时间在报表场景里经常是划算的。4.3 时区与时间类型的选型时间字段选DATETIME还是TIMESTAMP这个决定的影响比大多数人想的大。MySQL 的TIMESTAMP存储时会转成 UTC读取时按当前会话的时区转回来。同一份数据会话时区不同查出来的值不一样。而DATETIME原样存储不做任何转换。跨时区业务如果用了TIMESTAMP又没统一会话时区报表数字对不上是必然的。人大金仓对应的是timestamp without time zone和timestamp with time zone后者会带时区偏移。达梦也有TIMESTAMP WITH TIME ZONE类型。我的经验是业务时间字段统一用不带时区的类型应用层统一用 UTC 存储、按用户时区展示。这样数据库行为可预期时区转换只在一处发生出问题好定位。还有一个坑数据库服务器的系统时区和应用服务器的时区不一致。这个在容器化部署里特别常见因为基础镜像的默认时区经常是 UTC。上线前拿一句SELECT NOW()在三家库上各跑一次和应用服务器的时间对比一下差值不是 0 就说明有问题先修时区再上线。4.4 大数据量下的落地建议时间查询在数据量上去之后优化思路无非三条缩小扫描范围、让范围走索引、减少回表。第一条尽量把查询范围收窄。报表如果只需要统计值不要SELECT *把字段列出来能走覆盖索引最好。第二条索引的列顺序要讲究。常见组合是(租户ID, create_time)这种把等值条件放前面范围条件放后面。如果反过来写成(create_time, 租户ID)范围条件一出现后面的列就用不上了。第三条对于近 7 天这种高频查询如果表特别大可以考虑按时间做分区。MySQL 支持RANGE分区人大金仓支持声明式分区达梦也有分区表。按天或者按月分区之后查询会自动裁剪掉不需要的分区扫描量能降一个数量级。不过分区表有维护成本得定期加新分区、清理旧分区小表没必要上。5. 常见报错与排查实录写这一节是因为我发现时间相关的报错信息普遍不友好光看提示很难定位。下面这些是我和同事实际遇到过的。5.1 三库常见报错对照表现象可能原因处理方式MySQL 报Incorrect datetime value字符串格式与字段类型不匹配或严格模式下日期非法用STR_TO_DATE显式转换检查2024-02-30这类非法日期人大金仓报operator does not exist: timestamp - integer直接对 timestamp 减数字PG 风格不支持改成- interval 7 day或先::date再减达梦报无效的日期格式日期字符串格式与库参数不匹配用TO_DATE(2024-05-20,YYYY-MM-DD)显式转换达梦TRUNC返回结果不对格式串写错比如MONTH而非MM用官方支持的短格式YYYY、MM、DD、Q三库都有近一个月条数比预期少ADD_MONTHS或TIMESTAMPDIFF的月末收缩对月末日期做显式约定或改用天数口径时间区间查询结果随时段变化用了NOW()做上界区间不封闭改用自然日边界左闭右开查询突然变慢函数包字段导致索引失效把计算移到常量侧改写为范围条件跨库迁移后负数变正数DATEDIFF参数顺序不同MySQL 是(大, 小)达梦是(单位, 小, 大)逐个核对注意达梦的DATEDIFF参数顺序和 MySQL 相反这件事我用显眼的注释在代码里标了三次还是被同事写反过。如果团队里有从 MySQL 转过来的成员建议在代码规范里单独列一条。5.2 迁移改造时踩过的几个坑第一个坑是字段类型跟着变。MySQL 的DATETIME迁到达梦可能被映射成TIMESTAMP精度和范围都不一样。DATETIME支持的范围比较大TIMESTAMP的精度和时区行为又不同。迁移工具自动转换之后一定要抽样比对一下极值日期比如 1900 年和 2100 年的记录能不能正常存取。第二个坑是默认值和NOW()的差异。MySQL 里DEFAULT CURRENT_TIMESTAMP很常用到了人大金仓写法是DEFAULT now()到达梦又是DEFAULT SYSDATE。建表语句直接搬会失败得改。第三个坑是日期字面量的写法。MySQL 里2024-05-20直接比较没问题人大金仓在严格模式下字符串和 timestamp 比较可能走隐式转换性能不好达梦更推荐DATE 2024-05-20这种标准字面量写法。统一用标准写法跨库兼容性最好。第四个坑是函数在兼容模式下不存在。前面提过date_trunc在 PG 兼容模式下有在纯 Oracle 兼容模式下可能没有ADD_MONTHS反过来。迁移前把这些函数列一个清单在目标库上逐条验证比事后救火省事得多。第五个坑是季度起始日的定义。绝大多数库是按自然季度1-3 月、4-6 月、7-9 月、10-12 月算的但个别业务方用的是财年季度。这个不是技术问题是口径问题但写代码的人经常会自己默认成自然季度然后和业务方对不上数。评审时问一句你们的季度是自然季度还是财年季度能省很多返工。6. 一套可以直接抄的落地方案前面几节把原理和坑都铺完了这一节给一套我实际用过的方案适合同时维护三个库、又不想在 SQL 里纠缠差异的团队。核心思路是把时间计算全部收到应用层SQL 里只保留最原始的范围比较。第一步定义一个时间区间对象包含起点和终点终点永远是开区间。public class TimeRange { private final LocalDateTime start; // 闭 private final LocalDateTime end; // 开 // 构造方法省略 }第二步写一个解析器把业务口径字符串转成区间对象。public static TimeRange of(String type) { LocalDate today LocalDate.now(); switch (type) { case LAST_7_DAYS: return new TimeRange(today.minusDays(6).atStartOfDay(), today.plusDays(1).atStartOfDay()); case THIS_MONTH: LocalDate monthStart today.withDayOfMonth(1); return new TimeRange(monthStart.atStartOfDay(), monthStart.plusMonths(1).atStartOfDay()); case THIS_QUARTER: int m ((today.getMonthValue() - 1) / 3) * 3 1; LocalDate qStart LocalDate.of(today.getYear(), m, 1); return new TimeRange(qStart.atStartOfDay(), qStart.plusMonths(3).atStartOfDay()); case THIS_YEAR: LocalDate yStart LocalDate.of(today.getYear(), 1, 1); return new TimeRange(yStart.atStartOfDay(), yStart.plusYears(1).atStartOfDay()); default: throw new IllegalArgumentException(不支持的口径 type); } }注意每个口径用的都是起点 一个周期的写法而不是最后一天 23:59:59。这样天然避开闰年、月末、跨年这些边界问题区间也永远是左闭右开。第三步SQL 统一成一句。三个库的写法几乎一样SELECT * FROM t_order WHERE create_time #{start} AND create_time #{end}如果是 MyBatis#{start}会自动按字段类型绑定三个库都不用改。切换数据库的时候只要字段类型对应得上这句 SQL 一行都不动。第四步加索引。时间范围查询最常见的索引就是(tenant_id, create_time)或者(status, create_time)等值列在前时间列在后。这套方案我用在两个项目上一个要同时跑 MySQL 和达梦一个要跑人大金仓和达梦。迁移的时候时间查询这块基本没有返工只有少数几个地方因为字段类型映射问题调整了一下。最后分享一个我一直在用的小技巧给每个时间口径写一条验证 SQL。比如定义一个固定基准日2024-03-15把近 7 天本月本季度的起止时间人工算一遍写成断言。上线前在三个库上各跑一遍结果必须完全一致。这个动作花不了半小时但能挡住绝大部分因为口径理解偏差导致的数据错误。比起数据对不上之后花几天排查这笔投入太划算了。我个人在实际操作中的体会是时间查询这件事难点从来不在函数本身而在口径的确认和边界的设计。函数写错编译器会告诉你口径理解错只有业务方对账的时候才会告诉你而那个时候往往已经发了几轮报表了。 人大金仓、MySQL、达梦时间函数差异与跨库迁移指南
1. 三个库的时间函数先把底子摸清楚人大金仓、MySQL、达梦这三家放在一起做时间查询是很多做信创改造的团队绕不开的场景。MySQL 的DATE_SUB、人大金仓 KingbaseES 的interval运算、达梦 DM8 的add_days与trunc单看名字只是函数不同实际语义差得挺远。我在一个报表项目里就吃过亏同一句近一个月的 SQL 从 MySQL 迁到人大金仓数据没问题再切到达梦之后条数突然少了几条排查大半天最后发现是月末日期进位规则不一致导致的。所以这篇东西主要写给两类人一类是需要同时维护 MySQL、人大金仓、达梦三种库的开发和运维另一类是正在做数据库迁移、需要把时间类 SQL 逐条改写的人。文中涉及的写法都是我在实际库里跑过或者验证过的取数口径、参数怎么算、为什么这么算我尽量都讲透。新手看能照着抄老手看能对着排查。1.1 为什么同一句时间 SQL 换个库就报错先说清楚一件事SQL 标准里对时间类型的定义其实很宽真正落到实现层各家都加了自己的方言。最典型的三处分歧几乎每次迁移都会撞上。第一处是当前时间函数。MySQL 用NOW()和CURDATE()人大金仓跟着 PostgreSQL 走用now()、current_date、current_timestamp达梦更偏 Oracle 一路SYSDATE、CURRENT_DATE都能用NOW()在部分版本里也能识别但行为未必和 MySQL 一致。这里有个细节要特别注意MySQL 的NOW()取的是语句开始执行的时间同一条 SQL 里调用多次结果完全一样而SYSDATE()是实时取每调用一次重新读一次系统时间。生产里做主从复制或者批量写入打时间戳这两个混用会出现同一条记录里两个时间字段对不上的诡异现象。第二处是日期加减的写法。MySQL 是DATE_ADD(d, INTERVAL 7 DAY)这种带关键字的写法人大金仓直接支持d interval 7 day的算术运算达梦用的是函数式ADD_DAYS(d, 7)、ADD_MONTHS(d, 1)。三种语法互不兼容硬套必然报错。第三处是日期截断。你要算本季度的起始日期MySQL 得绕一下用MAKEDATE配合QUARTER人大金仓用date_trunc(quarter, ...)达梦用TRUNC(d, Q)。这三个函数不仅名字不同返回值的类型也不同——MySQL 返回日期字符串人大金仓返回 timestamp达梦返回 date。类型不同后续再参与运算就又可能出错。提示迁移前先确认目标库处于哪种兼容模式。人大金仓和达梦都提供多种兼容模式同一个函数在不同模式下的可用性、返回值都可能不一样这一步不做后面全白干。1.2 三家常用时间函数对照表我整理了一张对照表覆盖日常取数最常用的十来组场景。表里的写法都是最简形式实际使用时字段类型要匹配比如date和timestamp在参与比较时行为是有差异的。需求MySQL人大金仓 KingbaseES达梦 DM8当前日期时间NOW()/SYSDATE()now()/current_timestampSYSDATE/NOW()当前日期CURDATE()current_dateCURRENT_DATE/TRUNC(SYSDATE)加 N 天DATE_ADD(d, INTERVAL n DAY)d interval n dayADD_DAYS(d, n)加 N 月DATE_ADD(d, INTERVAL n MONTH)d interval n monthADD_MONTHS(d, n)减 N 天DATE_SUB(d, INTERVAL n DAY)d - interval n dayADD_DAYS(d, -n)两日期相差天数DATEDIFF(d1, d2)d1 - d2date 类型DATEDIFF(DAY, d2, d1)两日期相差月数TIMESTAMPDIFF(MONTH, d1, d2)extract(year from age(d1,d2))*12 extract(month from age(d1,d2))MONTHS_BETWEEN(d1, d2)截断到月首DATE_FORMAT(d, %Y-%m-01)date_trunc(month, d)TRUNC(d, MM)截断到季首MAKEDATE(YEAR(d),1) INTERVAL QUARTER(d)-1 QUARTERdate_trunc(quarter, d)TRUNC(d, Q)截断到年首DATE_FORMAT(d, %Y-01-01)date_trunc(year, d)TRUNC(d, YYYY)当月最后一天LAST_DAY(d)(date_trunc(month, d) interval 1 month - 1 day)::dateLAST_DAY(d)看着挺规整实际上每一行都可能藏雷。举两个我在项目里真实碰到的例子。第一个是DATEDIFF的参数顺序。MySQL 的DATEDIFF(d1, d2)返回d1 - d2达梦的DATEDIFF(part, d1, d2)返回的是d2 - d1。方向正好反了。我见过一个统计逾期天数的口径迁移之后所有负数变成了正数报表显示无一逾期差点当成业务好转上报还好被复核拦下了。第二个是ADD_MONTHS的月末进位。Oracle 系达梦也沿用了这套规则的ADD_MONTHS(2024-01-31, 1)返回2024-02-29会自动收缩到当月最后一天MySQL 的DATE_ADD(2024-01-31, INTERVAL 1 MONTH)同样返回2024-02-29看着一致但换成2024-03-31减一个月MySQL 给2024-02-29而某些库的实现给的是2024-02-28或者直接报错。做对账类业务时这种一天的差别足够让整张报表对不平。1.3 兼容模式这个坑先确认再写代码人大金仓的兼容模式不是一个小开关它在初始化数据库实例时就要定下来后面改起来非常麻烦。大致分两类一类是偏 PostgreSQL 的行为interval运算、date_trunc、age这些函数是原生支持的另一类是偏 Oracle 的行为ADD_MONTHS、MONTHS_BETWEEN、TRUNC、SYSDATE这些能直接用。达梦的情况类似虽然默认更贴近 Oracle 语法但也提供了与其他数据库兼容的参数。实际项目中如果代码里混着写两套语法一定会有某些函数在某个模式下不存在。我的建议很直接在项目启动阶段就把目标库的模式问清楚写进技术方案文档然后在测试库里跑一遍你的全部时间 SQL。别等上线前一夜才发现某个函数不存在。验证方法很简单把本文后面几节的 SQL 拿到你的实例上逐条执行一遍能跑通基本就没大问题。这里还有一个容易忽略的点函数存在不等于语义一致。有些库为了兼容会提供一个同名函数但内部实现不同。比如date_trunc的week参数PostgreSQL 系默认按周一开始某些兼容实现按周日开始。这类差异不会报错只会让数据悄悄偏掉只能靠构造边界日期做验证。2. 取近期数据的写法近几天、一周、一月、季度、一年这一节是实操主体。我把近 N 天本周本月本季度本年这些高频需求按三个库分别写一遍同时把口径问题讲清楚。2.1 先定口径滚动区间还是自然周期这是最容易被跳过、事后扯皮最多的一步。近一周这三个字至少有两种理解。一种是滚动区间从当前时刻往前推 7 天比如今天是 3 月 15 日 10 点那就是 3 月 8 日 10 点 到 3 月 15 日 10 点。这种口径常见于监控、风控、实时看板好处是不受日期边界影响任意时刻查询结果都是稳定的 7 天。另一种是自然周期本周从周一到今天或者本周一到周日。这种口径常见于经营报表、考勤、财务因为它要跟日历对齐跨周不能混算。这两种口径写出来的 SQL 完全不同前者用加减法就够了后者必须先做日期截断。我建议在需求评审阶段就用一句话把口径写死比如近 7 天指含当前时刻在内的滚动 168 小时避免后面反复改。还有一个隐藏的口径问题边界含不含。区间是[今天, 7天前]还是(今天, 7天前]如果字段是DATETIME并且经常带时分秒用闭区间很容易把前一天的最后一秒漏掉或多算一天。稳妥的做法一律用左闭右开WHERE create_time 起始 AND create_time 结束下面所有示例我都按左闭右开来写。2.2 MySQLDATE_SUB 与 INTERVAL 的组合拳MySQL 的写法最直观核心是DATE_SUB、DATE_ADD和INTERVAL关键字。先看滚动区间。-- 近 7 天滚动含今天 SELECT * FROM t_order WHERE create_time DATE_SUB(NOW(), INTERVAL 7 DAY) AND create_time NOW(); -- 近 30 天 SELECT * FROM t_order WHERE create_time DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND create_time CURDATE() INTERVAL 1 DAY; -- 近 1 年 SELECT * FROM t_order WHERE create_time DATE_SUB(CURDATE(), INTERVAL 1 YEAR);注意第二段的写法起点用CURDATE()而不是NOW()终点用CURDATE() INTERVAL 1 DAY。这样整个区间对齐到自然日避免近 30 天在上午和下午查询结果条数不一样。这是个很小但很实用的习惯报表类需求我都这么写。自然周期口径要靠日期截断实现。MySQL 没有date_trunc得自己拼。-- 本周假设周一为一周起点 SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AS week_start; -- 本月一号 SELECT DATE_FORMAT(CURDATE(), %Y-%m-01) AS month_start; -- 本季度首日 SELECT MAKEDATE(YEAR(CURDATE()), 1) INTERVAL QUARTER(CURDATE()) - 1 QUARTER AS quarter_start; -- 本年首日 SELECT DATE_FORMAT(CURDATE(), %Y-01-01) AS year_start;WEEKDAY返回 0 到 60 代表周一所以CURDATE() - WEEKDAY(CURDATE())就是本周一。如果你的业务是周日算一周开始得换成DAYOFWEEK它返回 1 到 71 是周日算法要跟着调。MAKEDATE(YEAR(CURDATE()), 1)返回当年 1 月 1 日再叠加QUARTER()-1个季度。第一季度叠加 0 个季度还是 1 月 1 日第二季度叠加 1 个季度是 4 月 1 日逻辑是对的而且这个写法不需要CASE WHEN比较干净。上周、上月、上季度、去年同期只要把上面结果整体减一个周期就行。-- 上月整月 SELECT * FROM t_order WHERE create_time DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), %Y-%m-01) AND create_time DATE_FORMAT(CURDATE(), %Y-%m-01);用字符串比较日期在这里是安全的因为%Y-%m-%d格式的字典序和日期序一致。但要注意如果create_time是DATETIME类型MySQL 会做隐式转换虽然能跑但索引有可能用不上。更稳的写法是用STR_TO_DATE显式转换或者干脆用DATE函数处理。关于索引的事第 4 节会专门讲。2.3 人大金仓走 PostgreSQL 那套 interval 运算人大金仓在 PostgreSQL 兼容模式下时间运算可以直接用interval做加减读起来比 MySQL 更接近自然语言。-- 近 7 天 SELECT * FROM t_order WHERE create_time now() - interval 7 day AND create_time now(); -- 近 1 个月 SELECT * FROM t_order WHERE create_time now() - interval 1 month; -- 近 1 个季度 SELECT * FROM t_order WHERE create_time now() - interval 3 month; -- 近 1 年 SELECT * FROM t_order WHERE create_time now() - interval 1 year;interval 1 month和interval 30 day在这里不是一回事。前者是自然月遇到 3 月 31 日减一个月会得到 2 月 28 日或 29 日后者是固定 30 天。做月度口径一定要用 month用 day 会随月份长短漂移。自然周期截断用date_trunc这是个大杀器参数支持year、quarter、month、week、day、hour。-- 本周一 SELECT date_trunc(week, current_date)::date AS week_start; -- 本季度首日 SELECT date_trunc(quarter, current_date)::date AS quarter_start; -- 本月首日 SELECT date_trunc(month, current_date)::date AS month_start; -- 本年首日 SELECT date_trunc(year, current_date)::date AS year_start;注意date_trunc返回的是timestamp加::date强制转成日期这样后续做等值比较更干净。这一步别省我见过因为类型不匹配导致BETWEEN少算一整天的案例。用截断函数拼区间-- 本月数据 SELECT * FROM t_order WHERE create_time date_trunc(month, current_date) AND create_time date_trunc(month, current_date) interval 1 month; -- 上季度数据 SELECT * FROM t_order WHERE create_time date_trunc(quarter, current_date) - interval 3 month AND create_time date_trunc(quarter, current_date);这种截断 加一个周期的写法我特别推荐因为它天然形成左闭右开区间不用去纠结最后一天是 30 还是 31 号也不用LAST_DAY那套。如果实例跑在 Oracle 兼容模式下ADD_MONTHS、TRUNC、SYSDATE也能用写法可以直接参考下一节的达梦版本。但同一个项目里两套风格混着写维护起来会很痛苦建议统一。2.4 达梦add_days / add_months / trunc 三件套达梦的函数风格偏 Oracle日期加减是函数式日期截断用TRUNC加格式串。-- 近 7 天 SELECT * FROM t_order WHERE create_time ADD_DAYS(SYSDATE, -7) AND create_time SYSDATE; -- 近 1 个月 SELECT * FROM t_order WHERE create_time ADD_MONTHS(SYSDATE, -1); -- 近 1 个季度 SELECT * FROM t_order WHERE create_time ADD_MONTHS(SYSDATE, -3); -- 近 1 年 SELECT * FROM t_order WHERE create_time ADD_MONTHS(SYSDATE, -12);ADD_DAYS的第二个参数可以为负减几天就传负数不需要单独一个减法函数。ADD_MONTHS同理。这两个函数是我在达梦里用得最多的稳定性很好。日期截断用TRUNC第二个参数是格式串支持YYYY、MM、DD、Q、WW周一为一周起始、HH24等。-- 今天零点 SELECT TRUNC(SYSDATE) FROM DUAL; -- 本月一号 SELECT TRUNC(SYSDATE, MM) FROM DUAL; -- 本季度首日 SELECT TRUNC(SYSDATE, Q) FROM DUAL; -- 本年一月一号 SELECT TRUNC(SYSDATE, YYYY) FROM DUAL; -- 本周一 SELECT TRUNC(SYSDATE, WW) FROM DUAL;DUAL表在达梦里是虚拟表做无表查询时必须带上这点和 Oracle 一致。忘了写会报语法错误是个新手常踩的坑。拼区间-- 本月数据 SELECT * FROM t_order WHERE create_time TRUNC(SYSDATE, MM) AND create_time ADD_MONTHS(TRUNC(SYSDATE, MM), 1); -- 上周数据 SELECT * FROM t_order WHERE create_time TRUNC(SYSDATE, WW) - 7 AND create_time TRUNC(SYSDATE, WW); -- 上季度数据 SELECT * FROM t_order WHERE create_time ADD_MONTHS(TRUNC(SYSDATE, Q), -3) AND create_time TRUNC(SYSDATE, Q);TRUNC(SYSDATE, WW) - 7这种写法能跑是因为达梦对 DATE 类型支持直接加减数字数字单位是天。这个特性和 Oracle 一样用起来挺方便但可读性不如ADD_DAYS团队代码规范里最好统一一种。2.5 三家通用的参数化封装思路如果项目要同时支持三个库又不是用 ORM 自动生成 SQL最省事的办法是在应用层算好区间端点再把两个Date对象传进 SQL。这样 SQL 里只剩一句WHERE create_time ? AND create_time ?三家完全通用。计算端点的代码以 Java 为例用java.time包// 近 7 天滚动 LocalDateTime end LocalDateTime.now(); LocalDateTime start end.minusDays(7); // 本月自然月 LocalDate firstDay LocalDate.now().withDayOfMonth(1); LocalDateTime start firstDay.atStartOfDay(); LocalDateTime end firstDay.plusMonths(1).atStartOfDay(); // 本季度 LocalDate today LocalDate.now(); int firstMonthOfQuarter ((today.getMonthValue() - 1) / 3) * 3 1; LocalDate quarterStart LocalDate.of(today.getYear(), firstMonthOfQuarter, 1); LocalDateTime start quarterStart.atStartOfDay(); LocalDateTime end quarterStart.plusMonths(3).atStartOfDay();这么做有三个好处一是 SQL 变得极简迁移时几乎不用改二是时间计算能力 Java 比 SQL 强得多季度、周、工作日这些算起来更灵活三是可以给区间端点加索引性能更好。坏处也有区间逻辑散在代码里每个查询都得写一遍。解决办法是抽一个工具类把近N天本月上季度这些封装成静态方法全项目复用。我见过比较成熟的做法是定义一组枚举比如RangeType.LAST_7_DAYS然后统一走一个DateRangeResolver解析成起止时间。这样以后口径要改只改一处。3. 两个日期相差多少年、月、日这是比取区间更容易出错的一块因为它涉及满不满一个月满不满一年的判定。不同库的判定规则不一样同一个库不同函数的规则也可能不一样。3.1 差值的两种口径满减口径与自然口径先分清楚两种完全不同的算法。满减口径也叫实际经历时长从 1 月 31 日到 2 月 28 日算不算一个月按满减口径没满一个月只差 28 天。MySQL 的TIMESTAMPDIFF(MONTH, ...)走的就是这个逻辑它返回的是完整月份的个数。自然口径也叫日历口径同样是从 1 月 31 日到 2 月 28 日按日历看月份从 1 变成了 2算差一个月。Oracle 系的MONTHS_BETWEEN走的是偏日期的逻辑1 月 31 日到 2 月 28 日在很多实现里返回 1但如果两端都是月末返回的可能是整数。这两种口径在人事、合同、账期这些场景里必须明确选一个。工龄计算、合同剩余期通常用满减口径月度账单、订阅周期通常用自然口径。选错了不会报错但结果没法看。3.2 MySQL 的 TIMESTAMPDIFF 与 PERIOD_DIFF 怎么配合MySQL 里算差值主力是三个函数。SELECT TIMESTAMPDIFF(YEAR, 2020-03-15, 2024-05-20) AS diff_year, TIMESTAMPDIFF(MONTH, 2020-03-15, 2024-05-20) AS diff_month, TIMESTAMPDIFF(DAY, 2020-03-15, 2024-05-20) AS diff_day, DATEDIFF(2024-05-20, 2020-03-15) AS diff_day_2;TIMESTAMPDIFF的规则是满一个单位才算一个。返回 4 年、50 个月、1527 天。注意diff_day和diff_day_2都是 1527DATEDIFF本质上就是TIMESTAMPDIFF(DAY, ...)的简写。如果要拆成几年几个月几天这种组合结果光靠TIMESTAMPDIFF不够得配合取模和日期回推。SET d1 2020-01-31; SET d2 2024-05-20; SELECT TIMESTAMPDIFF(MONTH, d1, d2) DIV 12 AS y, TIMESTAMPDIFF(MONTH, d1, d2) MOD 12 AS m, DATEDIFF(d2, DATE_ADD(d1, INTERVAL TIMESTAMPDIFF(MONTH, d1, d2) MONTH)) AS d;思路是先算出总月数除以 12 得年模 12 得月再把起始日期加上总月数用DATEDIFF算剩下的天数。结果就是 4 年 3 月 20 天。这里有个坑要提前说DATE_ADD在月末会收缩。比如d1 2020-01-31加 1 个月得到2020-02-29加 2 个月得到2020-03-31加 3 个月得到2020-04-30。这种收缩会让天数差出现 1 到 2 天的跳动。做账期计算的必须对月末日期做显式约定比如统一把起始日截断到月初或者对账时允许一天的容差。PERIOD_DIFF是另一个算月份差的函数但它只认YYYYMM格式的整数用起来不如图省事SELECT PERIOD_DIFF(DATE_FORMAT(2024-05-20,%Y%m), DATE_FORMAT(2020-03-15,%Y%m));它返回的就是 50和TIMESTAMPDIFF(MONTH, ...)结果一样但它不受小的日期偏差影响实际上更接近自然口径的月份差。如果业务要的是月份数而不是满月数用这个更合适。3.3 人大金仓 age() 与 extract 拆解人大金仓在 PostgreSQL 兼容模式下有个专门算差值的函数age()直接返回一个interval格式就是X 年 X 月 X 日非常省事。SELECT age(timestamp 2024-05-20, timestamp 2020-03-15); -- 结果4 years 3 mons 5 days这个结果已经把年月日都拆好了。要单独取值用extractSELECT extract(year from age(timestamp 2024-05-20, timestamp 2020-03-15)) AS y, extract(month from age(timestamp 2024-05-20, timestamp 2020-03-15)) AS m, extract(day from age(timestamp 2024-05-20, timestamp 2020-03-15)) AS d;extract取interval的year字段时返回的就是那个4不是总年数。想取总月数得自己算SELECT extract(year from age(d2, d1)) * 12 extract(month from age(d2, d1)) AS total_months FROM (SELECT timestamp 2020-03-15 AS d1, timestamp 2024-05-20 AS d2) t;age()的规则是满减口径和 MySQL 的TIMESTAMPDIFF思路一致月末收缩的处理也类似。我个人比较喜欢age()因为一次调用就能得到完整年月日不用来回拼。需要提醒的是age()的两个参数如果传反了返回的interval会带负号不会报错。所以参数顺序一定要按结束时间在前、开始时间在后来写或者直接在注释里标清楚。3.4 达梦 months_between 与 datediff 的脾气达梦提供了MONTHS_BETWEEN这个函数返回的是带小数的月数是 Oracle 那一套的计算方式。SELECT MONTHS_BETWEEN(DATE 2024-05-20, DATE 2020-03-15) FROM DUAL; -- 结果50.1612903整数部分 50 就是完整的月份数小数部分是按 31 天一个月折算出来的天数比例。想要整数直接TRUNC或者FLOOR。想要年月日拆解可以用下面这套组合SELECT TRUNC(MONTHS_BETWEEN(d2, d1) / 12) AS y, TRUNC(MOD(MONTHS_BETWEEN(d2, d1), 12)) AS m, d2 - ADD_MONTHS(d1, TRUNC(MONTHS_BETWEEN(d2, d1))) AS d FROM (SELECT DATE 2020-03-15 AS d1, DATE 2024-05-20 AS d2 FROM DUAL) t;达梦的日期相减直接返回天数所以最后一行不用再套函数这个挺方便的。DATEDIFF在达梦里也能用但要注意参数顺序和 MySQL 相反SELECT DATEDIFF(DAY, DATE 2020-03-15, DATE 2024-05-20) FROM DUAL; -- 1527 SELECT DATEDIFF(MONTH, DATE 2020-03-15, DATE 2024-05-20) FROM DUAL; -- 50 SELECT DATEDIFF(YEAR, DATE 2020-03-15, DATE 2024-05-20) FROM DUAL; -- 4第一个参数是单位后面两个是日期返回第二个减第一个的差值。这个顺序我反复在代码注释里标注过因为从 MySQL 迁过来的同事十有八九会写反。MONTHS_BETWEEN还有一个特性要留意两个参数如果都是月末它会返回整数。比如MONTHS_BETWEEN(2024-02-29, 2024-01-31)返回 1尽管日期数字上差了 29 天。这个行为在账期计算里经常被当作正确来用你如果不知道这个规则会以为函数算错了。3.5 手工算年月日一套跨库都能跑的算法说实话如果你的项目要同时兼容三个库最靠得住的办法是在应用层算。SQL 里只存两个日期字段差值在 Java 里做这样三家库的行为完全一致。Java 8 以后的Period类就是为这个设计的。LocalDate start LocalDate.of(2020, 3, 15); LocalDate end LocalDate.of(2024, 5, 20); Period p Period.between(start, end); System.out.println(p.getYears() 年 p.getMonths() 月 p.getDays() 天); // 输出4 年 2 月 5 天Period.between用的是满减口径逻辑清晰不受数据库影响。如果业务要的是总天数或总月数用ChronoUnitlong days ChronoUnit.DAYS.between(start, end); long months ChronoUnit.MONTHS.between(start, end); long years ChronoUnit.YEARS.between(start, end);ChronoUnit.MONTHS和 SQL 里的TIMESTAMPDIFF(MONTH, ...)结果一致DAYS也一样。这样迁移的时候只要把 SQL 里的计算字段去掉改成应用层补算对不上的风险就小很多。如果确实必须在 SQL 里算又想让三家都能跑可以退一步只算总天数。总天数是唯一在三家库里都能稳定算出来的量。查出来之后年月日的拆解交给展示层。这个折中方案我推过好几次接受度都挺高。-- MySQL SELECT DATEDIFF(d2, d1) AS total_days FROM t; -- 人大金仓 SELECT (d2::date - d1::date) AS total_days FROM t; -- 达梦 SELECT DATEDIFF(DAY, d1, d2) AS total_days FROM t;三条 SQL 结果完全相同改动量最小。4. 边界、时区与性能真正会翻车的地方功能能跑通只是及格线真正决定这套时间查询能不能上生产的是下面这三件事。4.1 区间开闭与今天算不算我处理过一起线上事故原因是今日订单数在下午两点之后开始比实际少。排查发现SQL 写的是WHERE create_time BETWEEN CURDATE() AND NOW()BETWEEN是闭区间CURDATE()是今天零点NOW()是当前时刻。看起来没问题但create_time字段在写入时如果带了毫秒或者时间字段被截断到秒某些记录会落在NOW()之后的一瞬间——因为 SQL 里的NOW()是语句开始时间而插入操作还在继续。结果是正在写入的数据永远查不到全量。正确写法是左闭右开并且把上界设成一个稳定的未来点WHERE create_time CURDATE() AND create_time CURDATE() INTERVAL 1 DAY这样不管什么时候查今天的数据都是全的不会因为语句执行时刻不同而变化。这个习惯我从那次事故之后就一直保持。还有一种情况是近 7 天到底含不含今天。含今天就是今天加上前 6 天不含今天就是前 7 天到昨天。运营口径通常是含今天财务口径通常不含。这个必须问清楚我一般直接在 SQL 注释里写明白比如-- 近7天含当天共7个自然日。4.2 函数包字段索引直接失效这是性能问题里最常见的一个。看这两条 SQL-- 写法一索引失效 SELECT * FROM t_order WHERE DATE(create_time) CURDATE(); -- 写法二走索引 SELECT * FROM t_order WHERE create_time CURDATE() AND create_time CURDATE() INTERVAL 1 DAY;写法一里DATE(create_time)是一个计算表达式数据库没法直接用create_time上的索引去定位只能全表扫描后逐行算。数据量小的时候感觉不出来上到千万级就是几秒和几十毫秒的区别。这个规则在三个库里都成立。人大金仓和达梦同样会对函数包字段的操作做全表扫描除非你专门建函数索引。所以写时间查询时把计算放在常量那一侧不要放在字段那一侧。同理下面这些写法都应该改问题写法推荐写法WHERE YEAR(create_time) 2024WHERE create_time 2024-01-01 AND create_time 2025-01-01WHERE DATE_FORMAT(create_time,%Y%m) 202405WHERE create_time 2024-05-01 AND create_time 2024-06-01WHERE TRUNC(SYSDATE) - create_time 7WHERE create_time TRUNC(SYSDATE) - 7WHERE create_time 0 ...直接比较原字段如果业务上确实需要频繁按某年某月查询另一种办法是加一个冗余列比如create_month VARCHAR(7)写入时一起写然后在这个列上建索引。空间换时间在报表场景里经常是划算的。4.3 时区与时间类型的选型时间字段选DATETIME还是TIMESTAMP这个决定的影响比大多数人想的大。MySQL 的TIMESTAMP存储时会转成 UTC读取时按当前会话的时区转回来。同一份数据会话时区不同查出来的值不一样。而DATETIME原样存储不做任何转换。跨时区业务如果用了TIMESTAMP又没统一会话时区报表数字对不上是必然的。人大金仓对应的是timestamp without time zone和timestamp with time zone后者会带时区偏移。达梦也有TIMESTAMP WITH TIME ZONE类型。我的经验是业务时间字段统一用不带时区的类型应用层统一用 UTC 存储、按用户时区展示。这样数据库行为可预期时区转换只在一处发生出问题好定位。还有一个坑数据库服务器的系统时区和应用服务器的时区不一致。这个在容器化部署里特别常见因为基础镜像的默认时区经常是 UTC。上线前拿一句SELECT NOW()在三家库上各跑一次和应用服务器的时间对比一下差值不是 0 就说明有问题先修时区再上线。4.4 大数据量下的落地建议时间查询在数据量上去之后优化思路无非三条缩小扫描范围、让范围走索引、减少回表。第一条尽量把查询范围收窄。报表如果只需要统计值不要SELECT *把字段列出来能走覆盖索引最好。第二条索引的列顺序要讲究。常见组合是(租户ID, create_time)这种把等值条件放前面范围条件放后面。如果反过来写成(create_time, 租户ID)范围条件一出现后面的列就用不上了。第三条对于近 7 天这种高频查询如果表特别大可以考虑按时间做分区。MySQL 支持RANGE分区人大金仓支持声明式分区达梦也有分区表。按天或者按月分区之后查询会自动裁剪掉不需要的分区扫描量能降一个数量级。不过分区表有维护成本得定期加新分区、清理旧分区小表没必要上。5. 常见报错与排查实录写这一节是因为我发现时间相关的报错信息普遍不友好光看提示很难定位。下面这些是我和同事实际遇到过的。5.1 三库常见报错对照表现象可能原因处理方式MySQL 报Incorrect datetime value字符串格式与字段类型不匹配或严格模式下日期非法用STR_TO_DATE显式转换检查2024-02-30这类非法日期人大金仓报operator does not exist: timestamp - integer直接对 timestamp 减数字PG 风格不支持改成- interval 7 day或先::date再减达梦报无效的日期格式日期字符串格式与库参数不匹配用TO_DATE(2024-05-20,YYYY-MM-DD)显式转换达梦TRUNC返回结果不对格式串写错比如MONTH而非MM用官方支持的短格式YYYY、MM、DD、Q三库都有近一个月条数比预期少ADD_MONTHS或TIMESTAMPDIFF的月末收缩对月末日期做显式约定或改用天数口径时间区间查询结果随时段变化用了NOW()做上界区间不封闭改用自然日边界左闭右开查询突然变慢函数包字段导致索引失效把计算移到常量侧改写为范围条件跨库迁移后负数变正数DATEDIFF参数顺序不同MySQL 是(大, 小)达梦是(单位, 小, 大)逐个核对注意达梦的DATEDIFF参数顺序和 MySQL 相反这件事我用显眼的注释在代码里标了三次还是被同事写反过。如果团队里有从 MySQL 转过来的成员建议在代码规范里单独列一条。5.2 迁移改造时踩过的几个坑第一个坑是字段类型跟着变。MySQL 的DATETIME迁到达梦可能被映射成TIMESTAMP精度和范围都不一样。DATETIME支持的范围比较大TIMESTAMP的精度和时区行为又不同。迁移工具自动转换之后一定要抽样比对一下极值日期比如 1900 年和 2100 年的记录能不能正常存取。第二个坑是默认值和NOW()的差异。MySQL 里DEFAULT CURRENT_TIMESTAMP很常用到了人大金仓写法是DEFAULT now()到达梦又是DEFAULT SYSDATE。建表语句直接搬会失败得改。第三个坑是日期字面量的写法。MySQL 里2024-05-20直接比较没问题人大金仓在严格模式下字符串和 timestamp 比较可能走隐式转换性能不好达梦更推荐DATE 2024-05-20这种标准字面量写法。统一用标准写法跨库兼容性最好。第四个坑是函数在兼容模式下不存在。前面提过date_trunc在 PG 兼容模式下有在纯 Oracle 兼容模式下可能没有ADD_MONTHS反过来。迁移前把这些函数列一个清单在目标库上逐条验证比事后救火省事得多。第五个坑是季度起始日的定义。绝大多数库是按自然季度1-3 月、4-6 月、7-9 月、10-12 月算的但个别业务方用的是财年季度。这个不是技术问题是口径问题但写代码的人经常会自己默认成自然季度然后和业务方对不上数。评审时问一句你们的季度是自然季度还是财年季度能省很多返工。6. 一套可以直接抄的落地方案前面几节把原理和坑都铺完了这一节给一套我实际用过的方案适合同时维护三个库、又不想在 SQL 里纠缠差异的团队。核心思路是把时间计算全部收到应用层SQL 里只保留最原始的范围比较。第一步定义一个时间区间对象包含起点和终点终点永远是开区间。public class TimeRange { private final LocalDateTime start; // 闭 private final LocalDateTime end; // 开 // 构造方法省略 }第二步写一个解析器把业务口径字符串转成区间对象。public static TimeRange of(String type) { LocalDate today LocalDate.now(); switch (type) { case LAST_7_DAYS: return new TimeRange(today.minusDays(6).atStartOfDay(), today.plusDays(1).atStartOfDay()); case THIS_MONTH: LocalDate monthStart today.withDayOfMonth(1); return new TimeRange(monthStart.atStartOfDay(), monthStart.plusMonths(1).atStartOfDay()); case THIS_QUARTER: int m ((today.getMonthValue() - 1) / 3) * 3 1; LocalDate qStart LocalDate.of(today.getYear(), m, 1); return new TimeRange(qStart.atStartOfDay(), qStart.plusMonths(3).atStartOfDay()); case THIS_YEAR: LocalDate yStart LocalDate.of(today.getYear(), 1, 1); return new TimeRange(yStart.atStartOfDay(), yStart.plusYears(1).atStartOfDay()); default: throw new IllegalArgumentException(不支持的口径 type); } }注意每个口径用的都是起点 一个周期的写法而不是最后一天 23:59:59。这样天然避开闰年、月末、跨年这些边界问题区间也永远是左闭右开。第三步SQL 统一成一句。三个库的写法几乎一样SELECT * FROM t_order WHERE create_time #{start} AND create_time #{end}如果是 MyBatis#{start}会自动按字段类型绑定三个库都不用改。切换数据库的时候只要字段类型对应得上这句 SQL 一行都不动。第四步加索引。时间范围查询最常见的索引就是(tenant_id, create_time)或者(status, create_time)等值列在前时间列在后。这套方案我用在两个项目上一个要同时跑 MySQL 和达梦一个要跑人大金仓和达梦。迁移的时候时间查询这块基本没有返工只有少数几个地方因为字段类型映射问题调整了一下。最后分享一个我一直在用的小技巧给每个时间口径写一条验证 SQL。比如定义一个固定基准日2024-03-15把近 7 天本月本季度的起止时间人工算一遍写成断言。上线前在三个库上各跑一遍结果必须完全一致。这个动作花不了半小时但能挡住绝大部分因为口径理解偏差导致的数据错误。比起数据对不上之后花几天排查这笔投入太划算了。我个人在实际操作中的体会是时间查询这件事难点从来不在函数本身而在口径的确认和边界的设计。函数写错编译器会告诉你口径理解错只有业务方对账的时候才会告诉你而那个时候往往已经发了几轮报表了。