How does Oracle 12c handle Time Zone data in DATE datatype

user_8473244 0
Viewed 426

I'm using Oracle Database 12c. I want to know how oracle manages timezone details for DATE datatype.

  1. If i were to migrate data between time zones can i still get away with using DATE datatype? Or do i have to use TimeStamp or TimeStamp with TimeZone?
  2. Does it keep the UTC time in DB and convert it when querying for results according to session time zone or some other NLS setting?

Thank you.

oracle12c timezone date oracle
1 Answers

No, data type DATE does not handle any time zone information. Use data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH TIME ZONE holds date + time + time zone

TIMESTAMP WITH LOCAL TIME ZONE holds date + time and these are stored internally in DBTIMEZONE (typically UTC). The value is always shown in the current user session time zone SESSIONTIMEZONE.

If you like to convert a DATE to TIMESTAMP WITH {LOCAL} TIME ZONE, then you must tell Oracle which time zone shall be used.

Typically you do it like this:

FROM_TZ(CAST({date_value} AS TIMESTAMP), 'desired time zone')

The cast is required because FROM_TZ requires a TIMESTAMP rather than a DATE

If you don't specify the time zone, e.g. like CAST({date_value} AS TIMESTAMP WITH TIME ZONE) then Oracle defaults the time zone to SESSIONTIMEZONE

user_30272660
Related