以JSON_TABLE为单位的日期不会节省时间

Question

我有这个代码，给定一个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",
"msg",
"level"
FROM
t
CROSS JOIN
JSON_TABLE ( log, '$'
COLUMNS (
NESTED PATH '$[*]'
COLUMNS (
"uuid" VARCHAR2 ( 36 ) PATH '$.uuid',
"date" DATE PATH '$.date',
"msg" VARCHAR2 ( 1024 ) PATH '$.msg',
"level" VARCHAR2 ( 5 ) PATH '$.level'
)
)
)

这只会将日期(2021-10-18)保存到数据库中，但不会保存时间。我如何保存日期和时间?

Answer 1

使用TIMESTAMP或TIMESTAMP WITH TIME ZONE:

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" AT TIME ZONE 'UTC',
"msg",
"level"
FROM   t
CROSS JOIN JSON_TABLE(
log,
'$[*]'
COLUMNS (
"uuid" VARCHAR2 ( 36 ) PATH '$.uuid',
"date" TIMESTAMP WITH TIME ZONE PATH '$.date',
"msg" VARCHAR2 ( 1024 ) PATH '$.msg',
"level" VARCHAR2 ( 5 ) PATH '$.level'
)
)

db<此处小提琴>