Oracle Data Types

Oracle data type description-the date-type oracle Data Type looks very simple, but many knowledge points will be found in use. This article is my preparation of the ORACLE date data type, all of which are development entry-level materials, share with you: Note: Since INTERVAL and TIMEZONE are rarely used, this article does not cover these two aspects. 1. Common

Oracle data type description-the date-type oracle Data Type looks very simple, but many knowledge points will be found in use. This article is my preparation of the ORACLE date data type, all of which are development entry-level materials, share with you: Note: Since INTERVAL and time zone are rarely used, this article does not cover these two aspects. 1. Common

Oracle data type description-date type

Oracle data types seem very simple, but many knowledge points will be found in use. This article is my summary of ORACLE date data types, all of which are getting started with development. share with you:
Note: Since INTERVAL and time zone are rarely used, this article does not cover these two aspects.

1. Common date data types
1.1, DATE
This is the most common date type in ORACLE. It can save Date and time. This type can be used for common date processing. The DATE range can be from January 1, January 1, 4712 BC to January 1, December 31, 9999 AD.
The storage of the date type in the database is fixed to seven bytes in the format:
1st Bytes: century + 100
2nd Bytes: Year
3rd Bytes: Month
4th Bytes: Day
5th Bytes: hour + 1
6th Bytes: + 1
7th Bytes: Second + 1

1.2, TIMESTAMP (p)
This is also a common date type in ORACLE. It differs from date in that it can not only save the date and time, but also save decimal seconds. The decimal places can be specified as 0-9, the default value is 6 bits, so the highest precision can be ns (nanoseconds). The database is stored in 7 or 11 bytes. If the precision is 0, it is stored in 7 bytes, the function is the same as the date type. If the precision is greater than 0, it is stored in 11 bytes.
Format:
1st Bytes: century + 100
2nd Bytes: Year
3rd Bytes: Month
4th Bytes: Day
5th Bytes: hour + 1
6th Bytes: + 1
7th Bytes: Second + 1
8-11 bytes: nanoseconds, which are stored in 4 bytes. The internal operation type is integer.

Note: If the timestamp date type is added or subtracted from the value, it is automatically converted to the DATE type, that is, it is automatically removed in decimal seconds.

1.3 DATE and TIMESTAMP internal storage Verification

 1 create table T 2 ( 3   C1 DATE, 4   C2 TIMESTAMP(9) 5 ); 6  7 insert into t(c1,c2) values(date'2010-2-12',timestamp'2010-2-12 13:24:52.234123211'); 8 insert into t(c1,c2) values( 9        to_date('2010-2-12 10:20:30','YYYY-MM-DD HH24:MI:SS'),10        to_timestamp('2010-2-12 13:24:52.123456','YYYY-MM-DD HH24:MI:SS.FF6')11 );12 13 SQL> select c1,dump(c1) c1_d,c2,dump(c2) c2_d from t;

C1 C1_D C2 C2_D
------------------------ ---------------------------------------------------------------------------- Accept -------------------------------------------------------------------------------------------------------------------------------------------------------------
2010-2-12 Typ = 12 Len = 7: 120,110, 180, 120,110, 1 12-FEB-10 01.24.52.234123211 PM Typ = 244,111,203 Len = 11 :,
10:20:30 Typ = 12 Len = 7: 120,110, 180, 120,110, 31 12-FEB-10 01.24.52.123456000 PM Typ = 91,202 Len = 11 :,

The following is a test to verify the TIMESTAMP's scale storage algorithm:

1 SQL> select c2,dump(c2,16) c2_d16 from t;

C2 C2_D16
------------------------------------------------------------------------------------------------------------------------
12-FEB-10 01.24.52.234123211 PM Typ = 180 Len = 11: 78, 6e, 2, c, e, 19,35, d, f4, 6f, cb
12-FEB-10 01.24.52.123456000 PM Typ = 180 Len = 11: 78, 6e, 2, c, e, 19,35, 7, 5b, ca, 0

SQL> select to_number('0df46fcb','xxxxxxxx') mydata1,to_number('075bca00','xxxxxxxx') mydata2 from dual;

MYDATA1 MYDATA2
--------------------
234123211 123456000

2. FAQs
2.1. How to obtain the current time
Sysdate -- returns the current system date and time, accurate to seconds
Systimestamp -- returns the current system date and time, accurate to milliseconds
2.2. How to perform date operations
The date type data can be added or subtracted from the value to obtain a new date. The unit of the value is day.
Sysdate + 1 -- get the current time of tomorrow
Sysdate-1/24 -- Take the previous hour of the current time

SQL> select sysdate d1,sysdate+1 d2,sysdate-1/24 d3 from dual;

D1 D2 D3
------------------------------------------------------------------------
10:55:16 10:55:16

2.3. How to calculate the interval between two dates?
Two dates can be directly subtracted. The returned unit is day, and the hours and minutes are converted into decimal places.

SQL> select date'2012-01-01'-sysdate from dual;

DATE '2017-01-01 '-SYSDATE
------------------------
597.046030092593

2.4. How to convert a date to a character
To_char (sysdate, 'YYYY-MM-DD HH24: MI: ss ')
2.5. How to convert character to date
To_date ('2017-02-24 15:01:54 ', 'yyyy-MM-DD HH24: MI: ss ')
To_timestamp ('2017-12-01 11:00:00. 100', 'yyyy-MM-DD HH: MI: SS. ff6 ')

3. Common date functions
3.1. TO_CHAR(DATE, FORMATSTR) -- format the DATE to a character

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') d1 from dual;

