Oracle Database supports four datetime data types: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE.
Date Literals
You can specify a DATE value as a string literal, or you can convert a character or numeric value to a date value with the TO_DATE function. DATE literals are the only case in which Oracle Database accepts a TO_DATE expression in place of a string literal.
To specify a DATE value as a literal, you must use the Gregorian calendar. You can specify an ANSI literal, as shown in this example:
DATE '1998-12-25'
The ANSI date literal contains no time portion, and must be specified in the format 'YYYY-MM-DD'. Alternatively you can specify an Oracle date value, as in the following example:
TO_DATE('98-DEC-25 17:30','YY-MON-DD HH24:MI')
The default date format for an Oracle DATE value is specified by the initialization parameter NLS_DATE_FORMAT. This example date format includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation.
Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions.
If you specify a date value without a time component, then the default time is midnight (00:00:00 or 12:00:00 for 24-hour and 12-hour clock time, respectively). If you specify a date value without a date, then the default date is the first day of the current month.
Oracle DATE columns always contain both the date and time fields. Therefore, if you query a DATE column, then you must either specify the time field in your query or ensure that the time fields in the DATE column are set to midnight. Otherwise, Oracle may not return the query results you expect. You can use the TRUNC date function to set the time field to midnight, or you can include a greater-than or less-than condition in the query instead of an equality or inequality condition.
Here are some examples that assume a table my_table with a number column row_num and a DATE column datecol:
INSERT INTO my_table VALUES (1, SYSDATE);
INSERT INTO my_table VALUES (2, TRUNC(SYSDATE));
SELECT *
FROM my_table;
ROW_NUM DATECOL
---------- ---------
1 03-OCT-02
2 03-OCT-02
SELECT *
FROM my_table
WHERE datecol > TO_DATE('02-OCT-02', 'DD-MON-YY');
ROW_NUM DATECOL
---------- ---------
1 03-OCT-02
2 03-OCT-02
SELECT *
FROM my_table
WHERE datecol = TO_DATE('03-OCT-02','DD-MON-YY');
ROW_NUM DATECOL
---------- ---------
2 03-OCT-02
If you know that the time fields of your DATE column are set to midnight, then you can query your DATE column as shown in the immediately preceding example, or by using the DATE literal:
SELECT *
FROM my_table
WHERE datecol = DATE '2002-10-03';
ROW_NUM DATECOL
---------- ---------
2 03-OCT-02
However, if the DATE column contains values other than midnight, then you must filter out the time fields in the query to get the correct result. For example:
SELECT *
FROM my_table
WHERE TRUNC(datecol) = DATE '2002-10-03';
ROW_NUM DATECOL
---------- ---------
1 03-OCT-02
2 03-OCT-02
Oracle applies the TRUNC function to each row in the query, so performance is better if you ensure the midnight value of the time fields in your data. To ensure that the time fields are set to midnight, use one of the following methods during inserts and updates:
Use the TO_DATE function to mask out the time fields:
INSERT INTO my_table
VALUES (3, TO_DATE('3-OCT-2002','DD-MON-YYYY'));
Use the DATE literal:
INSERT INTO my_table VALUES (4, '03-OCT-02');
Use the TRUNC function:
INSERT INTO my_table VALUES (5, TRUNC(SYSDATE));
The date function SYSDATE returns the current system date and time. The function CURRENT_DATE returns the current session date. For information on SYSDATE, the TO_* datetime functions, and the default date format, see Datetime Functions.
TIMESTAMP Literals
The TIMESTAMP data type stores year, month, day, hour, minute, and second, and fractional second values. When you specify TIMESTAMP as a literal, the fractional_seconds_precision value can be any number of digits up to 9, as follows:
TIMESTAMP '1997-01-31 09:26:50.124'
TIMESTAMP WITH TIME ZONE Literals
The TIMESTAMP WITH TIME ZONE data type is a variant of TIMESTAMP that includes a time zone region name or time zone offset. When you specify TIMESTAMP WITH TIME ZONE as a literal, the fractional_seconds_precision value can be any number of digits up to 9. For example:
TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the data. For example,
TIMESTAMP '1999-04-15 8:00:00 -8:00'
is the same as
TIMESTAMP '1999-04-15 11:00:00 -5:00'
8:00 a.m. Pacific Standard Time is the same as 11:00 a.m. Eastern Standard Time.
You can replace the UTC offset with the TZR (time zone region name) format element. For example, the following example has the same value as the preceding example:
TIMESTAMP '1999-04-15 8:00:00 US/Pacific'
To eliminate the ambiguity of boundary cases when the daylight saving time switches, use both the TZR and a corresponding TZD format element. The following example ensures that the preceding example will return a daylight saving time value:
TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'
You can also express the time zone offset using a datetime expression:
SELECT TIMESTAMP '2009-10-29 01:30:00' AT TIME ZONE 'US/Pacific' FROM DUAL;
If you do not add the TZD format element, and the datetime value is ambiguous, then Oracle returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If that parameter is set to FALSE, then Oracle interprets the ambiguous datetime as standard time in the specified region.
TIMESTAMP WITH LOCAL TIME ZONE Literals
The TIMESTAMP WITH LOCAL TIME ZONE data type differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone. The time zone offset is not stored as part of the column data. There is no literal for TIMESTAMP WITH LOCAL TIME ZONE. Rather, you represent values of this data type using any of the other valid datetime literals. The table that follows shows some of the formats you can use to insert a value into a TIMESTAMP WITH LOCAL TIME ZONE column, along with the corresponding value returned by a query.
Table 2-12 TIMESTAMP WITH LOCAL TIME ZONE Literals
Value Specified in INSERT Statement Value Returned by Query'19-FEB-2004'
19-FEB-2004.00.00.000000 AM
SYSTIMESTAMP
19-FEB-04 02.54.36.497659 PM
TO_TIMESTAMP('19-FEB-2004', 'DD-MON-YYYY')
19-FEB-04 12.00.00.000000 AM
SYSDATE
19-FEB-04 02.55.29.000000 PM
TO_DATE('19-FEB-2004', 'DD-MON-YYYY')
19-FEB-04 12.00.00.000000 AM
TIMESTAMP'2004-02-19 8:00:00 US/Pacific'
19-FEB-04 08.00.00.000000 AM
Notice that if the value specified does not include a time component (either explicitly or implicitly), then the value returned defaults to midnight.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4