Datetime Expressions are useful for converting datetimes from one time zone to another. With them we can take values like these
select c_timestamp_with_time_zone as test_time_value from t ;
TEST_TIME_VALUE --------------------------------------------------------------------------- -2400-10-31 12:34:56 +03:00 -0500-02-28 00:00:00 +05:30 -0001-12-31 12:39:00 +01:39 0000-01-01 12:00:00 +00:00 0001-06-21 22:00:00 -12:00 0820-11-23 05:48:58 +03:25 1947-12-16 01:10:11 -05:00 2007-06-15 14:30:00 -07:00
and convert them to either a session's local time zone, a database's time zone, or any arbitrary time zone we wish to specify.
Converting to the Session's Time Zone
To see what time zone a session is currently operating in perform a query like the following. (Your time zone may differ from the one shown. If so, you will receive different results for some of the examples below.)
select SESSIONTIMEZONE from dual ;
SESSIONTIMEZONE --------------------------------------------------------------------------- -04:00
The following expressions convert the test time values to the session's time zone.
select c_timestamp_with_time_zone AT LOCAL as session_time_zone_time from t ;
SESSION_TIME_ZONE_TIME --------------------------------------------------------------------------- -2400-10-31 05:34:56 -04:00 -0500-02-27 14:30:00 -04:00 -0001-12-31 07:00:00 -04:00 0000-01-01 08:00:00 -04:00 0001-06-22 06:00:00 -04:00 0820-11-22 22:23:58 -04:00 1947-12-16 02:10:11 -04:00 2007-06-15 17:30:00 -04:00
select c_timestamp_with_time_zone AT TIME ZONE SESSIONTIMEZONE as session_time_zone_time from t ;
SESSION_TIME_ZONE_TIME --------------------------------------------------------------------------- -2400-10-31 05:34:56 -04:00 -0500-02-27 14:30:00 -04:00 -0001-12-31 07:00:00 -04:00 0000-01-01 08:00:00 -04:00 0001-06-22 06:00:00 -04:00 0820-11-22 22:23:58 -04:00 1947-12-16 02:10:11 -04:00 2007-06-15 17:30:00 -04:00
Converting to the Database's Time Zone
To see what time zone the database is currently operating in perform a query like the following. (Your database's time zone may differ from the one shown. If so, you will receive different results for some of the examples below.)
select DBTIMEZONE from dual ;
DBTIME ------ +00:00
The following expressions convert the test time values to the database's time zone.
select c_timestamp_with_time_zone AT TIME ZONE DBTIMEZONE as db_time_zone_time from t ;
DB_TIME_ZONE_TIME --------------------------------------------------------------------------- -2400-10-31 09:34:56 +00:00 -0500-02-27 18:30:00 +00:00 -0001-12-31 11:00:00 +00:00 0000-01-01 12:00:00 +00:00 0001-06-22 10:00:00 +00:00 0820-11-23 02:23:58 +00:00 1947-12-16 06:10:11 +00:00 2007-06-15 21:30:00 +00:00
Converting to a Specified Time Zone
To specify a specific time zone which may not necessarily match either the session or the database time zone use one of the following expressions.
select c_timestamp_with_time_zone AT TIME ZONE '+01:00' as utc_plus_1_time from t ;
UTC_PLUS_1_TIME --------------------------------------------------------------------------- -2400-10-31 10:34:56 +01:00 -0500-02-27 19:30:00 +01:00 -0001-12-31 12:00:00 +01:00 0000-01-01 13:00:00 +01:00 0001-06-22 11:00:00 +01:00 0820-11-23 03:23:58 +01:00 1947-12-16 07:10:11 +01:00 2007-06-15 22:30:00 +01:00
select c_timestamp_with_time_zone AT TIME ZONE '-03:30' as utc_minus_3_30_time from t ;
UTC_MINUS_3_30_TIME --------------------------------------------------------------------------- -2400-10-31 06:04:56 -03:30 -0500-02-27 15:00:00 -03:30 -0001-12-31 07:30:00 -03:30 0000-01-01 08:30:00 -03:30 0001-06-22 06:30:00 -03:30 0820-11-22 22:53:58 -03:30 1947-12-16 02:40:11 -03:30 2007-06-15 18:00:00 -03:30
select c_timestamp_with_time_zone AT TIME ZONE 'Australia/Sydney' as sydney_time from t ;
SYDNEY_TIME --------------------------------------------------------------------------- -2400-10-31 19:30:56 +10:04 -0500-02-28 04:26:00 +10:04 -0001-12-31 20:56:00 +10:04 0000-01-01 21:56:00 +10:04 0001-06-22 19:56:00 +10:04 0820-11-23 12:19:58 +10:04 1947-12-16 16:10:11 +10:00 2007-06-16 07:30:00 +10:00
(Allowable timezone names, like "Australia/Sydney", for your system can be found by querying V$TIMEZONE_NAMES. See Oracle® Database Globalization Support Guide - Choosing a Time Zone File for more details. )
Converting to Coordinated Universal Time (UTC)
To convert a datetime to Coordinated Universal Time (UTC) use one of the following expressions.
select c_timestamp_with_time_zone AT TIME ZONE 'UTC' as utc_time from t ;
UTC_TIME --------------------------------------------------------------------------- -2400-11-01 13:34:56 +00:00 -0500-02-28 22:30:00 +00:00 0000-01-01 15:00:00 +00:00 0000-01-02 16:00:00 +00:00 0001-06-23 14:00:00 +00:00 0820-11-24 06:23:58 +00:00 1947-12-16 06:10:11 +00:00 2007-06-15 21:30:00 +00:00
select SYS_EXTRACT_UTC( c_timestamp_with_time_zone ) as utc_time from t ;
UTC_TIME --------------------------------------------------------------------------- -2400-10-31 09:34:56 -0500-02-27 18:30:00 -0001-12-31 11:00:00 0000-01-01 12:00:00 0001-06-22 10:00:00 0820-11-23 02:23:58 1947-12-16 06:10:11 2007-06-15 21:30:00
Note how the last version returns a TIMESTAMP value, not a TIMESTAMP WITH TIME ZONE value like the preceding examples.
NEW_TIME
The function NEW_TIME can be used to convert DATE values from one time zone to another, but it only supports GMT and North American time zones.
NEW_TIME was introduced in Oracle 8i, before the more robust Datetime Expressions described above, which were introduced in Oracle 9i.