Oracle Database - Datatype Datetime

> Database > Oracle Database

1 - About

Oracle Database supports the following datetime data types:

  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
  • TIMESTAMP WITH LOCAL TIME ZONE

3 - Date and Time Datatype

3.1 - DATE Data Type

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.

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;
Advertising

3.2 - TIMESTAMP Data Type

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.

4 - TIMESTAMP WITH TIME ZONE Data Type

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.

4.1 - TIMESTAMP WITH LOCAL TIME ZONE Data Type

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.

5 - Representing the Difference (interval) between Datetime Values

5.1 - INTERVAL DAY TO SECOND data type

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.

5.2 - INTERVAL YEAR TO MONTH data type

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.

Advertising

6 - Input and output of DATE and TIME

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.

7 - How to change the default date format

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.

8 - Julian dates

Oracle Database Concepts for information about Julian dates. Oracle Database Julian dates might not be compatible with Julian dates generated by other date algorithms.

9 - Documentation / Reference

  • Oracle Database SQL Language Reference for information on DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types
  • SQL Developer Help
  • Oracle Database provides various functions for calculating and converting datetime data. For examples in SQL statements, see Using Date Functions.
Advertising
db/oracle/datatype_datetime.txt · Last modified: 2017/09/06 19:27 by gerardnico