I have this code which given the a JSON array inserts it in a table:
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'
)
)
)
This is only saving the date (2021-10-18) into the database, but not the time. How can I save date and time?
CodePudding user response:
Use TIMESTAMP
or 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<>fiddle here