写SQL函数系列写到现在已经是第147章我越来越觉得真正值得反复讲的不是那些炫技的分析函数而是像TO_TIMESTAMP这种“平时不起眼、报错想骂人”的转换函数。你在关联查询、窗口函数里折腾半天未必会碰它一次可一旦碰上“字符串转时间戳”的需求格式串、时区、数据库方言这几个坎就够从入门到放弃了。这篇我打算把TO_TIMESTAMP从语法、格式模板到跨数据库替代方案完整拆一遍顺便把这些年踩过的坑都列出来适合天天跟报表、ETL、日志解析打交道的同学收藏备查。1. 先给 TO_TIMESTAMP 定个位它到底解决什么问题1.1 为什么“字符串”不是“时间”很多新手第一次见TO_TIMESTAMP会觉得很奇怪我直接拿字符串比大小不行吗答案是不行而且后果很隐蔽。数据库里的字符串比较是按字符逐位排序的。比如你有一个字段存了日期格式是“2024-2-1”“2024-11-5”这种用字符串排序会得到什么2024-11-5会排在2024-2-1前面因为字符1比2小。如果业务报表靠这种排序做时间轴数据会乱成一锅粥。这还只是排序更麻烦的是区间查找、日期加减、按月汇总这些操作都需要真正的时间类型。TO_TIMESTAMP存在的意义就是让你把一段“长得像时间”的字符串安全地转换成数据库能理解的时间戳类型。转换之后你才能做时间之间的减法、比较大小、提取年月日星期才能在JOIN条件里和另一张表的时间字段对齐。它不是给系统看的是给业务逻辑用的。1.2 TO_TIMESTAMP 在数据链路中的位置做数据开发的人都有体会系统里最脏的数据往往就是“时间”。上游接口传过来的是字符串Excel导出来的是自定义格式日志文件里是带时区的文本甚至同一个字段今天和明天的格式都不一样。TO_TIMESTAMP这类函数本质上是数据清洗链路里的“标准化关卡”。它通常出现在三个场景ETL加载阶段从文件、接口把日志或业务数据拉进来先要把各种字符串时间统一成时间戳才能入仓库。报表参数处理前端传过来的是“2024-06-18 14:25:36”后台要拿它去查数据得先转换成时间。跨系统对账两个系统导出的时间格式不一致需要统一口径才能比较。当然不同数据库对“把字符串转成时间戳”这件事的命名和语法并不统一。我整理了一张常见数据库的转换函数对照表先看这个后面再展开讲数据库字符串转时间戳函数说明OracleTO_TIMESTAMP(char, fmt)正统TO_TIMESTAMP支持格式串PostgreSQLTO_TIMESTAMP(text, fmt)双版本数字时间戳也支持MySQLSTR_TO_DATE(str, fmt) / CAST / CONVERT名字不叫TO_TIMESTAMP但功能对应SQL ServerCONVERT(datetime, str, style) / TRY_CONVERT / PARSE用样式码控制格式思路不同SQLitedatetime() / strftime()内置时间函数无严格类型体系这张表最大的价值是提醒你别拿着Oracle的SQL直接扔到MySQL里跑一定会报错。2. 最关键的格式串读懂它才不会踩坑2.1 格式模板规则TO_TIMESTAMP的第二个参数是格式模板它告诉数据库“我给你的字符串长什么样”。格式模板写不对字符串再标准也转换失败。这一节我把主流格式元素拆开说尤其是那几个特别容易混淆的。格式元素含义示例输入说明YYYY四位数年份2024最推荐YY两位数年份自动补到当年世纪24有歧义不推荐RR近50年窗口的两位年份66、04Oracle特有老系统常见MM两位数月份06注意别和分钟搞混MON月份缩写Jun受语言环境影响MONTH月份全称June / 六月受语言环境影响DD两位数日期18HH2424小时制14推荐HH1212小时制02需要配合AM/PMMI分钟25经典易错点SS秒36FF1-FF9小数秒位数785Oracle重要特性MS毫秒785PostgreSQL里用US微秒785123PostgreSQL里用TZH时区小时偏移08需要配合时区类型TZM时区分钟偏移00需要配合时区类型AM / PM上午下午标识PM配HH12使用FM去掉前导零和填充字符FMYYYY-MM-DDOracle关键修饰符FX精确匹配格式FXYYYY.MM.DDOracle里要求完全一致这里面最容易翻车的是“MM”和“MI”。Oracle的分钟格式符是MI不是MM。字符串“14:25”对应的模板必须是“HH24:MI”如果你写成“HH24:MM”数据库会认为分钟位置是另一个月份数字直接报ORA-01810“格式代码出现两次”。这个梗在DBA圈子里流传了很久了。还有一个容易忽略的问题是格式串本身里的分隔符。数据库默认要求模板里的分隔符和字符串里的分隔符一致。字符串是“2024-06-18”模板里写“YYYY/MM/DD”就会报错。想要兼容多种分隔符要么写多个转换语句要么在Oracle里用FX精确匹配并配合“/”和“-”的手工转义。我个人建议工程上统一格式别偷懒兼容。2.2 格式模板实战示例看三个最常用的例子直接把写法记住。Oracle下转换标准日期时间SELECT TO_TIMESTAMP(2024-06-18 14:25:36.785, YYYY-MM-DD HH24:MI:SS.FF3) FROM dual;PostgreSQL下转换同样的字符串SELECT TO_TIMESTAMP(2024-06-18 14:25:36.785, YYYY-MM-DD HH24:MI:SS.MS);注意PostgreSQL里小数秒的格式符是MS毫秒和US微秒和Oracle的FF3不是一个体系。这条语法差异跨库迁移时几乎必踩。更复杂一点带时区的输入-- Oracle SELECT TO_TIMESTAMP_TZ(2024-06-18 14:25:36 08:00, YYYY-MM-DD HH24:MI:SS TZH:TZM) FROM dual; -- PostgreSQL SELECT TO_TIMESTAMP(2024-06-18 14:25:3608, YYYY-MM-DD HH24:MI:SS TZH);格式串读懂了函数就学完了一半。剩下的一半是不同数据库之间的语法细节。3. 四种数据库的实操对照3.1 OracleTO_TIMESTAMP 的正统用法Oracle的TO_TIMESTAMP将字符串转换为TIMESTAMP数据类型。它和TO_DATE最大的区别就是结果里保留了小数秒和时区信息。你如果只需要精确到秒TO_DATE就够了但一旦涉及毫秒、微秒就得使用TO_TIMESTAMP。基本语法TO_TIMESTAMP(char [, fmt] [, nlsparam])nlsparam是语言环境参数可以指定月份、星期名称的语言。例如SELECT TO_TIMESTAMP(2024年06月18日, YYYY年MM月DD日, NLS_LANGUAGESIMPLIFIED CHINESE) FROM dual;注意中文年月日这种写法裸字符串里的“年”“月”“日”必须用双引号包起来否则Oracle会试图把它们解析成格式元素。实际上Oracle的TIMESTAMP类型和DATE类型的区别很多人在面试里被问过。DATE精确到秒内部存储占用7字节TIMESTAMP精确到小数秒占用11字节。当你写一个时间字段时建议先想清楚业务到底需要什么精度。日志和交易流水一般需要毫秒级以上这时就该用TIMESTAMP。另外一个实战技巧是TO_TIMESTAMP返回的数据可以直接参与时间减法。比如计算两笔交易之间的间隔SELECT TO_TIMESTAMP(2024-06-18 14:25:36.785, YYYY-MM-DD HH24:MI:SS.FF3) - TO_TIMESTAMP(2024-06-18 14:25:36.100, YYYY-MM-DD HH24:MI:SS.FF3) AS diff_interval FROM dual;结果会得到一个INTERVAL类型精确显示到小数秒这是DATE类型很难做到的。3.2 PostgreSQL两个重载版本PostgreSQL内置了两个TO_TIMESTAMP一不留神就会选错-- 版本1文本转时间戳 TO_TIMESTAMP(text, text) -- 版本2Unix纪元秒转时间戳时间戳带时区 TO_TIMESTAMP(double precision)版本2经常被低估。当你拿到一个字段存的是Unix时间戳比如1718701536这种数字Oracle里你得先转成字符串再处理而PostgreSQL直接SELECT TO_TIMESTAMP(1718701536);一下就得到了带时区的时间戳。这个重载在数据同步场景里太常用了很多第三方接口返回的是Unix时间戳PostgreSQL直接就能转成标准时间。文本版本也值得细看。PostgreSQL的TO_TIMESTAMP返回的是timestamptz类型也就是带时区的时间戳。官方文档里明确写了它会把没有时区信息的输入解释成当前时区的时间。这点和Oracle有些不同Oracle默认会先按会话时区处理但在某些场景下会严格一些。跨库迁移时必须留意。小数秒的处理也是PostgreSQL的坑点。它的格式符是MS和USSELECT TO_TIMESTAMP(2024-06-18 14:25:36.785123, YYYY-MM-DD HH24:MI:SS.US);如果你的源数据是三位的毫秒就用MS如果是六位的微秒就用US。别混。PostgreSQL还有一个优点对非法输入的报错信息更友好会直接提示“invalid value for second”之类的具体字段。这是Oracle的ORA-01861望尘莫及的。3.3 MySQL没有 TO_TIMESTAMP 用什么MySQL没有TO_TIMESTAMP这个函数。实现同样目的有两条路第一条是STR_TO_DATESELECT STR_TO_DATE(2024-06-18 14:25:36, %Y-%m-%d %H:%i:%s);注意MySQL的格式符和Oracle完全不同分钟是“%i”不是“%M”。这是很多Oracle转MySQL的团队最容易错的地方。第二条更推荐利用MySQL默认格式规则SELECT CAST(2024-06-18 14:25:36 AS DATETIME);MySQL的DATETIME类型在字符串输入符合“YYYY-MM-DD HH:MM:SS”标准格式时可以直接CAST不需要写任何格式串。日常业务里你只要能保证上游传过来的字符串是标准格式CAST是最省心、性能也最好的方案。如果需要毫秒精度就用SELECT CAST(2024-06-18 14:25:36.785 AS DATETIME(3));DATETIME(3)表示保留3位小数秒括号里的数字就是精度位数。STR_TO_DATE还常用来解析非常规格式比如“18/06/2024”或者“20240618”SELECT STR_TO_DATE(20240618, %Y%m%d);如果在存储层做ETL我更建议多做一步把清洗后的时间统一改造成标准格式字符串再CAST让索引和分区策略都能用上。多花一步转换时间后面查询能快不少。3.4 SQL Server 的转换思路SQL Server的转换风格和前面几个数据库差别很大。它没有TO_TIMESTAMP用CONVERT加一个数字样式码处理SELECT CONVERT(DATETIME, 2024-06-18 14:25:36, 120);样式码120对应“yyyy-mm-dd hh:mi:ss”。常用样式码有几个需要背样式码格式示例120yyyy-mm-dd hh:mi:ss2024-06-18 14:25:36121同上带毫秒2024-06-18 14:25:36.785112yyyymmdd20240618111yyyy/mm/dd2024/06/1820短日期带时间2024-06-18 14:25:3623yyyy-mm-dd2024-06-18样式码最大的好处是统一缺点是难记且可读性差。进入G时代后微软推出了PARSE函数它可以用明确的格式串解析SELECT PARSE(2024-06-18 AS DATE USING zh-CN); SELECT TRY_CONVERT(DATETIME, 2024-06-18 14:25:36, 120);TRY_CONVERT和TRY_CAST是SQL Server 2012以后提供的“安全转换”版本。转换失败时不会直接报错而是返回NULL。做报表清洗时这种容错非常管用。如果你有一段数据里混着异常日期先用TRY_CONVERT把坏数据变成NULL再单独排查比直接让整个作业崩溃强太多。4. 时区、默认值和 NLS 那些隐性陷阱4.1 时区数据到底怎么处理字符串转时间戳对面最容易被忽略的就是时区。很多项目一开始跑得好好的到了跨国家部署突然数据就“差8小时”了。原因往往就是同一段字符串在A时区解析和B时区解析得到的时间点不同。Oracle里想保留时区信息不能只用TO_TIMESTAMP得用TO_TIMESTAMP_TZ结果类型是TIMESTAMP WITH TIME ZONESELECT TO_TIMESTAMP_TZ(2024-06-18 14:25:36 08:00, YYYY-MM-DD HH24:MI:SS TZH:TZM) FROM dual;PostgreSQL的TO_TIMESTAMP返回的本来就是带时区的timestamptz所以在文本解析时数据库会按当前时区解释没有时区后缀的字符串。想固定一个时区解析可以先用SET TIME ZONE指定SET TIME ZONE Asia/Shanghai; SELECT TO_TIMESTAMP(2024-06-18 14:25:36, YYYY-MM-DD HH24:MI:SS);MySQL则不太一样。DATETIME类型本身没有时区概念TIMESTAMP类型存储的是UTC时间在展示时换算成本地时区。跨时区业务下我建议统一把时间存成UTC在应用层做展示转换这样数据不会因为数据库参数变化而飘移。时区这块最容易出的实际问题是你在开发环境测试一切正常因为开发库和你的电脑是同一个时区但生产库一旦设置了UTC或者其他时区SQL里的“2024-06-18 14:25:36”就被解读成一个完全不同的时间点。排查这类问题第一步永远是先确认会话时区再确认字段类型。4.2 踩过的报错和解决方案时间转换类的报错几乎每个数据库工程师都背过几段血泪史。我把最常见的几个整理成了速查表报错信息数据库原因解决办法ORA-01861: literal does not match format stringOracle字符串内容和格式串不匹配对照格式串逐字符检查重点看分隔符和位数ORA-01843: not a valid monthOracle月份值不在1-12或格式串把分钟当成月份检查MM/MI混淆检查源数据是否含非法月份ORA-01810: format code appears twiceOracle格式串里MM用了两次分钟要用MI只有月份才用MMORA-01830: date format picture ends before converting entire input stringOracle格式串比字符串短给格式串补全HH24:MI:SS等元素invalid value for secondPostgreSQL秒数不在0-59范围先查源数据是否有60秒闰秒或脏数据date/time field value out of rangePostgreSQL年份超过1-9999或月日非法清洗源数据加过滤条件Conversion failed when converting date/time from character stringSQL Server字符串和样式码不匹配用TRY_CONVERT先容错把异常值捞出来无效的日期时间格式MySQLSTR_TO_DATE的格式符与字符串不匹配检查%Y/%m/%d等格式符大小写还有一个很隐蔽的坑是全角字符。从网页或者Excel粘贴出来的字符串经常混入全角空格、全角冒号“”肉眼看不出来但数据库解析时就是不对。我在处理这种问题时的习惯是先SELECT LENGTH()和DUMP()看每个字符的ASCII码直接把不可见字符揪出来。4.3 性能与索引注意事项TO_TIMESTAMP不是性能杀手但用法错了会疯狂拖慢查询。最常见的反面教材是某张表的时间字段存的是字符串查询时在WHERE条件里写WHERE TO_TIMESTAMP(start_time, YYYY-MM-DD HH24:MI:SS) SYSDATE这种写法的问题在于你给字段套了一层函数数据库的B树索引基本就废了。因为索引里存的是原始字符串不是转换后的时间戳优化器没法直接用它做范围扫描。数据量小的时候感觉不出来一旦上了千万行查询就是全表扫描能跑几十秒。正确做法有两种。第一种查询条件里不要动字段而是把右边的常量转成同类型WHERE start_time TO_CHAR(SYSDATE - 1, YYYY-MM-DD HH24:MI:SS)这样start_time本身不用转换索引可以正常命中。第二种在ETL阶段就把字符串列统一改成TIMESTAMP列查询时直接用时间字段比较这是最推荐的根治方案。另外如果表里有多个时间格式一定要在建表阶段就定好标准。我见过一些项目日志表里既有“YYYY-MM-DD HH24:MI:SS”又有“YYYY/MM/DD”风格清洗的时候为了兼容格式串得像写正则一样写一大坨转换逻辑不仅慢还容易漏。5. 把 TO_TIMESTAMP 用出经验感模板与搭配5.1 常用复合写法单独用TO_TIMESTAMP只是入门真正好用的时候是和其他函数搭配。场景一提取年月日和星期。转换出TIMESTAMP之后用EXTRACT提取-- Oracle SELECT EXTRACT(YEAR FROM TO_TIMESTAMP(2024-06-18 14:25:36, YYYY-MM-DD HH24:MI:SS)) AS year FROM dual; -- PostgreSQL SELECT DATE_PART(year, TO_TIMESTAMP(2024-06-18 14:25:36, YYYY-MM-DD HH24:MI:SS));场景二按小时、天做聚合。PostgreSQL的DATE_TRUNC非常强大SELECT DATE_TRUNC(hour, TO_TIMESTAMP(2024-06-18 14:25:36, YYYY-MM-DD HH24:MI:SS));半小时、一天、一周的粒度都能直接切配合GROUP BY做时间序列分析很方便。场景三写默认值。建表时直接把字符串转时间戳作为默认值保证新插入数据的时间格式统一CREATE TABLE api_log ( log_time TIMESTAMP DEFAULT TO_TIMESTAMP(2024-01-01 00:00:00, YYYY-MM-DD HH24:MI:SS) );这里只是一个初始默认值实际项目里更多是用CURRENT_TIMESTAMP但如果你想统一历史数据导入时的默认时间TO_TIMESTAMP这种写法很实用。5.2 我在项目里的转换模板做ETL这么多年我自己的习惯是给时间转换做一层“模板化封装”。比如Oracle里我写了一个很小的函数包专门处理常见的日期格式CREATE OR REPLACE FUNCTION parse_log_time(input_str IN VARCHAR2) RETURN TIMESTAMP IS BEGIN BEGIN RETURN TO_TIMESTAMP(input_str, YYYY-MM-DD HH24:MI:SS.FF3); EXCEPTION WHEN OTHERS THEN RETURN TO_TIMESTAMP(input_str, YYYY/MM/DD HH24:MI:SS); END; END;虽然Oracle的TO_TIMESTAMP没给原生的容错选项但你可以用异常捕获做到类似SQL Server TRY_CONVERT的效果。第一选择的标准格式解析不了就再试第二候选格式。这样即使上游接口临时改了格式数据任务也不会立刻挂掉。另一个经验是做大批量导入时先做“预清洗”而不是在INSERT语句里逐个转。比如从CSV导入两千万行你把清洗逻辑写成一个预处理步骤先用SQL或者脚本把时间字符串统一成标准格式导入时直接用CAST能比在每行INSERT里调用TO_TIMESTAMP快不少。当转换函数出现在海量数据的SELECT列表里时它的CPU开销也会被放大能提前转成不需要处理的类型就尽量提前。5.3 最后再提醒三个容易忽略的细节第一注意数据库版本差异。Oracle的TO_TIMESTAMP在19c、21c里的行为基本一致但PostgreSQL的TO_TIMESTAMP在旧版本里对某些格式符的支持并不完整。生产环境升级时要重新跑一遍日期转换的单元测试。第二小心“零值日期”。历史系统里偶尔会出现‘0000-00-00’这种非法日期你在Oracle和PostgreSQL里直接转会报错而MySQL在非严格模式下居然能存进去。这种脏数据不清理后续每次聚合都会炸。建议在转换前先加一层非空和格式校验。第三转换后的精度要保持一致。业务上如果统一保留三位毫秒所有转换都要用FF3或者DATETIME(3)否则有的行有小数秒有的行没有排序和JOIN时会产生边界问题。虽然影响不大但排查起来很让人头疼。我个人的体会是日期时间转换这类函数没有太多“高大上”的技巧拼的就是对格式串的熟悉程度和数据库方言的积累。你踩过的坑越多后面遇到类似需求就越稳。如果这篇文章能帮你少走几个弯路那就值了。
写SQL函数系列写到现在已经是第147章我越来越觉得真正值得反复讲的不是那些炫技的分析函数而是像TO_TIMESTAMP这种“平时不起眼、报错想骂人”的转换函数。你在关联查询、窗口函数里折腾半天未必会碰它一次可一旦碰上“字符串转时间戳”的需求格式串、时区、数据库方言这几个坎就够从入门到放弃了。这篇我打算把TO_TIMESTAMP从语法、格式模板到跨数据库替代方案完整拆一遍顺便把这些年踩过的坑都列出来适合天天跟报表、ETL、日志解析打交道的同学收藏备查。1. 先给 TO_TIMESTAMP 定个位它到底解决什么问题1.1 为什么“字符串”不是“时间”很多新手第一次见TO_TIMESTAMP会觉得很奇怪我直接拿字符串比大小不行吗答案是不行而且后果很隐蔽。数据库里的字符串比较是按字符逐位排序的。比如你有一个字段存了日期格式是“2024-2-1”“2024-11-5”这种用字符串排序会得到什么2024-11-5会排在2024-2-1前面因为字符1比2小。如果业务报表靠这种排序做时间轴数据会乱成一锅粥。这还只是排序更麻烦的是区间查找、日期加减、按月汇总这些操作都需要真正的时间类型。TO_TIMESTAMP存在的意义就是让你把一段“长得像时间”的字符串安全地转换成数据库能理解的时间戳类型。转换之后你才能做时间之间的减法、比较大小、提取年月日星期才能在JOIN条件里和另一张表的时间字段对齐。它不是给系统看的是给业务逻辑用的。1.2 TO_TIMESTAMP 在数据链路中的位置做数据开发的人都有体会系统里最脏的数据往往就是“时间”。上游接口传过来的是字符串Excel导出来的是自定义格式日志文件里是带时区的文本甚至同一个字段今天和明天的格式都不一样。TO_TIMESTAMP这类函数本质上是数据清洗链路里的“标准化关卡”。它通常出现在三个场景ETL加载阶段从文件、接口把日志或业务数据拉进来先要把各种字符串时间统一成时间戳才能入仓库。报表参数处理前端传过来的是“2024-06-18 14:25:36”后台要拿它去查数据得先转换成时间。跨系统对账两个系统导出的时间格式不一致需要统一口径才能比较。当然不同数据库对“把字符串转成时间戳”这件事的命名和语法并不统一。我整理了一张常见数据库的转换函数对照表先看这个后面再展开讲数据库字符串转时间戳函数说明OracleTO_TIMESTAMP(char, fmt)正统TO_TIMESTAMP支持格式串PostgreSQLTO_TIMESTAMP(text, fmt)双版本数字时间戳也支持MySQLSTR_TO_DATE(str, fmt) / CAST / CONVERT名字不叫TO_TIMESTAMP但功能对应SQL ServerCONVERT(datetime, str, style) / TRY_CONVERT / PARSE用样式码控制格式思路不同SQLitedatetime() / strftime()内置时间函数无严格类型体系这张表最大的价值是提醒你别拿着Oracle的SQL直接扔到MySQL里跑一定会报错。2. 最关键的格式串读懂它才不会踩坑2.1 格式模板规则TO_TIMESTAMP的第二个参数是格式模板它告诉数据库“我给你的字符串长什么样”。格式模板写不对字符串再标准也转换失败。这一节我把主流格式元素拆开说尤其是那几个特别容易混淆的。格式元素含义示例输入说明YYYY四位数年份2024最推荐YY两位数年份自动补到当年世纪24有歧义不推荐RR近50年窗口的两位年份66、04Oracle特有老系统常见MM两位数月份06注意别和分钟搞混MON月份缩写Jun受语言环境影响MONTH月份全称June / 六月受语言环境影响DD两位数日期18HH2424小时制14推荐HH1212小时制02需要配合AM/PMMI分钟25经典易错点SS秒36FF1-FF9小数秒位数785Oracle重要特性MS毫秒785PostgreSQL里用US微秒785123PostgreSQL里用TZH时区小时偏移08需要配合时区类型TZM时区分钟偏移00需要配合时区类型AM / PM上午下午标识PM配HH12使用FM去掉前导零和填充字符FMYYYY-MM-DDOracle关键修饰符FX精确匹配格式FXYYYY.MM.DDOracle里要求完全一致这里面最容易翻车的是“MM”和“MI”。Oracle的分钟格式符是MI不是MM。字符串“14:25”对应的模板必须是“HH24:MI”如果你写成“HH24:MM”数据库会认为分钟位置是另一个月份数字直接报ORA-01810“格式代码出现两次”。这个梗在DBA圈子里流传了很久了。还有一个容易忽略的问题是格式串本身里的分隔符。数据库默认要求模板里的分隔符和字符串里的分隔符一致。字符串是“2024-06-18”模板里写“YYYY/MM/DD”就会报错。想要兼容多种分隔符要么写多个转换语句要么在Oracle里用FX精确匹配并配合“/”和“-”的手工转义。我个人建议工程上统一格式别偷懒兼容。2.2 格式模板实战示例看三个最常用的例子直接把写法记住。Oracle下转换标准日期时间SELECT TO_TIMESTAMP(2024-06-18 14:25:36.785, YYYY-MM-DD HH24:MI:SS.FF3) FROM dual;PostgreSQL下转换同样的字符串SELECT TO_TIMESTAMP(2024-06-18 14:25:36.785, YYYY-MM-DD HH24:MI:SS.MS);注意PostgreSQL里小数秒的格式符是MS毫秒和US微秒和Oracle的FF3不是一个体系。这条语法差异跨库迁移时几乎必踩。更复杂一点带时区的输入-- Oracle SELECT TO_TIMESTAMP_TZ(2024-06-18 14:25:36 08:00, YYYY-MM-DD HH24:MI:SS TZH:TZM) FROM dual; -- PostgreSQL SELECT TO_TIMESTAMP(2024-06-18 14:25:3608, YYYY-MM-DD HH24:MI:SS TZH);格式串读懂了函数就学完了一半。剩下的一半是不同数据库之间的语法细节。3. 四种数据库的实操对照3.1 OracleTO_TIMESTAMP 的正统用法Oracle的TO_TIMESTAMP将字符串转换为TIMESTAMP数据类型。它和TO_DATE最大的区别就是结果里保留了小数秒和时区信息。你如果只需要精确到秒TO_DATE就够了但一旦涉及毫秒、微秒就得使用TO_TIMESTAMP。基本语法TO_TIMESTAMP(char [, fmt] [, nlsparam])nlsparam是语言环境参数可以指定月份、星期名称的语言。例如SELECT TO_TIMESTAMP(2024年06月18日, YYYY年MM月DD日, NLS_LANGUAGESIMPLIFIED CHINESE) FROM dual;注意中文年月日这种写法裸字符串里的“年”“月”“日”必须用双引号包起来否则Oracle会试图把它们解析成格式元素。实际上Oracle的TIMESTAMP类型和DATE类型的区别很多人在面试里被问过。DATE精确到秒内部存储占用7字节TIMESTAMP精确到小数秒占用11字节。当你写一个时间字段时建议先想清楚业务到底需要什么精度。日志和交易流水一般需要毫秒级以上这时就该用TIMESTAMP。另外一个实战技巧是TO_TIMESTAMP返回的数据可以直接参与时间减法。比如计算两笔交易之间的间隔SELECT TO_TIMESTAMP(2024-06-18 14:25:36.785, YYYY-MM-DD HH24:MI:SS.FF3) - TO_TIMESTAMP(2024-06-18 14:25:36.100, YYYY-MM-DD HH24:MI:SS.FF3) AS diff_interval FROM dual;结果会得到一个INTERVAL类型精确显示到小数秒这是DATE类型很难做到的。3.2 PostgreSQL两个重载版本PostgreSQL内置了两个TO_TIMESTAMP一不留神就会选错-- 版本1文本转时间戳 TO_TIMESTAMP(text, text) -- 版本2Unix纪元秒转时间戳时间戳带时区 TO_TIMESTAMP(double precision)版本2经常被低估。当你拿到一个字段存的是Unix时间戳比如1718701536这种数字Oracle里你得先转成字符串再处理而PostgreSQL直接SELECT TO_TIMESTAMP(1718701536);一下就得到了带时区的时间戳。这个重载在数据同步场景里太常用了很多第三方接口返回的是Unix时间戳PostgreSQL直接就能转成标准时间。文本版本也值得细看。PostgreSQL的TO_TIMESTAMP返回的是timestamptz类型也就是带时区的时间戳。官方文档里明确写了它会把没有时区信息的输入解释成当前时区的时间。这点和Oracle有些不同Oracle默认会先按会话时区处理但在某些场景下会严格一些。跨库迁移时必须留意。小数秒的处理也是PostgreSQL的坑点。它的格式符是MS和USSELECT TO_TIMESTAMP(2024-06-18 14:25:36.785123, YYYY-MM-DD HH24:MI:SS.US);如果你的源数据是三位的毫秒就用MS如果是六位的微秒就用US。别混。PostgreSQL还有一个优点对非法输入的报错信息更友好会直接提示“invalid value for second”之类的具体字段。这是Oracle的ORA-01861望尘莫及的。3.3 MySQL没有 TO_TIMESTAMP 用什么MySQL没有TO_TIMESTAMP这个函数。实现同样目的有两条路第一条是STR_TO_DATESELECT STR_TO_DATE(2024-06-18 14:25:36, %Y-%m-%d %H:%i:%s);注意MySQL的格式符和Oracle完全不同分钟是“%i”不是“%M”。这是很多Oracle转MySQL的团队最容易错的地方。第二条更推荐利用MySQL默认格式规则SELECT CAST(2024-06-18 14:25:36 AS DATETIME);MySQL的DATETIME类型在字符串输入符合“YYYY-MM-DD HH:MM:SS”标准格式时可以直接CAST不需要写任何格式串。日常业务里你只要能保证上游传过来的字符串是标准格式CAST是最省心、性能也最好的方案。如果需要毫秒精度就用SELECT CAST(2024-06-18 14:25:36.785 AS DATETIME(3));DATETIME(3)表示保留3位小数秒括号里的数字就是精度位数。STR_TO_DATE还常用来解析非常规格式比如“18/06/2024”或者“20240618”SELECT STR_TO_DATE(20240618, %Y%m%d);如果在存储层做ETL我更建议多做一步把清洗后的时间统一改造成标准格式字符串再CAST让索引和分区策略都能用上。多花一步转换时间后面查询能快不少。3.4 SQL Server 的转换思路SQL Server的转换风格和前面几个数据库差别很大。它没有TO_TIMESTAMP用CONVERT加一个数字样式码处理SELECT CONVERT(DATETIME, 2024-06-18 14:25:36, 120);样式码120对应“yyyy-mm-dd hh:mi:ss”。常用样式码有几个需要背样式码格式示例120yyyy-mm-dd hh:mi:ss2024-06-18 14:25:36121同上带毫秒2024-06-18 14:25:36.785112yyyymmdd20240618111yyyy/mm/dd2024/06/1820短日期带时间2024-06-18 14:25:3623yyyy-mm-dd2024-06-18样式码最大的好处是统一缺点是难记且可读性差。进入G时代后微软推出了PARSE函数它可以用明确的格式串解析SELECT PARSE(2024-06-18 AS DATE USING zh-CN); SELECT TRY_CONVERT(DATETIME, 2024-06-18 14:25:36, 120);TRY_CONVERT和TRY_CAST是SQL Server 2012以后提供的“安全转换”版本。转换失败时不会直接报错而是返回NULL。做报表清洗时这种容错非常管用。如果你有一段数据里混着异常日期先用TRY_CONVERT把坏数据变成NULL再单独排查比直接让整个作业崩溃强太多。4. 时区、默认值和 NLS 那些隐性陷阱4.1 时区数据到底怎么处理字符串转时间戳对面最容易被忽略的就是时区。很多项目一开始跑得好好的到了跨国家部署突然数据就“差8小时”了。原因往往就是同一段字符串在A时区解析和B时区解析得到的时间点不同。Oracle里想保留时区信息不能只用TO_TIMESTAMP得用TO_TIMESTAMP_TZ结果类型是TIMESTAMP WITH TIME ZONESELECT TO_TIMESTAMP_TZ(2024-06-18 14:25:36 08:00, YYYY-MM-DD HH24:MI:SS TZH:TZM) FROM dual;PostgreSQL的TO_TIMESTAMP返回的本来就是带时区的timestamptz所以在文本解析时数据库会按当前时区解释没有时区后缀的字符串。想固定一个时区解析可以先用SET TIME ZONE指定SET TIME ZONE Asia/Shanghai; SELECT TO_TIMESTAMP(2024-06-18 14:25:36, YYYY-MM-DD HH24:MI:SS);MySQL则不太一样。DATETIME类型本身没有时区概念TIMESTAMP类型存储的是UTC时间在展示时换算成本地时区。跨时区业务下我建议统一把时间存成UTC在应用层做展示转换这样数据不会因为数据库参数变化而飘移。时区这块最容易出的实际问题是你在开发环境测试一切正常因为开发库和你的电脑是同一个时区但生产库一旦设置了UTC或者其他时区SQL里的“2024-06-18 14:25:36”就被解读成一个完全不同的时间点。排查这类问题第一步永远是先确认会话时区再确认字段类型。4.2 踩过的报错和解决方案时间转换类的报错几乎每个数据库工程师都背过几段血泪史。我把最常见的几个整理成了速查表报错信息数据库原因解决办法ORA-01861: literal does not match format stringOracle字符串内容和格式串不匹配对照格式串逐字符检查重点看分隔符和位数ORA-01843: not a valid monthOracle月份值不在1-12或格式串把分钟当成月份检查MM/MI混淆检查源数据是否含非法月份ORA-01810: format code appears twiceOracle格式串里MM用了两次分钟要用MI只有月份才用MMORA-01830: date format picture ends before converting entire input stringOracle格式串比字符串短给格式串补全HH24:MI:SS等元素invalid value for secondPostgreSQL秒数不在0-59范围先查源数据是否有60秒闰秒或脏数据date/time field value out of rangePostgreSQL年份超过1-9999或月日非法清洗源数据加过滤条件Conversion failed when converting date/time from character stringSQL Server字符串和样式码不匹配用TRY_CONVERT先容错把异常值捞出来无效的日期时间格式MySQLSTR_TO_DATE的格式符与字符串不匹配检查%Y/%m/%d等格式符大小写还有一个很隐蔽的坑是全角字符。从网页或者Excel粘贴出来的字符串经常混入全角空格、全角冒号“”肉眼看不出来但数据库解析时就是不对。我在处理这种问题时的习惯是先SELECT LENGTH()和DUMP()看每个字符的ASCII码直接把不可见字符揪出来。4.3 性能与索引注意事项TO_TIMESTAMP不是性能杀手但用法错了会疯狂拖慢查询。最常见的反面教材是某张表的时间字段存的是字符串查询时在WHERE条件里写WHERE TO_TIMESTAMP(start_time, YYYY-MM-DD HH24:MI:SS) SYSDATE这种写法的问题在于你给字段套了一层函数数据库的B树索引基本就废了。因为索引里存的是原始字符串不是转换后的时间戳优化器没法直接用它做范围扫描。数据量小的时候感觉不出来一旦上了千万行查询就是全表扫描能跑几十秒。正确做法有两种。第一种查询条件里不要动字段而是把右边的常量转成同类型WHERE start_time TO_CHAR(SYSDATE - 1, YYYY-MM-DD HH24:MI:SS)这样start_time本身不用转换索引可以正常命中。第二种在ETL阶段就把字符串列统一改成TIMESTAMP列查询时直接用时间字段比较这是最推荐的根治方案。另外如果表里有多个时间格式一定要在建表阶段就定好标准。我见过一些项目日志表里既有“YYYY-MM-DD HH24:MI:SS”又有“YYYY/MM/DD”风格清洗的时候为了兼容格式串得像写正则一样写一大坨转换逻辑不仅慢还容易漏。5. 把 TO_TIMESTAMP 用出经验感模板与搭配5.1 常用复合写法单独用TO_TIMESTAMP只是入门真正好用的时候是和其他函数搭配。场景一提取年月日和星期。转换出TIMESTAMP之后用EXTRACT提取-- Oracle SELECT EXTRACT(YEAR FROM TO_TIMESTAMP(2024-06-18 14:25:36, YYYY-MM-DD HH24:MI:SS)) AS year FROM dual; -- PostgreSQL SELECT DATE_PART(year, TO_TIMESTAMP(2024-06-18 14:25:36, YYYY-MM-DD HH24:MI:SS));场景二按小时、天做聚合。PostgreSQL的DATE_TRUNC非常强大SELECT DATE_TRUNC(hour, TO_TIMESTAMP(2024-06-18 14:25:36, YYYY-MM-DD HH24:MI:SS));半小时、一天、一周的粒度都能直接切配合GROUP BY做时间序列分析很方便。场景三写默认值。建表时直接把字符串转时间戳作为默认值保证新插入数据的时间格式统一CREATE TABLE api_log ( log_time TIMESTAMP DEFAULT TO_TIMESTAMP(2024-01-01 00:00:00, YYYY-MM-DD HH24:MI:SS) );这里只是一个初始默认值实际项目里更多是用CURRENT_TIMESTAMP但如果你想统一历史数据导入时的默认时间TO_TIMESTAMP这种写法很实用。5.2 我在项目里的转换模板做ETL这么多年我自己的习惯是给时间转换做一层“模板化封装”。比如Oracle里我写了一个很小的函数包专门处理常见的日期格式CREATE OR REPLACE FUNCTION parse_log_time(input_str IN VARCHAR2) RETURN TIMESTAMP IS BEGIN BEGIN RETURN TO_TIMESTAMP(input_str, YYYY-MM-DD HH24:MI:SS.FF3); EXCEPTION WHEN OTHERS THEN RETURN TO_TIMESTAMP(input_str, YYYY/MM/DD HH24:MI:SS); END; END;虽然Oracle的TO_TIMESTAMP没给原生的容错选项但你可以用异常捕获做到类似SQL Server TRY_CONVERT的效果。第一选择的标准格式解析不了就再试第二候选格式。这样即使上游接口临时改了格式数据任务也不会立刻挂掉。另一个经验是做大批量导入时先做“预清洗”而不是在INSERT语句里逐个转。比如从CSV导入两千万行你把清洗逻辑写成一个预处理步骤先用SQL或者脚本把时间字符串统一成标准格式导入时直接用CAST能比在每行INSERT里调用TO_TIMESTAMP快不少。当转换函数出现在海量数据的SELECT列表里时它的CPU开销也会被放大能提前转成不需要处理的类型就尽量提前。5.3 最后再提醒三个容易忽略的细节第一注意数据库版本差异。Oracle的TO_TIMESTAMP在19c、21c里的行为基本一致但PostgreSQL的TO_TIMESTAMP在旧版本里对某些格式符的支持并不完整。生产环境升级时要重新跑一遍日期转换的单元测试。第二小心“零值日期”。历史系统里偶尔会出现‘0000-00-00’这种非法日期你在Oracle和PostgreSQL里直接转会报错而MySQL在非严格模式下居然能存进去。这种脏数据不清理后续每次聚合都会炸。建议在转换前先加一层非空和格式校验。第三转换后的精度要保持一致。业务上如果统一保留三位毫秒所有转换都要用FF3或者DATETIME(3)否则有的行有小数秒有的行没有排序和JOIN时会产生边界问题。虽然影响不大但排查起来很让人头疼。我个人的体会是日期时间转换这类函数没有太多“高大上”的技巧拼的就是对格式串的熟悉程度和数据库方言的积累。你踩过的坑越多后面遇到类似需求就越稳。如果这篇文章能帮你少走几个弯路那就值了。
