Oracle time zone problems

Oracle 9i has three Data Types About Time: Timestamp [(precision)] timestamp [(precision)] with Time Zone timestamp [(precision)] with local time zone, timestamp [(precision)] with Time Zone stores time zone information.

1. Oracle time zone settings

The Time Zone of Oracle can be divided into two types: the database time zone and the session time zone, that is, the time zone when the client is connected (after experiment, the client's time zone is modified after connection, the Time Zone of the session will not be changed ).

The Time Zone of the database can be specified by adding set time_zone = '{{+ |-} hh: Mi | time_zone_region}' to the create database statement. If this parameter is not specified, by default, the time zone of the operating system where the database is located is set. After creation, you can modify it by using alter database. The time_zone_region parameter can be used to query the V $ timezone_names dynamic view to obtain all supported values. After modification, You need to restart the database to take effect. Some people often encounter unmodifiable situations:

SQL> alter database set time_zone = '+ 06:00 ';
Alter database set time_zone = '+ 06:00'
*
Error at line 1:
ORA-02231: Missing or invalid option to alter Database

Tom has explained this problem. The time_zone is mainly set to with local time zone. When the time zone of the session is different from the time zone of the database, Oracle converts the time to the database based on the time zone gap, save it to the with local time zone type of the database. It does not save the time zone, so time_zone is required for time conversion between different time zones (with Time Zone saves the original time zone, therefore, you can convert time zones without setting time_zone ). However, once this type is available in the database, you cannot modify the time zone using alter database. The preceding error is returned. You can use the following statement to obtain all tables containing this type, delete them and modify them.

Select U. Name | '.' | O. Name | '.' | C. Name tsltzcolumn
From SYS. OBJ $ o, SYS. Col $ C, SYS. User $ u
Where C. Type #= 231
And O. OBJ # = C. OBJ #
And U. User # = O. Owner #;

(The query result is Oe. orders. order_date indicates that the order_date field of the orders table under the OE user uses the time zone information: with local time zone. After this information is removed, you can modify it, after modification, the database needs to be restarted to take effect)

The Time Zone of the session is determined by the time zone of the client. Of course, you can change the time zone of the session after the connection. The with local time zone type automatically converts the time to the time zone in which the session is located according to the time_zone setting. Because the time zone saves the time zone, you do not need to follow the time_zone .

2. view the time zone

You can use the sessiontimezone/dbtimezone built-in functions to view the session and database time zone respectively:

Sys @ skydb> select dbtimezone from dual;

Dbtime
------
+ 08:00

Sys @ skydb> select sessiontimezone from dual;

Sessiontimezone
---------------------------------------------
+ 09: 00

In addition, you can use tz_offset to query the difference between a time zone and UTC.

Tz_offset ({'time _ zone_name'
| '{+ |-} Hh: Mi'
| Sessiontimezone
| Dbtmezone}
)

Select tz_offset ('us/Eastern ') from dual;

Tz_offs
-------
-

Select tz_offset (dbtimezone) from dual;

Tz_offset (dbti
--------------
+ 08:00

Here, time_zone_name can also be obtained from V $ timezone_names.

3. Comparison of Several built-in Time Functions

Sysdate/iimestamp is the time when the database is returned and the time zone of the database is used. They return the operating system time. Sysdate returns the date type, with no time zone information. The time is returned on the operating system, and the systimestamp returns the timestamp with Time Zone class with the time zone information:

Sys @ skydb> select sysdate from dual;

Sysdate
-------------------
10:01:31

Sys @ skydb> select policimestamp from dual;

Systimestamp
-----------------------------------------------
03-aug-06 10.02.21.093000 AM + 08:00

Sys @ skydb> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.

Change the OS time zone to +

Sys @ skydb> startup
Oracle instance started.

Total system global area 89202456 bytes
Fixed size 454424 bytes
Variable Size 62914560 bytes
Database buffers 25165824 bytes
Redo buffers 667648 bytes
Database mounted.
Database opened.

Sys @ skydb> select sysdate from dual;

Sysdate
-------------------
04:03:37

Sys @ skydb> select policimestamp from dual;

Systimestamp
----------------------------------------------
03-aug-06 04.04.15.687000 AM + 0:00

Note: This is the result of an experiment on our machine. I have created multiple databases and do not know why I cannot connect to local data through IPC, when logging in, use sqlplus "/@ skydb as sysdba", that is, use a listener to connect, but do the same experiment at home and connect sqlplus "/As sysdba" Through IPC ", after the time zone is changed, sysdate still displays the time before the modification, but the systimestamp is correct. I do not know why:

SQL> select sysdate from dual;

Sysdate
-------------------
22:21:40

SQL> select distinct imestamp from dual;

Systimestamp
---------------------------------------------------------------------------
02-aug-06 10.22.38.578000 PM + 08:00

SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
Change the time zone to +
SQL> startup
Oracle instance started.

Total system global area 131145064
Bytes

Fixed size 453992
Bytes

Variable Size 109051904
Bytes

Database buffers 20971520
Bytes

Redo Buffers' 667648

Bytes

Database mounted.
Database opened.
SQL> select sysdate from dual;

Sysdate
---------
02-aug-06

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';

Session altered.

SQL> select sysdate from dual;

Sysdate
-------------------
22:32:59 <-or the previous time

SQL> select distinct imestamp from dual;

Systimestamp
---------------------------------------------------------------------------
02-aug-06 11.3520.5.171000 PM + <-the time is correct

In addition, there is an initialization parameter fixed_date, which can be set to return the specified time sysdate:

Alter system set fixed_date = '2017-04-04-11-00-00'

This fixed_date is normally used, in Oracle, for dubugging purpose.

Once finishing it, you can set it back:

Alter system set fixed_date = none

Eygle's article on this parameter: Why sysdate is fixed

Current_timestamp/current_date will also return the database time, but the time zone converted to session for display, you can use alter session set time_zone to change the session time zone.

4. Four Date and Time experiments

SQL> select dbtimezone from dual;

Dbtime
------
+ 06:00

SQL> select sessiontimezone from dual;

Sessiontimezone
---------------------------------------------------------------------------
+ 08:00

SQL> ed
Wrote file afiedt. Buf

1 create table tztest (a date,
2 B timestamp (0 ),
3 C timestamp (0) with time zone,
4 * D timestamp (0) with local time zone)
SQL>/

Table created.

SQL> alter session set nls_date_format = 'yyyy-dd-mm hh24: MI: ss ';

Session altered.

SQL> select sysdate from dual;

Sysdate
-------------------
22:21:40

SQL> select distinct imestamp from dual;

Systimestamp
---------------------------------------------------------------------------
02-aug-06 10.22.38.578000 PM + 08:00

SQL> select current_date from dual;

Current_date
-------------------
22:23:50

SQL> select current_timestamp from dual;

Current_timestamp
---------------------------------------------------------------------------
02-aug-06 10.24.04.031000 PM +

SQL> insert into tztest
2 values (sysdate, systimestamp );

1 row created.

SQL> commit;

Commit complete.

SQL> select * From tztest;

A
-------------------
B
---------------------------------------------------------------------------
C
---------------------------------------------------------------------------
D
---------------------------------------------------------------------------
22:25:59
02-aug-06 10.25.59 pm
02-aug-06 10.25.59 PM + 08:00
02-aug-06 10.25.59 pm

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0-Production
With the partitioning, OLAP and Oracle Data Mining options
Jserver release 9.2.0.3.0-Production

Changed the time zone of the client operating system

C: \ Documents ents and Settings \ Administrator> sqlplus sky/xxxx

SQL * Plus: Release 9.2.0.3.0-production on Wed Aug 2 23:28:01 2006

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

Connected:
Oracle9i Enterprise Edition Release 9.2.0.3.0-Production
With the partitioning, OLAP and Oracle Data Mining options
Jserver release 9.2.0.3.0-Production

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss ';

Session altered.

SQL> select sysdate from dual;

Sysdate
-------------------
22:28:49 <-The database has not been restarted and the time is still before modification

SQL> select distinct imestamp from dual;

Systimestamp
---------------------------------------------------------------------------
02-aug-06 11.29.33.609000 PM + 09:00 <-the time zone information has changed. Is the time zone automatically converted?

SQL> select * From tztest;

A
-------------------
B
---------------------------------------------------------------------------
C
---------------------------------------------------------------------------
D
---------------------------------------------------------------------------
22:25:59 <-not changed
02-aug-06 10.25.59 PM <-not changed
02-aug-06 10.25.59 PM + 08:00 <-save the time zone information
02-aug-06 11.25.59 PM <-time zone for automatically converting to session