Oracle data type Detailed---date type __oracle

Oracle Data type detailed---date type

Oracle data types look very simple, but there are a lot of knowledge to use, this article is my Oracle date data types of the collation, are the development of introductory materials, to share with you:
Note: Because interval and time zone are actually used relatively little, so this article does not cover these two aspects.

1, commonly used date type data types
1.1. DATE
This is the most commonly used date type for Oracle, which can hold dates and times, which can be used for common date processing. The date range represented by date can be January 1, 4712 to A.D. December 31, 9999
The date type's storage in the database is fixed to 7 bytes, in the form:
1th byte: Century +100
2nd byte: Year
3rd Byte: Month
4th Byte: Day
5th byte: Hour +1
6th byte: Minute +1
7th byte: Seconds +1

1.2, TIMESTAMP (p)
This is also the date type commonly used by Oracle, it differs from date in that it saves not only the date and time, but also the decimal seconds, the scale can be specified as 0-9, the default is 6 bits, so the highest precision can go to NS (nanoseconds), the database is stored within 7 or 11 bytes, and if the precision is 0, 7-byte storage with the same functionality as the date type, and 11-byte storage if the precision is greater than 0.
The format is:
1th byte: Century +100
2nd byte: Year
3rd Byte: Month
4th Byte: Day
5th byte: Hour +1
6th byte: Minute +1
7th byte: Seconds +1
第8-11 bytes: nanosecond, with 4 byte storage, internal operation type is shaping

Note: The timestamp date type is automatically converted to the date type if the value is added and subtraction, which means that the decimal seconds are automatically removed.

1.3, date and timestamp type internal storage authentication

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 '),        to_ Timestamp (' 2010-2-12 13:24:52.123456 ', ' yyyy-mm-dd HH24:MI:SS.FF6 ')
; 
sql> Select C1,dump (C1) c1_d,c2,dump (C2) c2_d from T;

C1 c1_d C2 C2_d
------------------------ ---------------------------------------- ---------------------------------------- ------- ----------------------------------------------
2010-2-12 typ=12 len=7:120,110,2,12,1,1,1 12-feb-10 01.24.52.234123211 PM typ=180 len=11: 120,110,2,12,14,25,53,13,244,111,203
2010-2-12 morning 10:20:30 typ=12 len=7:120,110,2,12,11,21,31 12-feb-10 01.24.52.123456000 PM typ=180 len=11:1 20,110,2,12,14,25,53,7,91,202,0

The following is to verify the timestamp decimal storage algorithm for testing:

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. Frequently Asked Questions
2.1. How to take 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 do date operation
Date-type data can be added to the value minus the new date, plus minus the number of days
sysdate+1--take tomorrow's current time
sysdate-1/24--take the first one hours of the current time

Sql> Select Sysdate d1,sysdate+1 d2,sysdate-1/24 D3 from dual;

D1 D2 D3
------------------------ ------------------------ ------------------------
2010-5-13 10:55:16 2010-5-14 10:55:16 2010-5-13 09:55:16

2.3. How to find the time interval of two dates
Can be directly subtracted from the two date, the return unit for days, hours and seconds will be converted to decimal

sql> Select Date ' 2012-01-01 '-sysdate from dual;

DATE ' 2012-01-01 '-sysdate
------------------------
597.046030092593

2.4. How to convert dates to characters
To_char (sysdate, ' yyyy-mm-dd HH24:MI:SS ')
2.5. How to convert characters to date
To_date (' 2010-02-24 15:01:54 ', ' yyyy-mm-dd HH24:MI:SS ')
To_timestamp (' 1999-12-01 11:00:00.123456 ', ' Yyyy-mm-dd HH:MI:SS. FF6 ')

3. Common Date function
3.1, To_char(DATE,FORMATSTR)--format date into characters

Sql> Select To_char (sysdate, ' yyyy-mm-dd HH24:MI:SS ') D1 from dual;

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

