Trino SQL提取含纳秒时间及时间比较方法技术问询

一、提取带纳秒的时间部分 #

1. 转为纳秒精度的time类型 #

Trino的time类型支持指定精度，默认仅保留整秒，显式指定精度为9即可保留纳秒：

CAST(my_date_time_variable AS TIME(9))

示例：

SELECT CAST('2020-05-11 11:15:05.123456789 +01:00' AS TIME(9));
-- 返回结果：11:15:05.123456789

2. 格式化为带纳秒的字符串 #

若需要字符串形式的时间部分，使用TO_CHAR指定纳秒格式：

TO_CHAR(my_date_time_variable, 'HH24:MI:SS.FF9')

示例：

SELECT TO_CHAR('2020-05-11 11:15:05.123456789 +01:00', 'HH24:MI:SS.FF9');
-- 返回结果：'11:15:05.123456789'

二、直接比较时间部分（含纳秒） #

无需构造日期+时间的组合，通过计算datetime与当日零点的时间间隔，直接和目标纳秒级时间间隔比较，完整保留精度：

WHERE (my_date_time_variable - DATE_TRUNC('day', my_date_time_variable)) 
  BETWEEN INTERVAL '09:30:00.000000000' HOUR TO NANOSECOND 
  AND INTERVAL '16:00:00.000000000' HOUR TO NANOSECOND

原理说明 #

• DATE_TRUNC('day', my_date_time_variable)将datetime截断到当日零点（如2020-05-11 00:00:00.000000000 +01:00）
• 两者相减得到当日的时间间隔（如0 11:15:05.123456789），完全保留纳秒精度
• 直接与指定的纳秒级时间间隔对比，避免原方法中date_add导致的精度丢失

三、修复原方法的精度问题 #

如果仍需基于日期构造时间范围，可直接利用datetime的日期部分生成精确区间：

WHERE my_date_time_variable 
  BETWEEN (DATE_TRUNC('day', my_date_time_variable) + INTERVAL '09:30:00.000000000' HOUR TO NANOSECOND)
  AND (DATE_TRUNC('day', my_date_time_variable) + INTERVAL '16:00:00.000000000' HOUR TO NANOSECOND)

该方式不会出现截断或四舍五入问题，完整保留纳秒精度。

内容的提问来源于stack exchange，提问作者Misha