Working with TIME ZONE information

To view the time zone settings for the database and your session:

SELECT DBTIMEZONE, SESSIONTIMEZONE
FROM DUAL;

DBTIMEZONE	SESSIONTIMEZONE
+01:00	Europe/Zurich

Some other useful functions:

SYSTIMESTAMP --> Returns system date including fractional seconds, datatype TIMESTAMP WITH TIME ZONE.

LOCALTIMESTAMP --> Returns the user session's local timestamp, datatype TIMESTAMP

CURRENT_TIMESTAMP --> Returns datatype TIMESTAMP WITH TIME ZONE, in the local time zone set for the session

CURRENT_DATE --> Returns datatype DATE, in the local time zone set for the session

SELECT  SYSTIMESTAMP,
        LOCALTIMESTAMP,
        CURRENT_TIMESTAMP,
        CURRENT_DATE
FROM DUAL;

SYSTIMESTAMP	LOCALTIMESTAMP	CURRENT_TIMESTAMP	CURRENT_DATE
10.02.2014 09:17:00.301998 +01:00	10.02.2014 09:17:00.302006	10.02.2014 09:17:00.302006 +01:00	10.02.2014 09:17:00

Some conversion functions:

FROM_TZ

-- converts a TIMESTAMP to TIMESTAMP WITH TIMEZONE

SELECT LOCALTIMESTAMP, FROM_TZ(LOCALTIMESTAMP, '+01:00' ) FROM DUAL

LOCALTIMESTAMP                 FROM_TZ(LOCALTIMESTAMP,'+01:00')
------------------------------ ---------------------------------------------------------------------------
18.03.14 09:23:40.032126       18.03.14 09:23:40.032126 +01:00

-- or alternatively, look up the timezone used in your session
-- and use this in a subquery 
SELECT LOCALTIMESTAMP, 
       FROM_TZ(LOCALTIMESTAMP, SELECT SESSIONTIMEZONE FROM DUAL) ) "Converted to TSTZ"
FROM DUAL;

LOCALTIMESTAMP                 Converted to TSTZ
------------------------------ ----------------------------------------
18.03.14 09:28:39.470322       18.03.14 09:28:39.470322 +01:00

TO_TIMESTAMP_TZ

--converts a string to a TIMESTAMP WITH TIMEZONE datatype
SELECT TO_TIMESTAMP_TZ('03.02.1973 16:00:00', 'DD.MM.YYYY HH24:MI:SS') 
FROM DUAL;

TO_TIMESTAMP_TZ('03.02.197316:00:00','DD.MM.YYYYHH24:MI:SS')
---------------------------------------------------------------------------
03.02.73 16:00:00.000000000 +01:00

TO_TIMESTAMP

--converts a string to a TIMESTAMP datatype
SELECT TO_TIMESTAMP('03.02.1973 16:00:00:123456','DD.MM.YYYY HH24:MI:SS:FF') 
FROM DUAL;

TO_TIMESTAMP('03.02.197316:00:00:123456','DD.MM.YYYYHH24:MI:SS:FF')
---------------------------------------------------------------------------
03.02.73 16:00:00.123456000

CAST
Cast can be used for a variety of conversions. It takes an expression as its first parameter, and returns an instance of the datatype you are converting to.
It is useful to convert strings representing dates or times, into proper datetime formats.

-- to convert a string into a TIMESTAMP WITH LOCAL TIME ZONE, use CAST
SELECT CAST('03-FEB-11'
       AS TIMESTAMP WITH LOCAL TIME ZONE) "String to TWLTZ"
FROM DUAL;

String to TWLTZ
----------------------------
03.02.11 00:00:00.000000

For a string with more precision, use TO_TIMESTAMP to convert to a timestamp first:

SELECT CAST(
            TO_TIMESTAMP('03.02.1973 16:05:00','DD.MM.YYYY HH24:MI:SS')
            AS TIMESTAMP WITH LOCAL TIME ZONE
           ) "TS to TSWLTZ"
