Tag Archives: localtimestamp

Understand and Set Time Zone datatime datatype in Oracle

Understand and Set Time Zone datatime datatype in Oracle

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

DATE:
It store 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';

TIMESTAMP:
It store additional fraction seconds plus date datatype format as value. By default it use NLS_TIMESTAMP_FORMAT and NLS_DATE_LANGUAGE parameters.
Note: If you insert data with timezone in this TIMESTAMP datatype then it will skip 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:
It includes a time zone region name or time zone offset in its value (it is difference in hours & mins between local or universal time(GMT). 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 stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data.
When users retrieve the data, Oracle Database returns it in the users’ local session time zone. By 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
Returns the value of the database time zone.

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

Check the current_Date
Returns the current date in the session time zone as date datatype.

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

Check current_timestamp
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
Returns the current date and time in the session time zone as timestamp datatype

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

Advertisements