Oracle Database supports the following datetime data types:
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 valid date range is from January 1, 4712 BC to December 31, 9999 AD.
Add | Expression |
---|---|
an hour | sysdate + 1/24 |
a minute | sysdate + 1/(24*60) |
a second | sysdate + 1/(24*60*60) |
3 seconds | sysdate + 3/(24*60*60) |
Difference between two dates in second:
SELECT
(to_date('12-10-2011 08:03:27') - to_date('12-10-2011 00:00:00'))*24*60*60 difference_in_second
FROM
dual;
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.
Because TIMESTAMP WITH TIME ZONE can also store time zone information, it is particularly suited for recording date information that must be gathered or coordinated across geographic regions.
Use TIMESTAMP WITH LOCAL TIME ZONE when the time zone is not significant. For example, you might use it in an application that schedules teleconferences, where participants each see the start and end times for their own time zone.
The TIMESTAMP WITH LOCAL TIME ZONE type is appropriate for two-tier applications in which you want to display dates and times that use the time zone of the client system.
It is generally inappropriate in three-tier applications because data displayed in a Web browser is formatted according to the time zone of the Web server, not the time zone of the browser. The Web server is the database client, so its local time is used.
Use the INTERVAL DAY TO SECOND data type to represent the precise difference between two datetime values. For example, you might use this value to set a reminder for a time 36 hours in the future or to record the time between the start and end of a race. To represent long spans of time with high precision, you can use a large value for the days portion.
Use the INTERVAL YEAR TO MONTH data type to represent the difference between two datetime values, where the only significant portions are the year and the month. For example, you might use this value to set a reminder for a date 18 months in the future, or check whether 6 months have elapsed since a particular date.
Oracle Database stores dates in its own internal format which is fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.
For input and output of dates, the standard Oracle Database default date format is DD-MON-RR. The RR datetime format element enables you store 20th century dates in the 21st century by specifying only the last two digits of the year.
Time is stored in a 24-hour format as HH24:MI:SS. By default, the time in a DATE column is 12:00:00 A.M. (midnight) if no time portion is entered or if the DATE is truncated. In a time-only entry, the date portion defaults to the first day of the current month.
You can change the current default date or time format for a specific date or timestamp with the use the TO_DATE or TO_TIMESTAMP function with a format mask, such as:
TO_DATE('27-OCT-98', 'DD-MON-RR')
TO_DATE('15-NOV-05 10:56 A.M.','DD-MON-YY HH:MI A.M.')
TO_TIMESTAMP ('10-Sep-05 14:10:10.123000',
'DD-Mon-RR HH24:MI:SS.FF')
Be careful when using a date format such as DD-MON-YY. The YY indicates the year in the current century. For example, 31-DEC-92 is December 31, 2092, not 1992 as you might expect. If you want to indicate years in any century other than the current one, use a format mask such as the default RR.
You can use the following techniques to change the default date format on a more global level:
To change on an instance-wide basis, use the NLS_DATE_FORMAT parameter.
To change during a session, use the ALTER SESSION statement.
Oracle Database Concepts for information about Julian dates. Oracle Database Julian dates might not be compatible with Julian dates generated by other date algorithms.