Oracle Time Type

1, common 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. Date range may be from January 1, 4712 to December 31, 9999 A.D.
The date type is stored in the database in a fixed 7-byte format:
1th byte: Century +100
2nd byte: Year
3rd Byte: Month
4th Byte: Day
5th byte: Hours +1
6th byte: Minute +1
7th byte: Seconds +1

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

Note: The timestamp date type is automatically converted to the date type if it is added to the numeric value, which means that fractional seconds are automatically removed.

1.3. Date and timestamp type internal storage validation

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 perform date arithmetic
Date data can be added and reduced to a new date, plus or minus the number of days
sysdate+1--take tomorrow's current time
sysdate-1/24--Take the first one hours of the current time

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 afternoon 09:55:16

2.3, how to find the time interval of two dates
You can subtract two dates directly, and the returned units are in days, hours, and seconds, and converted into decimals.

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

DATE ' 2012-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 a character to a 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)--formatting dates into characters

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

SQL to_char (date,) Week_dayth, To_char (date,) Month_dayth, To_char (date,) Year_dayth, To_char (date,) WeekdayName, _ (date,) month_weekth, To_char (date,) year_weekth DUAL;

 

week_dayth month_dayth year_dayth weekdayname month_weekth YEAR_WEEKTH
---------- -------------------------------------------------------
6           12          043        friday      2           

3.2, To_date (CHAR,FORMATSTR)--Converts a character to a date
To_date (' 2010-02-24 15:01:54 ', ' yyyy-mm-dd HH24:MI:SS ')
Format notes:
HH indicates 12-hour binary, HH24 means 24-hour input, MM for the month, MI for minutes.

3.3, TRUNC (date)--Returns the date part of date, 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 a part of the date
If DateValue is a date type, then data can be (year, MONTH, day)
If DateValue is a timestamp type, then 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
--------------------------------------------
2010-5-13 pm 11:05:06         2010          5
3.5, Add_months (date,months)--Gets a new date in the date added month

Add_months (sysdate,3)--current date plus 3 months
Add_months is a more interesting function that automatically handles the size of month and leap months , as shown below

Select 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) from dual;

 
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

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 specified week of the next CHAR starting from a given date

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 of the [day: minute: Second] format, which is typically used for date addition and subtraction operations

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 a specified unit in CHAR, typically used for date plus subtraction operations
Char can be a year,month

Date Numtoyminterval (,) newdate1,date numtoyminterval (,) Newdate2 dual;

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

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

Date Numtodsinterval (,) newdate1,date numtodsinterval (,) Newdate2 dual;

NEWDATE1 NEWDATE2
----------- ------------------------
2010-2-13 2010-2-12 01:00:00

Oracle Time Type