See a lot of Oracle new scholars on the Internet annoying time format for Oracle, often after designing a table with a time field, inserting data into the table fails. Recall that I was just beginning to learn Oracle, but also to this time format can not touch the mind, although it is a small problem, the experts disdain to discuss, but for beginners, this is also a small point, did not find the way, it may be a few days, is actually a layer of window paper, nothing difficult. Here to do a simple summary, because the level is limited, please add a master.
1 Oracle data storage for TIME formats
The data stored in the time format in Oracle database is stored in Oracle-specific format, accounting for 7 bytes, regardless of the time format that is displayed on the query, and which byte represents what, I am not sure, please add the master. The time of storage includes the day of the month and the minute, the minimum precision is seconds, and the time unit of the second is not stored. Therefore, you should be aware of this when you connect to an Oracle database in some foreground programs that support milliseconds, such as a PB client program. The time format that is displayed when querying is determined by the session environment, or is user-defined, regardless of the database.
2 Oracle Time Display form
Typically, when a client is connected to a database, Oracle gives a default time format data display, which is related to the character set used. Usually displays the day of the month, but not the time and seconds. For example, when using the Us7ascii character set (or other English character sets), the default time format is displayed as: 28-jan-2003, while the time format is displayed by default when using the ZHS16GBK character set (or other Chinese character set): 2 March-January-28. When inserting data into a table, if you do not use a transform function, the format of the time field must conform to the time format of the session environment, otherwise it cannot be inserted. To view the time format for the current session, you can use the following SQL statement:
Sql> select Sysdate from dual;
3 Inserting time-formatted data into an Oracle table
Inserting time-formatted data into an Oracle table is a relatively troublesome matter, either you write your time value strictly in accordance with the time format of the current session, or you use a conversion function to customize the format of the time data. There are two time-related conversion functions: To_char and To_date. The To_char (time value, time format) function converts a time value to a string form, usually used in a query, and to_date (string, time format) converts a format string to a time value, typically used in an INSERT statement. In the date format, the various date formats
The component is not discussed in detail here, please check the use of these two functions in the Oracle Help documentation, or check the Book of PL/SQL programming for details.
4 How to modify the date format in a session
Modify the date format in the current session there are three ways that I have only done the first two methods of testing, modify the Glogin.sql file method I have not tried, I do not know whether it can really do. Below are one by one explanations.
(1) Modify the date format of the current session in Sql*plus
Sql> alter session Set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss ';
Modifies the current session's time format to this format: 2003-01-28 15:23:38, i.e. four-bit-two-bit-two-bit day, space, 24-hour hour: minutes: seconds.
This modification method is valid only for the current session. Note that it is the current session, not the current Sql*plus window. That is, if you modify this and then use the Connect command to connect to the database with another user or connect to a different database, the date format is invalidated and reverts to the default date format.
(2) Modify the registry (only for Windows systems)
Add a string (8i version) to the registry \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 primary key, the string name is Nls_date_format, and the string value is the time format you want to define, such as: YYYY-MM-DD HH24:MI:SS, and then restart Sql*plus.
This method of modification is valid for the Sql*plus window, that is, no matter how many sql*plus windows you open, the default is this time format. Modifying the server-side registry is not valid, only modifying the client's registry.
There is no such method under the UNIX system, I do not know, I will not use UNIX or Linux system.
(3) Modify the Glogin.sql file in the $oracle_home\sqlplus\admin directory
This method I have not tried, dare not talk nonsense, lest mislead everybody. Please understand this method of expert to advise.
Priority of the 5th-year format
If, in a specific environment, both the registry is modified and the current session is modified with the ALTER session command, which method of modification is valid? The alter session command is valid and its priority is the highest. That is, regardless of the current client environment,
The registry changes to what it looks like, as long as you use the ALTER session command to modify the time format, then your modified time format will prevail.
Therefore, both methods can be used if you are using Sql*plus Interactive queries or inserting data in time format directly. If you are using a SQL file for bulk inserts or timed execution, it is a good idea to modify the session with the ALTER session command before using the time data to ensure that the SQL statement is not working properly with the environment. Similarly, if the client program in the foreground needs to use the data in the time format, it is best to modify the session before using it, or use the To_char or To_date function to convert it (it is strongly recommended to use a conversion function) to ensure
The program runs independently of the environment.
Summary:
The time-type data in an Oracle database defines a number of functions, especially the calculation of time data, which is convenient and, of course, handy if you are familiar with Oracle's time functions. Here I sometimes see that some people use string type fields when storing data for time types, and I strongly oppose this practice, whether from the overhead of the system, or from the computation of the time data, or by taking a certain sub-data in the data, such as taking the month of the time, taking the week of the time, etc. is not as convenient as storing data directly as a time type. As long as you're familiar with Oracle's time-related functions, I'm sure you'll love the Oracle time-type data, very flexible, powerful, and almost everything you can think of about time-depends on how much you know about Oracle's time function.
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Analysis of Oracle Time data format
