Shulou(Shulou.com)06/01 Report--

Original: http://blog.itpub.net/9765498/viewspace-539881

Background description: if you need to support an internationalized application, then the database-side internationalization feature support is particularly important. There are many features in Oracle that support internationalization, such as character sets, time zones, and so on. If the relevant parameters are not set properly, or because of the lack of understanding of the relevant features, it is not fully considered in the design stage, then it will certainly cause some loss to the application. Not long ago, I also encountered problems related to time zone, so I would like to make a small summary of the time zone with the problems encountered.

1. How to view and modify the database and session time zone

The relevant time zones in Oracle can be divided into two categories: database time zone and session time zone. It can be obtained in the following ways:

View database time zone information:

SQL > select dbtimezone from dual

DBTIME

-

+ 08:00

View session time zone information:

SQL > select sessiontimezone from dual

SESSIONTIMEZONE

+ 08:00

The timezone of Database can be specified when creating the database, such as:

CREATE DATABASE db01

...

SET TIME_ZONE='+08:00'

Or it can be modified by alter database statement after the database is created, but it is only valid after the database is restarted:

ALTER DATABASE SET TIME_ZONE='+08:00'

The timezone of session can be modified simply through the alter session statement:

ALTER SESSION SET TIME_ZONE='+08:00'

Note: Database Time Zone is only related to the TIMESTAMP WITH LOCAL TIME ZONE data type! In fact, the database timezone is only a yardstick for calculation. After the TIMESTAMP WITH LOCAL TIME ZONE data type is introduced into the database from the client, it is converted to the database time zone and stored in the database. When the relevant calculation is needed, Oracle first converts the time to standard time (UTC), and then saves the time when the result is converted to the database time zone after the calculation is completed. For more information about the TIMESTAMP WITH LOCAL TIME ZONE data type, please refer to the following relevant section:)

two。 Time zone related data types

The main data types related to the time zone are DATE,TIMESTAMP,TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. A rough introduction is as follows:

DATE: stores date and time information accurate to seconds.

SQL > alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'

Session altered.

