Understand and set Time Zone datatime datatype in Oracle

DATETIME DATATYPE
This has different data type which will support different format.

DATE:
It stores date and time as value. Default date format can be defined from NLS_DATE_FORMAT and NLS_DATE_LANGUAGE parameters.

EXAMPLE
DATE 'YYYY-MM-DD' 

--Set the date format at session level
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Alter the TIMESTAMP in Oracle

It store additional fraction seconds plus date datatype format as value. By default it use NLS_TIMESTAMP_FORMAT and NLS_DATE_LANGUAGE parameters.

Note: Inserting data with a timezone in the TIMESTAMP datatype will ignore the timezone.

Example
TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

--Set the timestamp format
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';

TIMESTAMP WITH TIME ZONE:
This includes a time zone name or offset from GMT, indicating the difference in hours and minutes. It uses the default NLS_TIMESTAMP_TZ_FORMAT parameter.

Example:
TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
TIMESTAMP '1999-01-15 8:00:00 America/Los_Angeles'
--For day light saving PDT used
TIMESTAMP '1999-10-29 01:30:00 America/Los_Angeles PDT'

--set the timestamp format.
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';
ALTER SESSION SET TIME_ZONE='-7:00';

TIMESTAMP WITH LOCAL TIME ZONE
It is stored in the database in the database’s time zone, and the time zone offset is not included in the column data.

When users access data, Oracle Database gives it back in their local session time zone, using the default NLS_TIMESTAMP_FORMAT parameter.

Example of using Local Time Zone:
It will change the time according to your local time while you inserting into the database:
--set the timestamp format.
ALTER SESSION SET TIME_ZONE='-7:00';

CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);

INSERT INTO table_tsltz VALUES(1, '01-JAN-2003 2:00:00');
INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');

SELECT * FROM table_tsltz;

C_ID C_TSLTZ
---------- ------------------------------------
1 01-JAN-03 02.00.00.000000 AM
2 01-JAN-03 02.00.00.000000 AM
3 01-JAN-03 03.00.00.000000 AM

Check all timezone present in Oracle

SELECT TZNAME, TZABBREV FROM V$TIMEZONE_NAMES ORDER BY TZNAME, TZABBREV;

Check Database Time Zone in Oracle

This returns the database’s time zone value.

SQL> select dbtimezone from dual
DBTIME
------
+00:00

Check Current Date
This returns the current date in the session time zone as a date datatype.

Select current_Date from dual;
CURRENT_D
---------
02-NOV-18

Check current_timestamp of Oracle Database

Returns the current date and time in the session time zone as a TIMESTAMP WITH TIME ZONE datatype.

select current_timestamp from dual;
CURRENT_TIMESTAMP
-------------------------------------
02-NOV-18 10.56.04.000000 AM +05:30

Check the localtimestamp of Oracle Database

Returns the current date and time in the session’s time zone as a timestamp.

 select localtimestamp from dual;
 LOCALTIMESTAMP
 ----------------------
 02-NOV-18 10.56.04.000000 AM