TDengine 转化函数 TO_TIMESTAMP 用户手册-EW帮帮网

在这里插入图片描述

TDengine TO_TIMESTAMP 函数用户使用手册

函数概述

TO_TIMESTAMP 是 TDengine 中的标量函数，用于将字符串按照指定格式转换为时间戳。该函数在数据导入、时间格式转换、以及处理各种时间字符串格式时非常有用。

语法

TO_TIMESTAMP(ts_str_literal, format_str_literal)

参数说明

ts_str_literal: 时间字符串，VARCHAR 类型
format_str_literal: 格式字符串，VARCHAR 类型，定义了时间字符串的格式

返回值

数据类型: TIMESTAMP
精度: 与查询表的时间精度一致，若未指定表则默认为毫秒精度

版本支持

起始版本: v3.2.2.0
适用范围: 表和超级表
嵌套查询: 支持内层查询和外层查询

格式字符串说明

支持的格式标识符

基于源码分析，TDengine 支持以下格式标识符：

年份格式

YYYY/yyyy: 4位年份 (如: 2023)
YYY/yyy: 3位年份 (如: 023)
YY/yy: 2位年份 (如: 23)
Y/y: 1位年份 (如: 3)

月份格式

MM/mm: 2位月份数字 (01-12)
MONTH/month: 完整月份名称 (大小写不敏感)
MON/mon: 缩写月份名称 (大小写不敏感)
Month: 首字母大写的月份名称

日期格式

DD/dd: 2位日期 (01-31)
DDD/ddd: 一年中的第几天 (001-366)
D/d: 1位日期 (1-31)
DAY/day: 完整星期名称
DY/dy: 缩写星期名称

时间格式

HH24/hh24: 24小时制小时 (00-23)
HH12/hh12: 12小时制小时 (01-12)
HH/hh: 小时 (默认24小时制)
MI/mi: 分钟 (00-59)
SS/ss: 秒 (00-59)

亚秒精度格式

MS/ms: 毫秒 (000-999)
US/us: 微秒 (000000-999999)
NS/ns: 纳秒 (000000000-999999999)

上午/下午标识

AM/am/A.M./a.m.: 上午标识
PM/pm/P.M./p.m.: 下午标识

时区格式

TZH/tzh: 时区小时偏移

使用场景详解

1. 数据导入场景

当从外部系统导入数据时，时间格式可能多种多样：

-- 从 CSV 文件导入标准 ISO 格式时间
SELECT TO_TIMESTAMP('2023-10-15 14:30:25', 'YYYY-MM-DD HH24:MI:SS') as ts;

-- 导入美式日期格式
SELECT TO_TIMESTAMP('10/15/2023 2:30:25 PM', 'MM/DD/YYYY HH12:MI:SS PM') as ts;

-- 导入欧式日期格式
SELECT TO_TIMESTAMP('15.10.2023 14:30:25', 'DD.MM.YYYY HH24:MI:SS') as ts;

2. 日志分析场景

处理不同系统的日志时间格式：

-- Apache 日志格式
SELECT TO_TIMESTAMP('15/Oct/2023:14:30:25', 'DD/MON/YYYY:HH24:MI:SS') as log_time;

-- 应用程序日志格式
SELECT TO_TIMESTAMP('2023-10-15T14:30:25.123', 'YYYY-MM-DDTHH24:MI:SS.MS') as app_time;

-- 系统日志格式
SELECT TO_TIMESTAMP('Oct 15 14:30:25', 'MON DD HH24:MI:SS') as sys_time;

3. 数据清洗场景

清理和标准化不规范的时间数据：

-- 处理缺少前导零的时间
SELECT TO_TIMESTAMP('2023-1-5 9:5:5', 'YYYY-MM-DD HH24:MI:SS') as clean_time;

-- 处理包含多余空格的时间
SELECT TO_TIMESTAMP(' 2023 年 - 10 月 - 15 日 ', 'YYYY 年-MM月-DD日') as chinese_time;

4. 时区处理场景

处理带时区信息的时间字符串：

-- 处理带时区偏移的时间
SELECT TO_TIMESTAMP('2023-10-15 14:30:25+08', 'YYYY-MM-DD HH24:MI:SS+TZH') as tz_time;

-- 建议：为避免时区混乱，建议在时间字符串中包含时区信息
SELECT TO_TIMESTAMP('2023-10-15 14:30:25+08:00', 'YYYY-MM-DD HH24:MI:SS') as recommended;

5. 高精度时间处理场景

处理需要高精度的时间数据：

-- 纳秒精度时间戳 (需要在纳秒精度的表中查询)
SELECT TO_TIMESTAMP('2023-10-15 14:30:25.123456789', 'YYYY-MM-DD HH24:MI:SS.NS') 
FROM nano_precision_table LIMIT 1;

-- 同时指定毫秒、微秒、纳秒
SELECT TO_TIMESTAMP('2023-10-15 14:30:25.123.000456.000000789', 
                   'YYYY-MM-DD HH24:MI:SS.MS.US.NS') 
FROM nano_precision_table LIMIT 1;

实用示例

基础用法示例

-- 1. 标准 ISO 8601 格式
SELECT TO_TIMESTAMP('2023-10-15 14:30:25', 'YYYY-MM-DD HH24:MI:SS');

-- 2. 带毫秒的时间
SELECT TO_TIMESTAMP('2023-10-15 14:30:25.123', 'YYYY-MM-DD HH24:MI:SS.MS');

-- 3. 12小时制格式
SELECT TO_TIMESTAMP('2023-10-15 2:30:25 PM', 'YYYY-MM-DD HH12:MI:SS PM');

