CODEWITHSUNDEEP
oracle-databasedatetimezoneoracle12c
Chanuka
Chanuka

Reputation: 23

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

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.

Upvotes: 2

Views: 2302

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59622

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

Upvotes: 2

Related Questions