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