| Using literals of character and number types is pretty simple, because they don't involve any special formatting. However, when using literals of temporal type, you need to pay special attention to the formats in which they are specified. The following sections describe date, timestamp, and interval literals. 6.3.1 DATE Literals DATE literals are specified in the format specified by SQL Standard, and take the following form: DATE 'YYYY-MM-DD'
Unlike Oracle's DATE data type, a DATE literal doesn't specify any time information. You also can't specify a format. If you want to specify a date literal, you must always use the YYYY-MM-DD date format. The following example illustrates the use of a DATE literal in a SQL statement: INSERT INTO employee (emp_id, fname, lname, dept_id, manager_emp_id, salary, hire_date) VALUES (2304, 'John', 'Smith', 20, 1258, 20000, DATE '1999-10-22'); 1 row created. SELECT * FROM employee; EMP_ID FNAME LNAME DEPT_ID MANAGER_EMP_ID SALARY HIRE_DATE ------- -------- ------- ---------- -------------- ---------- --------- 2304 John Smith 20 1258 20000 22-OCT-99
In this example, the date literal DATE '1999-10-22' is interpreted as 22-OCT-99. ISO Standard for Date and Time Notation The International Standard ISO 8601 specifies date and time notation. The date is specified by four-digit year, two-digit month, and two-digit day of the month: YYYY-MM-DD
The time of the day is specified by two-digit hour, two-digit minute, and two-digit second: hh:mi:ss
For detailed information on ISO Standard 8601, refer to the following documents on the Internet: http://www.iso.ch/iso/en/prods-services/popstds/datesandtime.html http://www.cl.cam.ac.uk/~mgk25/iso-time.html http://www.saqqara.demon.co.uk/datefmt.htm http://www.mcs.vuw.ac.nz/technical/software/SGML/doc/iso8601/ISO8601.html
|
6.3.2 TIMESTAMP Literals A TIMESTAMP literal takes the following format: TIMESTAMP 'YYYY-MM-DD HH:MI:SS.xxxxxxxxx'
A TIMESTAMP literal can have up to nine digits of fractional seconds. The fractional part is optional, but the date and time elements are mandatory and must be provided in the given format. Here's an example in which data is inserted into a table with a TIMESTAMP column: INSERT INTO transaction VALUES (1001, TIMESTAMP '1998-12-31 08:23:46.368', 'OPEN'); 1 row created. SELECT * FROM transaction; TRANSACTION_ID TRANSACTION_TIMESTAMP STATUS -------------- --------------------------------- --------- 1001 31-DEC-98 08.23.46.368000 AM OPEN
A TIMESTAMP literal with a time zone displacement can be used to represent a literal of type TIMESTAMP WITH TIME ZONE. It takes the following form: TIMESTAMP 'YYYY-MM-DD HH:MI:SS.xxxxxxxxx {+|-} HH:MI'
Here is an example that shows how to insert data into a table with a TIMESTAMP WITH TIME ZONE column: INSERT INTO transaction_time_zone VALUES (1002, TIMESTAMP '1998-12-31 08:23:46.368 -10:30', 'NEW'); 1 row created. SELECT * FROM transaction_time_zone; TRANSACTION_ID TRANSACTION_TIMESTAMP STATUS -------------- ----------------------------------- ------- 1002 31-DEC-98 08.23.46.368 AM -10:30 NEW
Even though the data type is called TIMESTAMP WITH TIME ZONE, the literal still uses just the TIMESTAMP keyword. However, the literal also specifies a date/time displacement for time zone using the {+|-} HH:MI notation. If you are specifying a time zone displacement in a TIMESTAMP literal, you must specify the sign of the displacement (i.e., + or -). The range of the hour in a time zone displacement is -12 to +13, and the range of a minute is 0 to 59. A displacement outside these ranges will generate an error.  | The valid range of time zone displacement in Oracle differs from that specified by the SQL Standard. The SQL Standard requires the valid range to be from -12:59 to +13:00. However, Oracle enforces the range on the hour (-12 to +13) and minute (0 to 59) components separately. Therefore, the valid range of time zone displacement in Oracle is from -12:00 to +13:59. |
|
When you don't specify a time zone displacement, the displacement is not assumed to be zero; instead, the timestamp is assumed to be in your session time zone, and the value of the displacement defaults to the displacement of that time zone. For example, the TIMESTAMP literal in the following INSERT specifies no time zone displacement, yet the SELECT statement proves that a time zone is, in fact, assumed: INSERT INTO transaction_time_zone VALUES (1003, TIMESTAMP '1999-12-31 08:23:46.368', 'NEW'); 1 row created. SELECT * FROM transaction_time_zone; TRANSACTION_ID TRANSACTION_TIMESTAMP STATUS -------------- ------------------------------------- ------- 1003 31-DEC-99 08.23.46.368 AM -05:00 NEW
There is no literal specifically for the TIMESTAMP WITH LOCAL TIME ZONE data type. To insert data into a column of this type, you use a TIMESTAMP literal with a time zone displacement. For example: INSERT INTO transaction_local_time_zone VALUES (2001, TIMESTAMP '1998-12-31 10:00:00 -3:00', 'NEW'); 1 row created. SELECT * FROM transaction_local_time_zone; TRANSACTION_ID TRANSACTION_TIMESTAMP STATUS -------------- -------------------------- ------- 2001 31-DEC-98 08.00.00 AM NEW
In a case like this, the time zone displacement is not stored in the database. The data is stored in the database in normalized form with respect to the database time zone. By "normalized form" we mean the input time is converted into a time in the database time zone before being stored in the database. The database time zone in this example is -5:00. Therefore, -3:00 is 2 hours ahead of the database time zone, and 10:00:00 - 3:00 is the same as 08:00:00 - 5:00. Since the time is normalized with respect to the database time zone, the displacement doesn't need to be stored in the database. | When TIMESTAMP WITH LOCAL TIME ZONE data is normalized to the database time zone, the time zone of the original data is not preserved. |
|
6.3.3 INTERVAL Literals Just as Oracle supports DATE and TIMESTAMP literals, it supports INTERVAL literals, too. There are two interval data types, and two types of corresponding interval literals: YEAR TO MONTH and DAY TO SECOND. 6.3.3.1 YEAR TO MONTH interval literals A YEAR TO MONTH interval literal represents a time period in terms of years and months. A YEAR TO MONTH interval literal takes one of the following two forms: INTERVAL 'y [-m]' YEAR[(precision)] [TO MONTH] INTERVAL 'm' MONTH[(precision)]
The syntax elements are:
- y
-
An integer value specifying the years.
- m
-
An integer value specifying the months. You must include the TO MONTH keywords if you specify a month value.
- precision
-
Specifies the number of digits to allow for the year or month. The default is 2. The valid range is from 0 to 9.
The default precision for the year value is 2. If the literal represents a time period greater than 99 years, then you must specify a high-enough precision to accommodate the number of years in question. The integer value for the month, as well as the MONTH keyword, are optional. If you specify a month value, it must be between 0 and 11. You do need to use the MONTH keyword when you specify a month value. The following example inserts a YEAR TO MONTH interval literal into an INTERVAL YEAR TO MONTH column: INSERT INTO event_history VALUES (6001, INTERVAL '5-2' YEAR TO MONTH); 1 row created. SELECT * FROM event_history; EVENT_ID EVENT_DURATION ---------- ------------------------------------------ 6001 +05-02
The following example uses a YEAR TO MONTH interval literal to specify a time period of exactly four years. No value for months is included: SELECT INTERVAL '4' YEAR FROM DUAL; INTERVAL'4'YEAR ------------------------------------------- +04-00
A YEAR TO MONTH interval literal can also be used to represent months only: SELECT INTERVAL '3' MONTH FROM DUAL; INTERVAL'3'MONTH ------------------------------------------------- +00-03 SELECT INTERVAL '30' MONTH FROM DUAL; INTERVAL'30'MONTH ------------------------------------------------- +02-06
Notice that when you use a YEAR TO MONTH interval literal to represent only months, you can actually specify a month value larger than 11. In such a situation, Oracle normalizes the value into an appropriate number of years and months. This is the only situation in which the number of months can be greater than 11. 6.3.3.2 DAY TO SECOND interval literals A DAY TO SECOND interval literal represents a time period in terms of days, hours, minutes, seconds, and fractions of seconds. DAY TO SECOND interval literals take on the following form: INTERVAL 'd [h[:m[:s]]]' unit1[(precision1)] TO unit2[(frac_precision)]
The syntax elements are:
- d
-
An integer value specifying the days.
- h
-
An integer value specifying the hours.
- m
-
An integer value specifying the minutes.
- s
-
A number value specifying the seconds and fractional seconds.
- unit1, unit2
-
Can be DAY, HOUR, MINUTE, or SECOND. The leading unit (unit1) must always be greater than the trailing unit (unit2). For example, INTERVAL HOUR TO MINUTE is valid, but INTERVAL HOUR TO DAY is not valid.
- precision1
-
The number of digits to allow for the leading unit. The default is 2. The valid range is from 0 to 9.
- frac_precision
-
The number of digits to allow for fractional seconds. The default is 6. The valid range is from 0 to 9.
By default, two digits are allowed for the number of days. If a literal represents a time period of greater than 99 days, then you must specify a precision high enough to accommodate the number of digits that you need. There's no need to specify the precision for the hour and minute values. The value for hours can be between 0 and 23, and the value for the minutes can be between 0 and 59. While specifying fractional seconds, you can specify a precision for the fractional seconds as well. The precision for the fractional seconds can be between 0 and 9 (default 6), and the seconds value can be between 0 and 59.999999999. The following example inserts a DAY TO SECOND interval literal into a column of data type INTERVAL DAY TO SECOND. The time period being represented is 0 days, 3 hours, 16 minutes, 23.45 seconds. INSERT INTO batch_job_history VALUES (2001, INTERVAL '0 3:16:23.45' DAY TO SECOND); 1 row created. SELECT * FROM batch_job_history; JOB_ID JOB_DURATION ---------- ------------------------------------------------ 2001 +00 03:16:23.450000
The previous example uses all elements of the DAY TO SECOND interval literal. However, you can use fewer elements if that's all you need. The following example shows several valid permutations: SELECT INTERVAL '400' DAY(3) FROM DUAL; INTERVAL'400'DAY(3) ----------------------------------------------------------------- +400 00:00:00 SELECT INTERVAL '11:23' HOUR TO MINUTE FROM DUAL; INTERVAL'11:23'HOURTOMINUTE ----------------------------------------------------------------- +00 11:23:00 SELECT INTERVAL '11:23' MINUTE TO SECOND FROM DUAL; INTERVAL'11:23'MINUTETOSECOND ----------------------------------------------------------------- +00 00:11:23.000000 SELECT INTERVAL '20' MINUTE FROM DUAL; INTERVAL'20'MINUTE ----------------------------------------------------------------- +00 00:20:00
The only requirement is that you must use a range of contiguous elements. You cannot, for example, specify an interval in terms of only hours and seconds, because you can't omit the intervening minutes value. An interval of 4 hours, 36 seconds would need to be expressed as 4 hours, 0 minutes, 36 seconds. | 