CODEWITHSUNDEEP
databaseoracle-databaseiso8601
hades
hades

Reputation: 4696

Convert String ISO-8601 date to oracle's timestamp datatype

I have a ISO-8601 date in VARCHAR2 type, how can i convert that String date to timestamp in oracle db?

Date Example: "2014-09-12T11:53:06+00:00"

Maybe is something like the following but i not sure what is the format.

SELECT to_timestamp_tz ('2014-09-12T11:53:06+00:00', ????) FROM DUAL

Upvotes: 19

Views: 34136

Answers (2)

LeslieNope
LeslieNope

Reputation: 7

I had an app that was making queries against an OracleDB database (19c). The app was sending timestamp parameters in ISO 8601 format and the table had a column that was an Oracle TIMESTAMP type (not an Oracle TIMESTAMP WITH TIMEZONE type) that was just known to store timestamps that represented UTC time.

I thought the following query would work but it didn't seem to recognize the literals that were surrounded by double quotes:

SELECT * FROM measurements
WHERE measID = '333'
AND measTime > to_timestamp('2020-11-19T05:00:00Z', 'YYYY-MM-DD"T"hh:mm:ss"Z"');
-- Doesn't Work
-- ORA-01810: format code appears twice
-- 01810. 00000 -  "format code appears twice"

The following did work for me:

SELECT * FROM measurements
WHERE measID = '333'
AND measTime > SYS_EXTRACT_UTC(TO_UTC_TIMESTAMP_TZ('2020-11-19T05:00:00Z'));
-- Works

Sharing because formatting ISO 8601 to TIMESTAMP types wasn't immediately clear in Oracle documentation. I found this solution here: https://oracle-base.com/articles/18c/to_utc_timestamp_tz-function-18c

Upvotes: -1

Alex Poole
Alex Poole

Reputation: 191275

The date format model elements are listed in the Datetime Format Models documentation:

SELECT to_timestamp_tz ('2014-09-12T11:53:06+00:00', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')
FROM DUAL

TO_TIMESTAMP_TZ('2014-09-12T11:53:06+00:00','YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')
---------------------------------------------------------------------------
12-SEP-14 11.53.06.000000000 +00:00

The fixed T can be included as a character literal:

You can include these characters in a date format model:

  • Punctuation such as hyphens, slashes, commas, periods, and colons
  • Character literals, enclosed in double quotation marks

TZH is tome zone hour, and TZM is time zone minutes. The rest are more common model elements.

Upvotes: 27

Related Questions