Performance of specific Oracle time applications

The following article mainly introduces the specific application of Oracle time. The reason why Oracle time can be widely used in a short period of time is also because of its unique functions. The following articles mainly introduce the specific Oracle time application and related functions.

1. The biggest relationship between the Conversion Function and the date operation is the two conversion functions: to_date (), to_char ()

To_date () converts the character type to the date type in a certain format:

Usage: to_date ('1970-11-27 '', ''yyyy-mm-dd''). The former is a string, and the latter is a conversion date format. Note, the two must be one-to-one.

For example, to_date ('2017-11-27 13:34:43 '', ''yyyy-mm-dd hh24: mi: s'') will obtain the specific Oracle time.

Multiple date formats:

YYYY: Year in four bits

YYY, YY, Y: the last three digits, two digits, or one digit of the year. The default value is the current century.

MM: 01 ~ 12 month number

MONTH: The MONTH, which contains nine characters. Fill the MONTH with spaces on the right.

MON: the abbreviation of A Three-character month

WW: week of the year

D: The day of the week

DD: The day of the month.

DDD: The day of the year

DAY: The full name of the DAY, expressed in nine characters, and filled with spaces on the right

HH, HH12: the hour of the day, 12 hexadecimal notation

HH24: The hour in a day. The value ranges from 00 ~ 23

MI: minute in an hour

SS: seconds in one minute

SSSS: the number of seconds since midnight

To_char (): Convert the date to a certain format to the character type

 
 

  
  
SQL> select to_char(sysdate,''yyyy-mm-dd hh24:mi:ss'') time from dual;  
  
  
TIME  
  
  
2004-10-08 15:22:58  
 
 

Converts the current Oracle time to the character type in yyyy-mm-dd hh24: mi: ss format.

Processing date in oracle

TO_DATE format

 
 

  
  
Day:   
  
  
dd number 12   
  
  
dy abbreviated fri   
  
  
day spelled out friday   
  
  
ddspth spelled out, ordinal twelfth   
  
  
Month:   
  
  
mm number 03   
  
  
mon abbreviated mar   
  
  
month spelled out march   
  
  
Year:   
  
  
yy two digits 98   
  
  
yyyy four digits 1998  
 
 

The time range in the 24-hour format is: 0:00:00-23:59:59 ....

The Oracle time range in the 12-hour format is: 1:00:00-12:59:59 ....

[ZT] Date and ?? Letter ???

1.

Date and character conversion function usage to_date, to_char)

2.

 
 

  
  
select to_char( to_date(222,''J''),''Jsp'') from dual  
 
 

Display Two Hundred Twenty-Two

3.

The day of the week.

 
 

  
  
select to_char(to_date(''2002-08-26'',''yyyy-mm-dd''),''day'') from dual;  
 
 

Monday

 
 

  
  
select to_char(to_date(''2002-08-26'',''yyyy-mm-dd''),''day'',
''NLS_DATE_LANGUAGE = American'') from dual;   
  
  
monday  
 
 

Set the date language

 
 

  
  
ALTER SESSION SET NLS_DATE_LANGUAGE=''AMERICAN'';  
 
 

You can also

 
 

  
  
TO_DATE (''2002-08-26'', ''YYYY-mm-dd'', ''NLS_DATE_LANGUAGE = American'')  
 
 

4.

Number of days in a two-day period

 
 

  
  
select floor(sysdate - to_date(''20020405'',''yyyymmdd'')) from dual;  
 
 

5. Use a time of null

 
 

  
  
select id, active_date from table1   
  
  
UNION   
  
  
select 1, TO_DATE(null) from dual;  
 
 

Note that TO_DATE (null) is used)

6.

 
 

  
  
a_date between to_date(''20011201'',''yyyymmdd'') and to_date(''20011231'',''yyyymmdd'')  
 
 

Therefore, it is not included in this range after on January 1, December 31 and before on January 1, December 1.

Therefore, when Oracle time needs to be accurate, to_char is still necessary.

7. Date Format conflict

The input format depends on the type of the ORACLE character set you installed, for example, US7ASCII. The date format is ''01-Jan-01''

 
 

  
  
alter system set NLS_DATE_LANGUAGE = American   
  
  
alter session set NLS_DATE_LANGUAGE = American  
 
 

Or write it in to_date.

 
 

  
  
select to_char(to_date(''2002-08-26'',''yyyy-mm-dd''),''day'',
''NLS_DATE_LANGUAGE = American'') from dual;  
 
 

Note that I just mentioned NLS_DATE_LANGUAGE. Of course there are many more,

Available

 
 

  
  
select * from nls_session_parameters   
  
  
select * from V$NLS_PARAMETERS  
 
 

8.

 
 

  
  
select count(*)   
  
  
from ( select rownum-1 rnum   
  
  
from all_objects   
  
  
where rownum <= to_date(''2002-02-28'',''yyyy-mm-dd'') - to_date(''2002-   
  
  
02-01'',''yyyy-mm-dd'')+1   
  
  
)   
  
  
where to_char( to_date(''2002-02-01'',''yyyy-mm-dd'')+rnum-1, ''D'' )   
  
  
not   
  
  
in ( ''1'', ''7'' )  
 
 

Call DBMS_UTILITY.GET_TIME before and after the day except Monday and seven between 1/100-02-01, respectively, to subtract the result (seconds instead of milliseconds ). the above content is an introduction to the Oracle time application. I hope you will get some benefits.