SQL > select to_date ('2009-01-12 13-13-24-14-33-13-13-12-12-12-12-14-12-12-12-12-12-14-12-12-14-12-12-14-12-12-12-14-12-12-12-14-12-12-12-12-14-12-12-12-12-14-12-12-14-12-12-14-12-12-14-12-12-14-12-12-14-12-12-13-12-12-14-12-14-12-12-13-12-12-12-13-12-12-13-12-12-13-13-12-12-13-13-12-12-12-13-13-12-12-12-13-13-12-12-

TO_DATE ('2009-01-12

-

2009-01-12 13:24:33

TIMESTAMP: an extension of the DATE type that retains seconds at the decimal level and defaults to 6 places after the decimal point. Time zone and locale information is not saved.

SQL > select localtimestamp from dual

LOCALTIMESTAMP

12-JAN-09 07.21.37.984000 PM

TIMESTAMP WITH TIME ZONE: stores the TIMESTAMP with time zone information (saved in the form of time difference from UTC or region information). The form is roughly as follows:

TIMESTAMP '2009-01-12 8:00:00 + 8purl'

TIMESTAMP WITH LOCAL TIME ZONE: another different type of TIMESTAMP, which differs from the TIMESTAMP WITH TIME ZONE type in that the database does not store information about the time zone, but converts the time entered by the client into a database timezone-based time and stores it in the database (which is the meaning of the database tmiezone setting, as a scale for the TIMESTAMP WITH LOCAL TIME ZONE type). When the user requests this type of information, Oracle converts the data into the time zone time of the user's session and returns it to the user. Therefore, Oracle recommends that database timezone be set to standard time UTC, which can save the cost of each conversion and improve performance.

Here are some experiments on the above types:

Manipulate DATE type data:

SQL > INSERT INTO table_dt VALUES (1meme date '2009-01-01')

1 row created.

SQL > INSERT INTO table_dt VALUES (2 Asia/Hong_Kong' timestamp '2009-01-01 00:00:00)

1 row created.

SQL > INSERT INTO table_dt VALUES ('01MJANMY 2009')

1 row created.

SQL > commit

Commit complete.

SQL > select * from table_dt

C_ID C_DT

--

1 2009-01-01 00:00:00

2 2009-01-01 00:00:00

3 2009-01-01 00:00:00

Manipulate the TIMESTAMP data type:

SQL > ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF'

Session altered.

SQL > CREATE TABLE table_ts (c_id NUMBER, c_ts TIMESTAMP)

Table created.

SQL > INSERT INTO table_ts VALUES (1, '01-JAN-2009 2rig 0000')

1 row created.

SQL > INSERT INTO table_ts VALUES (2, TIMESTAMP '2009-01-01-01 2lv 0000')

1 row created.

SQL > INSERT INTO table_ts VALUES (3, TIMESTAMP '2009-01-01 2:00:00-08 TIMESTAMP')

1 row created.

SQL > commit

Commit complete.

SQL > set linesize 120

SQL > select * from table_ts

C_ID C_TS

-

1 01-JAN-09 02:00:00.000000

2 01-JAN-09 02:00:00.000000

3 01-JAN-09 02:00:00.000000

Note: the time zone information of the third piece of data is lost!

Manipulate the TIMESTAMP WITH TIME ZONE data type:

SQL > ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR'

Session altered.

SQL > ALTER SESSION SET TIME_ZONE='-7:00'

Session altered.

SQL > CREATE TABLE table_tstz (c_id NUMBER, c_tstz TIMESTAMP WITH TIME ZONE)

Table created.

SQL > INSERT INTO table_tstz VALUES (1, '01-JAN-2009 2:00:00 AM-07 01-JAN-2009 00')

1 row created.

SQL > INSERT INTO table_tstz VALUES (2, TIMESTAMP '2009-01-01-01 2lv 0000')

1 row created.

SQL > INSERT INTO table_tstz VALUES (3, TIMESTAMP '2009-01-01 2:00:00-8)

1 row created.

SQL > commit

Commit complete.

SQL > select * from table_tstz

C_ID C_TSTZ

-

1 01-JAN-09 02VR 001R 00.000000 AM-07:00

2 01-JAN-09 02VOV 00VOUR 00.000000 AM-07:00

3 01-JAN-09 02VR 001R 00.000000 AM-08:00

Note: the third piece of data holds the time zone information! You can make a comparison with the TIMESTAMP type of the previous example.

Manipulate the TIMESTAMP WITH LOCAL TIME ZONE data type:

SQL > ALTER SESSION SET TIME_ZONE='-07:00'

Session altered.

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

Table created.

SQL > INSERT INTO table_tsltz VALUES (1, '01-JAN-2009 2rig 0000')

1 row created.

SQL > INSERT INTO table_tsltz VALUES (2, TIMESTAMP '2009-01-01-01 2lv 0000')

1 row created.

SQL > INSERT INTO table_tsltz VALUES (3, TIMESTAMP '2009-01-01 2:00:00-08 TIMESTAMP')

1 row created.

SQL > commit

Commit complete.

SQL > select * from table_tsltz

C_ID C_TSLTZ

-

1 01-JAN-09 02:00:00.000000

2 01-JAN-09 02:00:00.000000

3 01-JAN-09 03:00:00.000000

Note: the third piece of data inserted is specified as the time of the UTC-8 time zone, and then stored in the database according to the time of database timezone. Finally, when requested by the client, the time of conversion to the client time zone (UTC-7) is returned! Please refer to the following simple experiments:

SQL > ALTER SESSION SET TIME_ZONE='-05:00'

Session altered.

SQL > select * from table_tsltz

C_ID C_TSLTZ

-

1 01-JAN-09 04:00:00.000000

2 01-JAN-09 04:00:00.000000

3 01-JAN-09 05:00:00.000000

As you can see, when the client time zone is changed to UTC-5, the return information of the TIMESTAMP WITH LOCAL TIME ZONE data type will change accordingly.

After understanding the relevant data types, how can we choose between them?

When you do not need to save time zone / region information, choose to use the TIMESTAMP data type, because it generally requires 7-11bytes storage space, which can save space.

When you need to save time zone / region information, please choose to use the TIMESTAMP WITH TIME ZONE data type. For example, a multinational banking application system needs to accurately record the time and place (time zone) of each transaction, in which case it is necessary to record information about the time zone. Because you need to record information about the time zone, you need more storage space, usually 13bytes.

When you don't care about the exact place where the operation takes place, but only care about what time the operation occurs in your current time zone, choose to use TIMESTAMP WITH LOCAL TIME ZONE. Such as a globally unified change control system. Users may only care about when the so-and-so operation happened in my time (for example, Chinese users see Beijing time 8:00am, while London users see 0:00am). Remember, such lines do not save time zone / locale information, so be careful if you need to save relevant information!

3. Several functions related to time zone

DBTIMEZONE-- Returns the value of the database time zone. The value is a time zone offset or a time zone region name.

SESSIONTIMEZONE-- Returns the value of the current session's time zone.

CURRENT_DATE-Returns the current date in the session time zone in a value in the Gregorian calendar, of the DATE datatype.

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

SYSDATE-Returns the date and time of the operating system on which the database resides, taking into account the time zone of the database server's operating system that was in effect when the database was started.

SYSTIMESTAMP-Returns the system date, including fractional seconds and time zone of the system on which the database resides.

Note: the return information of SYSDATE and SYSTIMESTAMP is the information of the operating system where the database resides, regardless of the time zone of the current session!

Example:

The database time zone is + 08:00 and the current session time zone is-05: 00:00:

SQL > select dbtimezone from dual

DBTIME

-

+ 08:00

SQL > select sessiontimezone from dual

SESSIONTIMEZONE

05:00

SQL > select current_date from dual

CURRENT_DATE

-

2009-01-12 06:18:24

SQL > select current_timestamp from dual

CURRENT_TIMESTAMP

12-JAN-09 06VOF 18VOR 36.625000 AM-05:00

SQL >

SQL > select sysdate from dual

SYSDATE

-

2009-01-12 19:18:42

SQL > select systimestamp from dual

SYSTIMESTAMP

12-JAN-09 07Viru 18 PM 52.921000 PM + 08:00

SQL >

Change the current session time zone to + after 09:00:

SQL > alter session set time_zone='+09:00'

Session altered.

SQL >

SQL > select dbtimezone from dual

DBTIME

-

+ 08:00

SQL > select sessiontimezone from dual

SESSIONTIMEZONE

+ 09:00

SQL > select current_date from dual

CURRENT_DATE

-

2009-01-12 20:19:54

SQL >

SQL > select current_timestamp from dual

CURRENT_TIMESTAMP

12-JAN-09 08virtual 20 PM 07.218000 PM + 09:00

SQL >

SQL > select sysdate from dual

SYSDATE

-

2009-01-12 19:20:24

SQL > select systimestamp from dual

SYSTIMESTAMP

12-JAN-09 07virtual 20 PM + 08:00 30.921000

SQL >

As can be seen from the above examples, the return results of SYSDATE and SYSTIMESTAMP do not change with the change of SESSION time zone. In fact, you can see from the name of the function (one is system, the other is current): d

Conclusion: since there are only so many things involved in this case, it is not comprehensive to sum up, everything is included. This is just a simple summary of how to view and modify the database / session time zone, related data types and functions. There are also things like Interval Datatypes (which stores time intervals), Daylight Saving Time (daylight saving time, which I still don't know), and other functions,parameters that are not covered.

For a systematic introduction to time zone, please refer to Oracle Database Globalization Support Guide, Chapter 4. There are other official documents and metalink that can be used as references.

PS: a little case ~

The application layer user found that the sysdate information was incorrect. The time, which was supposed to be + 09:00, was displayed as the time in the time zone of-05:00, and asked to modify the database timezone. In fact, the information returned by sysdate has nothing to do with the database timezone settings, so go to check the operating system information. It is found that there is a problem with the setting of the operating system layer. But the problem at that time was that the operating system could not be restarted casually, and the problem became very thorny!

Later, after the suggestion of colleagues, I set up the session information of the operating system: setenv TZ Japan. Then restart listener and database. After that, all users who connect to the database via listener select sysdate from dual; result in correct information, while users who do not connect through listener get the wrong information because the time zone of the operating system itself is not updated. It is said that after setting the time zone information at the session level, it is enough to restart listener. Individuals have not tried it. Those who are interested can try it.

The fundamental solution to the problem is to modify the operating system's time zone setting, but a temporary solution is also good:)

There are also some summaries about character sets, regions and other international features, which will be sent out later.:)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.