Thread: Setting Time Zone in oracle database

I want to change the Time stamp of database? It is possible or not? The oracle shows me this GMT -04:00 time zone. After the implementation of Time zone statement it gives me like:

SQL> select systimestamp from test;

SYSTIMESTAMP
---------------------------------------------------------------------------
12-Jan-09 12.58.29.906000 PM -04:00

How many time zone are being used in prediction database.

The core of time zones is recognized by the database time zones. You can view the chosen time zone using following statement:

SQL> select DBTIMEZONE from test;
DBTIME
------
-07:00

If you should to change the database time zone, you can use ALTER statement to alter the time zone value using recommended command :

SQL> ALTER database SET TIME_ZONE = 'India’;
Database altered.

TIMESTAMP data type is Oracle's great new hope which does not keep only time information but also fractional seconds is viewed by this data type.
The following code explains you the shared functionality of TIMESTAMP with TIMEZONE.

Code:

SQL> select dbtimezone,sessiontimezone from dual;
DBTIMEZONE SESSIONTIMEZONE
---------- ----------------
-07:00     -07:00
SQL> insert into date_table values (SYSTIMESTAMP,SYSTIMESTAMP);
1 row created.
SQL> alter session set time_zone='-12:00';
Session altered. 
SQL> select dbtimezone,sessiontimezone from dual;
DBTIMEZONE SESSIONTIMEZONE
---------- ----------------
-12:00     -04:00

SQL> insert into date_table values (LOCALTIMESTAMP,LOCALTIMESTAMP );
1 row created.
SQL> select time_stamp_tz, time_stamp_ltz from date_table;
TIME_STAMP_TZ                         TIME_STAMP_LTZ
------------------------------------- ------------------------------
12-Jan-09 12.58.29.906000 PM -04:00 12-Jan-09 12.58.29.906000 PM 
12-Jan-09 12.58.29.906000 PM -04:00 12-Jan-09 12.58.29.906000 PM 
SQL> alter session set time_zone='-04:00';
Session altered.
SQL> select time_stamp_tz, time_stamp_ltz from date_table;
TIME_STAMP_TZ                         TIME_STAMP_LTZ
------------------------------------- ------------------------------
12-Jan-09 12.58.29.906000 PM -04:00  12-Jan-09 
10.57.36.642000 AM
12-Jan-09 12.58.29.906000 PM -04:00   12-Jan-09 
10.57.48.549000 AM