D1
------------------------
2010-05-13 22:56:38

Other usage examples of TO_CHAR

1 SQL> SELECT TO_CHAR (date '2017-02-12 ', 'D') week_dayth, -- day of week (1-7), Sunday = 1, Monday = 2, tuesday = 3, wedday = 4, Thursday = 5, Friday = 6, Saturday = 72 TO_CHAR (date '2017-02-12 ', 'dd') month_dayth, -- DAY of the month 3 TO_CHAR (date '2017-02-12 ', 'ddd') year_dayth, -- DAY of the year 4 TO_CHAR (date '2017-02-12 ', 'day') weekdayname, -- English name of the week 5 _ CHAR (date '2017-02-12 ', 'w') month_weekth, -- week of the month (0-4) 6 TO_CHAR (date '2017-02-12 ', 'ww') year_weekth -- week of the year (0-53) 7 from dual;

WEEK_DAYTH MONTH_DAYTH YEAR_DAYTH WEEKDAYNAME MONTH_WEEKTH YEAR_WEEKTH
-----------------------------------------------------------------
6 12 043 FRIDAY 2 07

3.2, TO_DATE(CHAR, FORMATSTR) -- converts a character to a date
To_date ('2017-02-24 15:01:54 ', 'yyyy-MM-DD HH24: MI: ss ')
Format remarks:
HH indicates 12 hours, HH24 indicates 24 hours, MM indicates month, and MI indicates minute.

3.3. TRUNC(DATE) -- returns the DATE part of the DATE. The time is 00:00:00.

SQL> select sysdate d1,trunc(sysdate) d2 from dual;

D1 D2
------------------------------------------------
10:59:18

3.4. EXTRACT(Data from datevalue) -- returns a part of DATE.
If DATEVALUE is of the DATE type, DATA can be (YEAR, MONTH, DAY)
If DATEVALUE is of the TIMESTAMP type, DATA can be (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)

SQL> select sysdate d1,EXTRACT(YEAR FROM sysdate) thisyear,EXTRACT(MINUTE FROM systimestamp) thism from dual;

D1 THISYEAR THISM
--------------------------------------------
11:05:06 2010 5
3.5, ADD_MONTHS(DATE, MONTHS) -- add a month to DATE to get a new DATE.

ADD_MONTHS (sysdate, 3) -- the current date plus three months
ADD_MONTHS is an interesting function that automatically processes the month and month, As shown below

1   SQL> select ADD_MONTHS(date '2010-2-12', 1),2   ADD_MONTHS(date '2010-2-27', 1),3   ADD_MONTHS(date '2010-2-28', 1),4   ADD_MONTHS(date '2010-1-31', 1)5   from dual6   ;

ADD_MONTHS (DATE '2017-2-12 ', 1) ADD_MONTHS (DATE '2017-2-27', 1) ADD_MONTHS (DATE '2017-2-28 ', 1) ADD_MONTHS (DATE '2017-1-31 ', 1)
--------------------------------------------------------------------------------------------------------------------
2010-3-12 2010-3-27 2010-3-31 2010-2-28

3.6. LAST_DAY(DATE) -- returns the DATE of the last day of the month in which the DATE belongs.

SQL> select LAST_DAY(date '2010-2-12') from dual;

LAST_DAY (DATE '2014-2-12 ')
-------------------------
2010-2-28

3.7. NEXT_DAY(DATE, CHAR) -- returns the DATE of the next CHAR specified week from the given DATE.

SQL> SELECT NEXT_DAY(date'2010-2-21', 'MONDAY') NEXTDAY1,NEXT_DAY(date'2010-2-22', 'MONDAY') NEXTDAY2 FROM DUAL;

NEXTDAY1 NEXTDAY2
----------------------
2010-2-22 2010-3-1

TO_YMINTERVAL (CHAR) -- returns the interval in the [year-month] format. It is generally used for date addition and subtraction.

3.8. TO_DSINTERVAL(CHAR) -- returns the time interval in the format of [Day: minute: Second], which is generally used for date addition and subtraction.

SQL> select date'2010-2-12'+TO_YMINTERVAL('01-02') newdate from dual;

NEWDATE
------------------------------
2011-4-12

3.9. NUMTOYMINTERVAL(N, CHAR) -- returns the time interval value of the specified unit in CHAR. It is generally used for addition and subtraction of dates.
Char can be YEAR, MONTH

1 SQL> select date '2010-2-12' + NUMTOYMINTERVAL(1, 'year') newdate1,2 date '2010-2-12' + NUMTOYMINTERVAL(1, 'month') newdate23 from dual;

NEWDATE1 NEWDATE2
----------------------
2011-2-12 2010-3-12

3.10. NUMTODSINTERVAL(N, CHAR) -- returns the time interval value of the specified unit in CHAR. It is generally used for addition and subtraction of dates.
Char can be DAY, HOUR, MINUTE, SECOND

1 SQL> select date '2010-2-12' + NUMTODSINTERVAL(1, 'DAY') newdate1,2 date '2010-2-12' + NUMTODSINTERVAL(1, 'HOUR') newdate23 from dual;

NEWDATE1 NEWDATE2
-----------------------------------
01:00:00

Http://www.cnblogs.com/chuncn/archive/2009/01/29/1381281.html

Http://wenku.baidu.com/view/65b46e1810a6f524ccbf855b.html

Http://wenku.baidu.com/view/c41654d084254b35eefd341c.html

Oracle date data type Google