While trying to convert datetime to epoch, I am getting an error: ORA-01810: format code appears twice

QracleSQL query:

select (trunc(TO_TIMESTAMP('2022-05-08T19:09:17Z', 'yyyy-MM-dd"T"HH:mm:ssXXX')) - TO_DATE('01/01/1970', 'MM/DD/YYYY')) * 24 * 60 * 60 from dual;

CodePudding user response：

You should use:

• TO_TIMESTAMP_TZ instead of TO_TIMESTAMP
• the format model YYYY-MM-DD"T"HH24:MI:SS.FF TZD rather than incorrectly using MM twice, HH24 instead of HH, .FF instead of XXX, and TZD instead of hardcoding "Z".
• Make sure you always convert your timestamp to UTC time zone (yours is already but others may not be)
• Don't TRUNCate the timestamp to a DATE at midnight or you will lose the time component.

Like this:

SELECT ROUND(
         (
           TRUNC(timestamp_value AT TIME ZONE 'UTC', 'MI')
           - DATE '1970-01-01'
         ) * 86400
           EXTRACT(SECOND FROM timestamp_value AT TIME ZONE 'UTC')
       ) AS epoch_time
FROM   (
  SELECT TO_TIMESTAMP_TZ(
           '2022-05-08T19:09:17Z',
           'YYYY-MM-DD"T"HH24:MI:SS.FF TZD'
         ) AS timestamp_value
  FROM   DUAL
);

Which outputs:

EPOCH_TIME
1652033357

db<>fiddle here

CodePudding user response：

Format mask is incorrect.

SQL> select trunc(cast (to_timestamp('2022-05-08T19:09:17Z', 'yyyy-MM-dd"T"HH24:mi:ss"Z"') as date)
  2        - to_date('01/01/1970', 'mm/dd/yyyy')) *24 *60 *60 res
  3  from dual;

       RES
----------
1651968000

SQL>

CodePudding user response：

Something like this:

TEST DATA

create table sample_inputs (ts_string) as
  select '2022-05-08T16:49:34Z'     from dual union all
  select '2022-04-15T04:20:13.525Z' from dual
;

QUERY AND OUTPUT

with
  prep (ts_string, ts) as (
    select ts_string,
           to_timestamp(ts_string, 'yyyy-mm-dd"T"hh24:mi:ss.ff"Z"')
    from   sample_inputs
  )
select ts_string,
       round((trunc(ts, 'mi') - date '1970-01-01') * 24 * 3600)
           extract(second from ts)
       as epoch
from   prep;

TS_STRING                        EPOCH
-------------------------- -----------
2022-05-08T16:49:34Z        1652028574
2022-04-15T04:20:13.525Z    1649996413.525

NOTES

In your attempt there are several mistakes. The Oracle fractional-seconds element is ff, not xxx. You are missing the placeholder for the hard-coded Z at the end (you have "T" in your mask, which is correct, but you are missing the similar "ZZ"). HH is insufficient - it must be either HH24 or HH followed by AM (or equivalently PM) at the end. In your example, it is obviously HH24. And MM and mm mean the same thing in Oracle - this is not Unix. The element for minutes is mi or equivalently MI.

The query I wrote preserves fractional seconds in the epoch. Another question earlier today (perhaps yours too, under another user name) was closed as being a "duplicate" - but the claimed "duplicate" has absolutely nothing about preserving fractional seconds, when the input is an Oracle timestamp vs an Oracle date (which always does have a time component, but only in whole seconds).