Data Studio中ISO 8601文本转DateTime零值转换异常问题

异常原因 #

1. 时区格式不统一：零时区场景下，ISO8601字符串常以Z代替+000，此时你的代码中SUBSTR(BGInvoiceDate,24,3)会截取到非数字字符Z，转换为INT64时失败返回null，导致整个表达式结果为null。
2. 固定位置截取逻辑失效：当时间小时部分为00时，若字符串格式细节（如长度）发生变化，LEFT_TEXT(BGInvoiceDate,23)可能截取到包含时区符号的无效内容，导致PARSE_DATETIME解析失败返回null。

解决方案 #

放弃固定位置截取的方式，改用通用的ISO8601解析逻辑，同时统一时区格式：

方法1：直接转换为目标时区（推荐） #

利用PARSE_TIMESTAMP解析带时区的字符串，再直接转换为UTC+11时区的DateTime：

DATETIME(
  PARSE_TIMESTAMP("%Y-%m-%dT%H:%M:%E3S%z", REPLACE(BGInvoiceDate, "Z", "+000")),
  "+11:00"
)

• REPLACE(BGInvoiceDate, "Z", "+000")：将零时区的Z统一替换为+000，保证格式一致；
• PARSE_TIMESTAMP：正确解析所有标准ISO8601格式的时间字符串；
• DATETIME(..., "+11:00")：直接将解析后的时间转换为UTC+11时区的DateTime类型。

方法2：修复原有截取逻辑 #

如果要保留原有偏移计算逻辑，改用正则匹配来提取时间和时区部分：

DATETIME_ADD(
  PARSE_DATETIME("%FT%T.%E3S", REGEXP_EXTRACT(BGInvoiceDate, r"^(.+)\.[0-9]{3}(?:[+-]|Z)")),
  INTERVAL 11 - CAST(REGEXP_EXTRACT(REPLACE(BGInvoiceDate, "Z", "+000"), r"[+-](\d{3})") AS INT64)/100 HOUR
)

• REGEXP_EXTRACT：精准提取时间部分（到毫秒）和时区偏移数字，避免固定位置截取的误差；
• 同样先将Z替换为+000，保证时区偏移能被正确转换为数字。

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