SQL Language (MOSC)

MOSC Banner

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

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center