Oracle Database Dates Time Zones and Daylight Savings Time
Example
The DATE data type does not handle time zones or changes in daylight savings time.
Either:
- use the
TIMESTAMP WITH TIME ZONEdata type; or - handle the changes in your application logic.
A DATE can be stored as Coordinated Universal Time (UTC) and converted to the current session time zone like this:
SELECT FROM_TZ(
CAST(
TO_DATE( '2016-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AS TIMESTAMP
),
'UTC'
)
AT LOCAL AS time
FROM DUAL;
If you run ALTER SESSION SET TIME_ZONE = '+01:00'; then the output is:
TIME
------------------------------------
2016-01-01 13:00:00.000000000 +01:00
and ALTER SESSION SET TIME_ZONE = 'PST'; then the output is:
TIME
------------------------------------
2016-01-01 04:00:00.000000000 PST
