Different result from query TO_TIMESTAMP AT TIME ZONE ’UTC’ between Oracle 11g and Oracle 19c
In our system, we received some date without a timezone, so we convert those date to timestamp with UTC timezone using TO_TIMESTAMP(date_format) AT TIME ZONE ‘UTC’
Here some example if we received the date ‘2020-01-01 00:00:00’
So we convert using to_timestamp with this query :
select SESSIONTIMEZONE, DBTIMEZONE,
TO_TIMESTAMP ('2020-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'UTC' as PARAM1 FROM DUAL;
We add SESSIONTIMEZONE and DBTIMEZONE just for additional information.
Result in Oracle 11g (11.2.0.1.0):
SESSIONTIMEZONE Asia/Bangkok
DBTIMEZONE +00:00
PARAM1 01-JAN-20 12.00.00.000000000 AM UTC
Result in Oracle 19c (19.0.0.0.0):
SESSIONTIMEZONE Asia/Bangkok
DBTIMEZONE +00:00
PARAM1 31-DEC-19 05.00.00.000000000 PM UTC