what is the right way to convert timestamp to date in oracle?

Question

I have a timestamp column data looks like 2023-07-03 11:52:37 UTC wanted to convert it to date format like this 2023-07-03 11:52:37 without 'UTC' .

I tried using to_char it works and same if i convert it to to_date again it's not working as expected below is the query i used TO_DATE(TO_CHAR( from_tz(CAST(column AS timestamp), 'America/Los_Angeles') AT time zone 'UTC', 'YYYY-MM-DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS') AS column,

Answer 1

If you have a column with the TIMESTAMP WITH TIME ZONE data-type and you want to convert it to a TIMESTAMP (without time zone) data-type that is in the equivalent UTC time zone then use AT TIME ZONE to change to the UTC time zone and then CAST to the desired data-type:

SELECT CAST(
         timestamp_with_timezone_column AT TIME ZONE 'UTC'
         AS TIMESTAMP
       )
FROM   table_name

If you do not need to change the time zone then just use CAST:

SELECT CAST(timestamp_with_timezone_column AS TIMESTAMP)
FROM   table_name

If you want to convert it to a DATE (which has a time component but no fractional seconds then use CAST(... AS DATE).

---

If you have the sample data:

CREATE TABLE table_name (
  timestamp_with_timezone_column TIMESTAMP WITH TIME ZONE
);

INSERT INTO table_name (timestamp_with_timezone_column)
VALUES (TIMESTAMP '2023-07-03 11:52:37 UTC');

INSERT INTO table_name (timestamp_with_timezone_column)
VALUES (TIMESTAMP '2023-07-03 11:52:37 America/Los_Angeles');

Then:

SELECT CAST(
         timestamp_with_timezone_column AT TIME ZONE 'UTC'
         AS TIMESTAMP
       ) AS ts_utc,
       CAST(timestamp_with_timezone_column AS TIMESTAMP) AS ts,
       CAST(
         timestamp_with_timezone_column AT TIME ZONE 'UTC'
         AS DATE
       ) AS dt_utc,
       CAST(timestamp_with_timezone_column AS DATE) AS dt
FROM   table_name;

Outputs:

TS_UTC	TS	DT_UTC	DT
2023-07-03 11:52:37.000000	2023-07-03 11:52:37.000000	2023-07-03 11:52:37	2023-07-03 11:52:37
2023-07-03 18:52:37.000000	2023-07-03 11:52:37.000000	2023-07-03 18:52:37	2023-07-03 11:52:37

I tried using to_char it works and same if I convert it to to_date again it's not working as expected

That is a display problem and not a data problem. If you want the output as a DATE then the client application (i.e. SQL*Plus, SQL Developer, TOAD, Java, etc.) will convert the DATE to something it thinks is meaningful to the user by applying some formatting to it when it displays it. If the default formatting applied by the client application is not what you desire then change the default format model that the client applies (how to do that is different for each client).

For SQL*Plus and SQL Developer you can use:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR';

To change the default formats for dates, timestamps and timestamp with time zones, respectively.

If you want to explicitly display the DATE in a specific format (without changing the default for the client application) then use TO_CHAR to convert it to a string and apply the specific format that you want (and in that case, you do not need to use CAST, just use TO_CHAR).

SELECT TO_CHAR(
         timestamp_with_timezone_column AT TIME ZONE 'UTC',
         'YYYY-MM-DD HH24:MI:SS'
       ) AS str_utc,
       TO_CHAR(
         timestamp_with_timezone_column,
         'YYYY-MM-DD HH24:MI:SS'
       ) AS str
FROM   table_name;

Which outputs:

STR_UTC	STR
2023-07-03 11:52:37	2023-07-03 11:52:37
2023-07-03 18:52:37	2023-07-03 11:52:37

fiddle