-- 4. 月份名称格式
SELECT TO_TIMESTAMP('15-October-2023 14:30:25', 'DD-MONTH-YYYY HH24:MI:SS');

容错机制示例

-- 格式串容错：多余的字符会被忽略
SELECT TO_TIMESTAMP('200101/2', 'yyyyMM1/dd');

-- 空格容错：多余的空格会被自动忽略  
SELECT TO_TIMESTAMP(' 23 年 - 1 月 - 01 日 ', 'yy 年-MM月-dd日');

-- 数字位数容错：不足两位的数字也能正确解析
SELECT TO_TIMESTAMP('2023-1-1', 'YYYY-MM-DD');

高级应用示例

-- 1. 在数据插入中使用
INSERT INTO sensor_data (ts, temperature) 
VALUES (TO_TIMESTAMP('2023-10-15 14:30:25', 'YYYY-MM-DD HH24:MI:SS'), 25.6);

-- 2. 在 WHERE 条件中使用
SELECT * FROM sensor_data 
WHERE ts >= TO_TIMESTAMP('2023-10-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

-- 3. 在聚合查询中使用
SELECT COUNT(*) FROM sensor_data 
WHERE ts BETWEEN TO_TIMESTAMP('2023-10-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
             AND TO_TIMESTAMP('2023-10-15 23:59:59', 'YYYY-MM-DD HH24:MI:SS');

-- 4. 与其他时间函数结合使用
SELECT TO_CHAR(TO_TIMESTAMP('2023-10-15 14:30:25', 'YYYY-MM-DD HH24:MI:SS'), 
               'YYYY年MM月DD日') as formatted_date;

注意事项和最佳实践

1. 精度处理

-- 注意：输出精度取决于查询的表
-- 在毫秒表中查询，纳秒部分会被截断
SELECT TO_TIMESTAMP('2023-10-15 14:30:25.123456789', 'YYYY-MM-DD HH24:MI:SS.NS') 
FROM millisecond_table LIMIT 1;  -- 结果：2023-10-15 14:30:25.123

-- 在纳秒表中查询，保留完整精度
SELECT TO_TIMESTAMP('2023-10-15 14:30:25.123456789', 'YYYY-MM-DD HH24:MI:SS.NS') 
FROM nanosecond_table LIMIT 1;   -- 结果：2023-10-15 14:30:25.123456789

2. 字段覆盖规则

-- 如果同一字段被指定多次，后面的会覆盖前面的
SELECT TO_TIMESTAMP('2023-22-10-10', 'yyyy-yy-MM-dd');  -- 年份结果是 2022

3. 时区处理建议

-- 推荐：明确指定时区信息
SELECT TO_TIMESTAMP('2023-10-15 14:30:25+08:00', 'YYYY-MM-DD HH24:MI:SS');

-- 避免：依赖默认时区可能导致混乱
SELECT TO_TIMESTAMP('2023-10-15 14:30:25', 'YYYY-MM-DD HH24:MI:SS');

4. 12小时制使用

-- 使用 AM/PM 时，小时必须是 12 小时制 (01-12)
SELECT TO_TIMESTAMP('2023-10-15 02:30:25 PM', 'YYYY-MM-DD HH12:MI:SS PM');  -- 正确
SELECT TO_TIMESTAMP('2023-10-15 14:30:25 PM', 'YYYY-MM-DD HH12:MI:SS PM');  -- 错误

错误处理

常见错误类型

格式不匹配错误 (TSDB_CODE_FUNC_TO_TIMESTAMP_FAILED_FORMAT_ERR)

-- 错误示例：格式与字符串不匹配
SELECT TO_TIMESTAMP('2023-10-15', 'YYYY/MM/DD');

时间戳错误 (TSDB_CODE_FUNC_TO_TIMESTAMP_FAILED_TS_ERR)

-- 错误示例：非法的时间值
SELECT TO_TIMESTAMP('2023-13-40', 'YYYY-MM-DD');

不支持的格式 (TSDB_CODE_FUNC_TO_TIMESTAMP_FAILED_NOT_SUPPORTED)

-- 错误示例：使用了不支持的格式标识符
SELECT TO_TIMESTAMP('2023-10-15', 'YYYY-QQ-DD');

性能优化建议

预编译格式字符串: 相同的格式字符串会被缓存，重复使用时性能更好
避免过长的格式字符串: 格式字符串有最大长度限制 (4096 字符)
批量处理: 在处理大量数据时，尽量使用批量操作而不是逐行转换

与其他时间函数的关系

-- TO_TIMESTAMP 与 TO_CHAR 是逆操作
SELECT TO_CHAR(TO_TIMESTAMP('2023-10-15', 'YYYY-MM-DD'), 'YYYY年MM月DD日');

-- 与 NOW()、TODAY() 结合使用
SELECT TO_TIMESTAMP('14:30:25', 'HH24:MI:SS') + (TODAY() - TO_TIMESTAMP('00:00:00', 'HH24:MI:SS'));

-- 与 TIMEDIFF 结合计算时间差
SELECT TIMEDIFF(NOW(), TO_TIMESTAMP('2023-10-15 14:30:25', 'YYYY-MM-DD HH24:MI:SS'));

总结

TO_TIMESTAMP 函数是 TDengine 中处理时间字符串转换的核心工具，支持丰富的格式选项和容错机制。合理使用该函数可以有效处理各种时间数据导入、清洗和转换需求。在使用时需要特别注意精度设置、时区处理和格式匹配，以确保数据的准确性和一致性。

TDengine 转化函数 TO_TIMESTAMP 用户手册