Examples of other usages of To_char

1 sql> SELECT to_char (date ' 2010-02-12 ', ' D ') Week_dayth,--Zhou Di days (1-7), Sunday = 1, Monday = 2, Tuesday = 3, Wednesday = 4, Thursday = 5, Friday = 6, Saturday =7
2 to_char (date ' 2010-02-12 ', ' DD ') month_dayth,--months of the day
3 to_char (date ' 2010-02-12 ', ' DDD ') Year_dayth,--years of the first day
4 to_char (date ' 2010-02-12 ', ' Day ') WeekdayName,--English week name
5 _char (date ' 2010-02-12 ', ' W ') month_weekth,--months of the week ( 0-4)
6 to_char (date ' 2010-02-12 ', ' ww ') year_weekth--the first weeks (0-53)
7 from DUAL;

Week_dayth Month_dayth Year_dayth weekdayname month_weekth year_weekth
---------- ----------- ---------- ----------- ------------ -----------
6 043 FRIDAY 2 07

3.2, To_date (CHAR,FORMATSTR)--converts characters to dates
To_date (' 2010-02-24 15:01:54 ', ' yyyy-mm-dd HH24:MI:SS ')
Format notes:
HH represents a 12-hour system, HH24 represents a 24-hour system, MM represents the month, MI represents minutes.

3.3, TRUNC (date)--Returns the date part of the day, 0 points 0 minutes 0 seconds

Sql> Select Sysdate d1,trunc (sysdate) D2 from dual;

D1 D2
------------------------ ------------------------
2010-5-13 10:59:18 2010-5-13

3.4, EXTRACT (DATA from DATEVALUE)--Returns some part of date
If DateValue is a date type, then data can be (year, MONTH, day)
If DateValue is a timestamp type, data can be (year, Month,day, HOUR, MINUTE, SECOND)

Sql> Select Sysdate d1,extract (year from Sysdate) Thisyear,extract (MINUTE to Systimestamp) thism from dual;

D1 Thisyear thism
------------------------ ---------- ----------
2010-5-13 11:05:06 2010 5
3.5, Add_months (date,months)--new date in date increment month

Add_months (sysdate,3)--current date plus 3 months
Add_months is an interesting function that automatically handles the size and leap months , 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   dual
6   ;

Add_months (Date ' 2010-2-12 ', 1) add_months (date ' 2010-2-27 ', 1) add_months (date ' 2010-2-28 ', 1) add_months ( DATE ' 2010-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 is located

Sql> Select Last_day (Date ' 2010-2-12 ') from dual;

Last_day (DATE ' 2010-2-12 ')
-------------------------
2010-2-28

3.7, Next_day (Date,char)--Returns the date of the next CHAR specified week starting with a 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 time interval of the [year-month] format, typically used for date addition and subtraction operations

3.8, To_dsinterval (CHAR)--Returns the time interval, in the form of a [timing: minute: sec] format, that is typically used for date subtraction operations

sql> Select Date ' 2010-2-12 ' +to_yminterval (' 01-02 ') newdate from dual;

Newdate
------------------------------
2011-4-12

3.9, Numtoyminterval (N,char)--Returns the value of the time interval for a specified unit in CHAR, typically used for date addition and subtraction operations
Char can be a year,month

1 sql> Select Date ' 2010-2-12 ' + numtoyminterval (1, ' year ') newdate1,
2 Date ' 2010-2-12 ' + numtoyminterval (1, ' Mont H ') Newdate2
3 from dual;

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

3.10, Numtodsinterval (N,char)--Returns the value of the time interval for a specified unit in CHAR, typically used for date addition and subtraction operations
Char can be a day,hour,minute,second

1 sql> Select Date ' 2010-2-12 ' + numtodsinterval (1, ' Day ') newdate1,
2 Date ' 2010-2-12 ' + numtodsinterval (1, ' HOUR ' ) Newdate2
3 from dual;