FROM DUAL;

TS to TSWLTZ
-----------------------------
03.02.73 16:05:00.000000

EXTRACT
Extracts the value indicated by the constant immediately following the opening parenthesis, from a valid timestamp expression.
Note that EXTRACT requires a timestamp expression as an argument.
Its return type is character if you extract TIMEZONE_REGION or TIMEZONE_ABBR, but numeric for all other.

SELECT  EXTRACT(MINUTE FROM LOCALTIMESTAMP),
        EXTRACT(YEAR   FROM LOCALTIMESTAMP),
        EXTRACT(MONTH  FROM LOCALTIMESTAMP),
        EXTRACT(DAY    FROM LOCALTIMESTAMP),
        EXTRACT(HOUR   FROM LOCALTIMESTAMP),
        EXTRACT(SECOND FROM LOCALTIMESTAMP)
FROM DUAL;

EXTRACT(MINUTEFROMLOCALTIMESTAMP)	EXTRACT(YEARFROMLOCALTIMESTAMP)	EXTRACT(MONTHFROMLOCALTIMESTAMP)	EXTRACT(DAYFROMLOCALTIMESTAMP)	EXTRACT(HOURFROMLOCALTIMESTAMP)	EXTRACT(SECONDFROMLOCALTIMESTAMP)
3	2014	2	10	10	24.256402

Using the SYSTIMESTAMP, which includes TIME ZONE, in the EXTRACT function:

SELECT  EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP)   "TZ_HOUR",
        EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP) "TZ_MINUTE"
FROM DUAL;

TZ_HOUR	TZ_MINUTE
1	0

SYS_EXTRACT_UTC
Extract the universal time (UTC) based on a timestamp that contains time zone information:

SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP)
FROM DUAL;

SYS_EXTRACT_UTC(SYSTIMESTAMP)
10.02.2014 09:49:37.390230

AT TIME
Converts a datetime value to another time zone. AT TIME can convert TIMESTAMP, TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE.
But, take care, NOT the DATE datatype!
Note that the parenthesis are not required, but it makes the function more readable:

SELECT (LOCALTIMESTAMP) AT TIME ZONE DBTIMEZONE "DB time"
FROM DUAL;

DB time
10.02.2014 10:59:45.901974 +01:00

Or, using a named time zone region:

SELECT (CURRENT_TIMESTAMP) AT TIME ZONE 'Europe/Moscow' "Moscow Time"
FROM DUAL;

Moscow Time
10.02.2014 13:14:06.632136 +03:00

Specifying an offset from UTC:

SELECT (CURRENT_TIMESTAMP) AT TIME ZONE '-5:00' "New York Time"
FROM DUAL;

New York Time
10.02.2014 05:15:51.388302 -05:00

Use a TIMESTAMP value instead of TIMESTAMP WITH TIME ZONE:

SELECT (LOCALTIMESTAMP) AT TIME ZONE 'America/Los_Angeles' "LA Time"
FROM DUAL;

LA Time
10.02.2014 02:19:39.958949 -08:00

AT LOCAL
Convert the source data into the local time equivalent:

SELECT 
        FROM_TZ(
                TO_TIMESTAMP('10.02.2014 02:21:06.938886', 'DD.MM.YYYY HH24:MI:SS:FF')
               ,'US/Hawaii') 
        AT LOCAL "Hawaiian to Local Time"
FROM DUAL;

Hawaiian to Local Time
10.02.2014 13:21:06.938886000 +01:00

Without the AT LOCAL function, you would of course get the timestamp in the time zone specified

SELECT  FROM_TZ(
                TO_TIMESTAMP('10.02.2014 02:21:06.938886', 'DD.MM.YYYY HH24:MI:SS:FF')
               ,'US/Hawaii') "Hawaiian time"
FROM DUAL;

Hawaiian time
10.02.2014 02:21:06.938886000 -10:00