使用JSON_TABLE插入带时间的日期时仅保存日期不保存时间如何解决

• Oracle 直接用 DATE PATH '$.date' 解析带时区的ISO8601格式时间字符串时，默认转换规则没有匹配到完整的时间和时区信息，因此会截断时分秒部分，仅保留日期。
• 如果后续需要保留原始时区信息，建议将log表的date字段类型调整为TIMESTAMP WITH TIME ZONE；如果不需要时区，也可以用TIMESTAMP类型，两类都可以完整存储时分秒。

方案1：log表date字段保持DATE类型（不保留时区） #

你可以直接在JSON_TABLE中显式指定时间转换格式，适配ISO8601带时区的时间字符串：

INSERT INTO log (
   "uuid",
   "date",
   "msg",
   "level"
)
   WITH t ( log ) AS (
      SELECT
         JSON_QUERY('[{"uuid": "20000000-0000-0000-0000-000000000000", "date": "2021-10-18T13:49:15+01:00", "msg":"aaaa", "level": "debug" },
                  {"uuid": "20000000-0000-0000-0000-000000000000", "date": "2021-10-18T13:49:15+01:00", "msg":"bbbb", "level": "debug" }]'
         , '$')
      FROM
         dual
   )
   SELECT
      "uuid",
      "date",
      "msg",
      "level"
   FROM
      t
      CROSS JOIN
         JSON_TABLE ( log, '$'
            COLUMNS (
               NESTED PATH '$[*]'
                  COLUMNS (
                     "uuid" VARCHAR2 ( 36 ) PATH '$.uuid',
                     -- 显式指定ISO8601格式转换，自动转为数据库时区的DATE类型
                     "date" DATE PATH '$.date' FORMAT JSON 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM',
                     "msg" VARCHAR2 ( 1024 ) PATH '$.msg',
                     "level" VARCHAR2 ( 5 ) PATH '$.level'
                  )
            )
         )

如果你的Oracle版本不支持FORMAT JSON写法，可以用先取字符串再转换的兼容写法：

INSERT INTO log (
   "uuid",
   "date",
   "msg",
   "level"
)
   WITH t ( log ) AS (
      SELECT
         JSON_QUERY('[{"uuid": "20000000-0000-0000-0000-000000000000", "date": "2021-10-18T13:49:15+01:00", "msg":"aaaa", "level": "debug" },
                  {"uuid": "20000000-0000-0000-0000-000000000000", "date": "2021-10-18T13:49:15+01:00", "msg":"bbbb", "level": "debug" }]'
         , '$')
      FROM
         dual
   )
   SELECT
      "uuid",
      -- 先转带时区的时间戳，再转DATE类型
      CAST(TO_TIMESTAMP_TZ("date_str", 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') AS DATE) AS "date",
      "msg",
      "level"
   FROM
      t
      CROSS JOIN
         JSON_TABLE ( log, '$'
            COLUMNS (
               NESTED PATH '$[*]'
                  COLUMNS (
                     "uuid" VARCHAR2 ( 36 ) PATH '$.uuid',
                     "date_str" VARCHAR2(50) PATH '$.date',
                     "msg" VARCHAR2 ( 1024 ) PATH '$.msg',
                     "level" VARCHAR2 ( 5 ) PATH '$.level'
                  )
            )
         )

方案2：需要保留原始时区信息 #

先修改表结构，将date字段改为带时区的时间戳类型：

ALTER TABLE log MODIFY "date" TIMESTAMP WITH TIME ZONE;

再调整JSON_TABLE中的字段类型即可：

"date" TIMESTAMP WITH TIME ZONE PATH '$.date' FORMAT JSON 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'

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