Storing Date and Time Data |
 Previous |
 Next |
Oracle Database XE stores dates in its own internal format that corresponds to century, year, month, day, hour, minute, and second. For input and output of dates, the standard Oracle Database XE default date format is DD-MON-RR. The RR date-time 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.
Oracle Database Express Edition provides various SQL functions to calculate and convert date-time data. For examples, see "Using Date Functions" and "Using Conversion Functions". For more information about manipulating date formats on a global level, see Working in a Global Environment.
This section contains the following topic:
|
See Also:
|
Using DATE and TIMESTAMP Datatypes
Oracle Database supports the following date and time (date-time) datatypes:
-
DATE -
TIMESTAMP -
TIMESTAMPWITHTIMEZONE -
TIMESTAMPWITHLOCALTIMEZONE
Table: DATE and TIMESTAMP Examples shows examples of DATE and TIMESTAMP datatypes.
DATE and TIMESTAMP Examples
| Datatype | Example |
|---|---|
|
|
09-DEC-05 |
|
|
09-DEC-05 02.05.49.000000 PM |
|
|
09-DEC-05 02.05.49.000000 PM -08:00 |
|
|
09-DEC-05 02.05.49.000000 PM |
|
See Also: Oracle Database SQL Reference for information aboutDATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE datatypes
|
Using the DATE Datatype
Use the DATE datatype to store point-in-time values (dates and times) in a table. For example, a column to hold the date that an employee is hired can by defined as a DATE datatype. An application that specifies the time for a job might also use the DATE datatype. For most cases where you need to store date data, you would use the DATE datatype.
DATE columns are automatically formatted by Oracle Database XE to include a date and time component. The DATE datatype 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. Although both the date and time are stored in a date column, by default, the date portion is automatically displayed for you, when retrieving date data. However, Oracle Database Express Edition enables you great flexibility in how you can display your dates and times. See "Using Date Functions".
Using the TIMESTAMP Datatype
Use the TIMESTAMP datatype to store values that are precise to fractional seconds. An application that must decide which of two events occurred first might use TIMESTAMP.
Using the TIMESTAMP WITH TIME ZONE Datatype
Because the TIMESTAMP WITH TIME ZONE datatype can also store time zone information, it is particularly suited for recording date information that must be gathered or coordinated across geographic regions.
Using the TIMESTAMP WITH LOCAL TIME ZONE Datatype
Use the TIMESTAMP WITH LOCAL TIME ZONE datatype 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 datatype 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.