8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 18c » Here

TO_UTC_TIMESTAMP_TZ Function in Oracle Database 18c

Although JSON doesn't care what format you use for dates and timestamps, Javascript typically converts dates and timestamps to strings conforming to the ISO 8601 standard, so this has become common in JSON data. Oracle 18c introduced the new TO_UTC_TIMESTAMP_TZ function to help deal with ISO 8601 date strings.

The TO_UTC_TIMESTAMP_TZ function converts any valid ISO 8601 date represented as a string into a TIMESTAMP WITH TIMEZONE, which can optionally be used as input to the SYS_EXTRACT_UTC function.

Create a test table and populate it with some JSON documents containing valid ISO 8601 date strings.

--DROP TABLE json_documents PURGE;

CREATE TABLE json_documents (
  id    RAW(16) NOT NULL,
  data  CLOB,
  CONSTRAINT json_documents_pk PRIMARY KEY (id),
  CONSTRAINT json_documents_json_chk CHECK (data IS JSON)
);

INSERT INTO json_documents VALUES (SYS_GUID(), '{"datedata":"2018-10-26T21:32:52"}');
INSERT INTO json_documents VALUES (SYS_GUID(), '{"datedata":"2018-10-26T21:32:52+02:00"}');
INSERT INTO json_documents VALUES (SYS_GUID(), '{"datedata":"2018-10-26T19:32:52Z"}');
INSERT INTO json_documents VALUES (SYS_GUID(), '{"datedata":"2018-10-26T19:32:52+00:00"}');
INSERT INTO json_documents VALUES (SYS_GUID(), '{"datedata":"2018-10-26T21:32:52.12679"}');
COMMIT;

Query the contents of the test table, displaying the output of the TO_UTC_TIMESTAMP_TZ and SYS_EXTRACT_UTC functions.

SET LINESIZE 100
COLUMN datedata FORMAT A26
COLUMN utc_timestamp_tz FORMAT A39
COLUMN utc_timestamp FORMAT A32

SELECT jt.datedata,
       TO_UTC_TIMESTAMP_TZ(jt.datedata) AS utc_timestamp_tz,
       SYS_EXTRACT_UTC(TO_UTC_TIMESTAMP_TZ(jt.datedata)) AS utc_timestamp
FROM   json_documents jd,
       JSON_TABLE(data, '$'
         COLUMNS (datedata)) jt;

DATEDATA                   UTC_TIMESTAMP_TZ                        UTC_TIMESTAMP
-------------------------- --------------------------------------- --------------------------------
2018-10-26T21:32:52        26-OCT-18 09.32.52.000000000 PM +00:00  26-OCT-18 09.32.52.000000000 PM
2018-10-26T21:32:52+02:00  26-OCT-18 09.32.52.000000000 PM +02:00  26-OCT-18 07.32.52.000000000 PM
2018-10-26T19:32:52Z       26-OCT-18 07.32.52.000000000 PM +00:00  26-OCT-18 07.32.52.000000000 PM
2018-10-26T19:32:52+00:00  26-OCT-18 07.32.52.000000000 PM +00:00  26-OCT-18 07.32.52.000000000 PM
2018-10-26T21:32:52.12679  26-OCT-18 09.32.52.126790000 PM +00:00  26-OCT-18 09.32.52.126790000 PM

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.