Oracle - Date Datatype

1 - About

Use the DATE data type to store point-in-time values (dates and times) in a table. An application that specifies the time for a job might use the DATE data type.

The DATE data type stores:

  • the century,
  • year,
  • month,
  • day,
  • hours,
  • minutes,
  • and seconds.

The valid date range is from January 1, 4712 BC to December 31, 9999 AD.

Use the TIMESTAMP data type to store values that are precise to fractional seconds. An application that must decide which of two events occurred first might use TIMESTAMP.

3 - Formatting

The formatting of a date is controlled by the following parameters :

Snippet with the TO_CHAR function to give a specific format:


TO_CHAR(COLUMN_WITH_DATE_DATATYPE, 'YYYY/MM/DD HH24:MI:SS'),

4 - Date

4.1 - Substraction


select
  (End_Date - Start_Date)*24*60*60 diff_in_secondes
from
  your_table

4.2 - Addition


select TO_DATE('20141021 15:34:10','YYYYMMDD HH24:MI:SS')+ 
+(1)          *9 -- Day
+(1/24)       *9 -- Hour
+(1/24/60)    *9 -- Minutes
+(1/24/60/60) *9 -- Seconden
from dual;

5 - Documentation / Reference


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap