CODEWITHSUNDEEP
sqloracle-database
DSH
DSH

Reputation: 1139

Show UTC in timestamp Oracle SQL

In Oracle SQL, this:

SELECT to_timestamp('2021-08-11 16:25:54', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

Returns: 11-AUG-21 04.25.54.000000000 PM

However, my input timestamp also contains UTC: 2021-08-11 16:25:54 UTC

Desired result: 11-AUG-21 04.25.54.000000000 PM UTC

The docs specify to include Z... However this does not work:

--Invalid date format
SELECT to_timestamp('2021-08-11 16:25:54 UTC', 'YYYY-MM-DD HH24:MI:SS Z') FROM DUAL;

Upvotes: 1

Views: 347

Answers (2)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

First of all, you need to_timestamp_tz() instead of to_timestamp, and secondly you need to specify correct format: TZR instead of Z:

-- TZR = time zone region
SELECT to_timestamp_tz('2021-08-11 16:25:54 UTC', 'YYYY-MM-DD HH24:MI:SS TZR') FROM DUAL;

Upvotes: 1

Serg
Serg

Reputation: 22811

You can use at time zone

SELECT to_timestamp('2021-08-11 16:25:54', 'YYYY-MM-DD HH24:MI:SS') at time zone 'UTC' dt
FROM DUAL;

EDIT

Correct version, thanks to @WernfriedDomscheit

SELECT FROM_TZ(to_timestamp('2021-08-11 16:25:54', 'YYYY-MM-DD HH24:MI:SS'), 'UTC') ts
FROM DUAL;

Upvotes: 1

Related Questions