How to query the time format in oracle

In Oracle, you can use the "select sysdate from dual;" statement to query the time format. The select statement can perform simple query on the data. This statement is used to format the output date.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How does oracle query time format

1. Oracle stores time format data

Oracle stores time in a specific storage format, accounting for 7 bytes, regardless of the time displayed by the query. The stored time includes year, month, day, hour, minute and second. The minimum precision is seconds. Time units below seconds are not stored. Therefore, you should pay attention to this when some front-end programs that support millisecond level (such as PB client programs) connect to the Oracle database. The time format displayed during query is determined by the session environment or defined by the user and has nothing to do with the database

2. Oracle time display format

Usually, the client and the database After the connection is established, Oracle will give a default time format data display form, which is related to the character set used. Generally, the year, month and day are displayed instead of hours, minutes 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, and when using the zhs16gbk character set (or other Chinese character sets), the default time format is displayed as: :2003-January-28. When inserting data into the table, if you do not use a conversion function, the format of the time field must comply with the time format of the session environment, otherwise the insertion cannot be performed. To view the time format of the current session, you can use the following SQL statement:

SQL> select sysdate from dual;

3. Insert time format data into the oracle table

Insert time format into the oracle table The data is a relatively troublesome thing. 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 the time value into a string form, usually used in queries; to_date (string, time format) converts the format string into a time value, usually used in the insert statement

4 How to modify the date format in the session

It is said that there are three ways to modify the date format in the current session. I have only tried the first two methods, modifying glogin.sql I haven't tried the file method, so I don't know if it really works. Explain one by one below.

(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'; Change the date format of the current session The time format is modified to this format: 2003-01-28 15:23:38, that is, four digits of the year - two digits of the month - two digits of the day, a space, and the hours: minutes: seconds of the 24-hour system.

This modification method is only valid for the current session. Note that this is for the current session, not the current sql*plus window. That is, if you

## are modified like this and then use the connect command to connect to the database as another user or to connect to another database, the date format will become invalid and return to the default date format.

(2) Modify the registry (only for windows systems)

Add a string (8i version) to the primary key of hkey_local_machinesoftwareoraclehome0 in the registry. The string name is nls_date_format, and the value of the string is The time format you want to define, such as: yyyy-mm-dd hh24:mi:ss, then restart sql*plus.

This modification method is valid for the sql*plus window, that is, no matter how many sql*plus windows you open, this time format will be the default. Modifying the server-side registry is invalid. Only modifying the client-side registry is effective.

(3) Modify the glogin.sql file in the $oracle_homesqlplusadmin directory

I have not tried this method, so I dare not say it randomly, so as not to mislead everyone. Please give me some advice from experts who know this method.

5 Priority of date format

If in a specific environment, both the registry and the current session are modified using the alter session command, then which One modification method that works? The alter session command is valid and has the highest priority. That is, no matter what the current client environment is like or how the registry has been modified, as long as you use the alter session command to modify the time format, then your modified time format will be allow. Therefore, if you directly use sql*plus interactive query or insert time format data, both methods can be used. If you use SQL files for batch insertion or scheduled execution, it is best to use the alter session command to modify the session before using time data to ensure that the normal operation of this SQL statement has nothing to do with the environment. Similarly, if the front-end client program needs to use data in time format, it is best to modify the session before use, or use the to_char or to_date function for conversion (the use of conversion functions is highly recommended) to ensure that the program runs in accordance with the environment. Nothing to do.

summary:

The oracle database defines many functions for time type data, especially for the calculation of time data, which is very convenient. Of course, the premise of convenience is that you are familiar with Oracle's time functions. Here I sometimes see some people using string type fields when storing time type data. I strongly oppose this approach, whether it is from the perspective of system overhead, calculation of time data, or the extraction of time data. A certain sub-data in this data, such as getting the month in time, getting the week in time, etc., is not as convenient as directly storing it as time type data. As long as you are familiar with Oracle's time functions, I believe everyone will like Oracle time type data. It is very flexible and powerful. You can do almost everything you can think of about time - it depends on your understanding of Oracle time. Function mastery

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to query the time format in oracle. For more information, please follow other related articles on the PHP Chinese website!