CONVERT_TZ() equivalent in Oracle

I am trying to convert a timestamp value of the format YYYY-MM-DDTHH24:MI:SS to another timezone value YYYY-MM-DD HH24:MI:SS.SSSSSS. I was able to do this in MySql using the CONVERT_TZ(date, from_tz, to_tz) function,

timestamp (varchar): `2018-06-08T23:00:00`
offset (varchar): `+00:00`

convert_tz(timestamp, offset, '+00:00') = 2018-06-08 23:00:00 works fine

How can I do the same thing in Oracle?

I want to pass these 3 values (timestamp, offset, ‘+00:00’) and tried using from_tz() in Oracle but was able to just pass two values (timestamp, offset), how do I pass the third value (‘+00:00’)?

Here’s my query:

INSERT INTO CLIENTTABLE(id, timestamp, orgId) SELECT NULL, from_tz(to_timestamp('2018-06-08T23:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') ,'+00:00'), '1010' FROM IMPORTDATA;

This gives: 08-JUN-18 11.00.00.000000000 PM +00:00

Desired output: 2018-06-08 23:00:00.000000

Any help is appreciated. Thanks!

Answer

You can convert the string and offset separately; to_timestamp() gives you a plain timestamp (no time zone) and you can declare it to be from a particular one, which is basically what you are doing already. The result of that is a timestamp with time zone. You then use at time zone to convert to a different one. And finally you can convert that back to a string:

I’ll use a different offset to make it a bit clearer:

select to_timestamp('2018-06-08T23:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as ts,
  from_tz(
    to_timestamp('2018-06-08T23:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'),
    '+05:30'
  ) as tstz_local,
  from_tz(
    to_timestamp('2018-06-08T23:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'),
    '+05:30'
  ) at time zone 'UTC' as tstz_utc,
  to_char(
    from_tz(
      to_timestamp('2018-06-08T23:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'),
      '+05:30'
    ) at time zone 'UTC',
    'YYYY-MM-DD HH24:MI:SS.FF6'
  ) as str_utc
from dual;

You don’t need those separate results of course, I’m just showing the working.

As you want to end up with UTC you can use a built-in function to slightly simplify it:

select to_char(
  sys_extract_utc(
    from_tz(
      to_timestamp('2018-06-08T23:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'),
      '+05:30'
    )
  ), 'YYYY-MM-DD HH24:MI:SS.FF6') as str_utc
  as tstz_utc,
from dual;


STR_UTC
--------------------------
2018-06-08 17:30:00.000000

db<>fiddle

Alternatively you could concatenate the two strings together and convert them straight into a timestamp with time zone:

select to_char(
  sys_extract_utc(
    to_timestamp_tz('2018-06-08T23:00:00' || '+05:30',
      'YYYY-MM-DD"T"HH24:MI:SS TZH:TZM')
  ), 'YYYY-MM-DD HH24:MI:SS.FF6') as str_utc
from dual;

db<>fiddle

A date or timestamp (with or without a time zone) doesn’t have any real recognisable format; there are several internal representations Oracle uses depending on the context. If you don’t explicitly convert to a string with to_char() then your client will decide what format to use, via its own settings or more usually via the session NLS parameters, NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, etc.

You should only do that for display though. When storing data in a table, use the correct native data type for the column – in this case, a timestamp. Don’t store it as a string. Assuming your target table column is actually a timestamp, and the source staging table has string, you would then do something like:

INSERT INTO CLIENTTABLE(id, timestamp, orgId)
SELECT
  NULL,
  sys_extract_utc(
    from_tz(
      to_timestamp(timestamp, 'YYYY-MM-DD"T"HH24:MI:SS'),
      offset
    )
  ),
  '1010'
FROM IMPORTDATA;

Having columns with the same name as data types is confusing, as is having the same name in the source and target table.