6.5 INTERVAL Literals

6.5 INTERVAL Literals

Just as Oracle supports DATE and TIMESTAMP literals, it supports INTERVAL literals too. There are two interval datatypes, and two types of corresponding interval literals: YEAR TO MONTH interval literals and DAY TO SECOND interval literals.

6.5.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 on the following form:

INTERVAL 'y [- m]' YEAR[(precision_for_year)] [TO MONTH]

The syntax elements are:

y: An integer value specifying the years.
m: An optional integer value specifying the months. You must include the TO MONTH keywords if you specify a month value.
precision_for_year: Specifies the number of digits to allow for the year. 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 we must specify a high-enough precision for the year. 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 also need to use the TO MONTH keywords 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. Note that 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 we use a YEAR TO MONTH interval literal to represent only months, we 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 where the month can be greater than 11.

6.5.2 DAY TO SECOND Interval Literals

A DAY TO SECOND interval literal represents a time period in terms of days, hours, minutes, and seconds. DAY TO SECOND interval literals take on the following form:

INTERVAL 'd [h [:m[:s]]]' DAY[(day_prec)] [TO {HOUR | MINUTE | SECOND[(frac_prec)]}]

The syntax elements are:

d: An integer value specifying the days.
h: An optional integer value specifying the hours.
m: An optional integer value specifying the minutes.
s: An optional number value specifying the seconds and fractional seconds.
day_prec: The number of digits to allow for the days. The default is 2. The valid range is from 0 to 9.
frac_prec: The number of digits to allow for fractional seconds.

By default, two digits are allowed for the number of days. If the literal represents a time period of greater than 99 days, then we must specify a precision high enough to accommodate the number of digits we need. There's no need to specify the precision for the hour and minute values. The value for the hours can be between 0 and 23, and the value for the minutes can be between 0 and 59. If you specify fractional seconds, you need to specify a precision for the fractional seconds as well. The precision for the fractional seconds can be between 1 and 9, 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. For example, the following examples show 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.

6.5.3 Manipulating Timestamps and Intervals

To manipulate values of the new datetime and interval datatypes discussed in this chapter, Oracle9i introduced several new built-in SQL functions. Table 6-4 summarizes these functions.

Table 6-4. New DATETIME and INTERVAL functions in Oracle9i

Function

Description

Return datatype

DBTIMEZONE

Returns the database timezone.

Character

SESSIONTIMEZONE

Returns the session timezone.

Character

SYSTIMESTAMP

Returns the system date and timestamp in the session timezone.

TIMESTAMP WITH TIME ZONE

CURRENT_DATE

Returns the current date in the session timezone.

DATE

CURRENT_TIMESTAMP

Returns the current date and timestamp in the session timezone.

TIMESTAMP WITH TIME ZONE

LOCALTIMESTAMP

Returns the current date and timestamp in the session timezone.

TIMESTAMP

TO_TIMESTAMP

Converts character string into TIMESTAMP.

TIMESTAMP

TO_TIMESTAMP_TZ

Converts character string into TIMESTAMP WITH TIME ZONE.

TIMESTAMP WITH TIME ZONE

FROM_TZ

Converts TIMPSTAMP into TIMESTAMP WITH TIME ZONE.

TIMESTAMP WITH TIME ZONE

NUMTOYMINTERVAL

Converts number into INTERVAL YEAR TO MONTH.

INTERVAL YEAR TO MONTH

NUMTODSINTERVAL

Converts number into INTERVAL DAY TO SECOND.

INTERVAL DAY TO SECOND

TO_YMINTERVAL

Converts character string into INTERVAL YEAR TO MONTH.

TIMESTAMP WITH TIME ZONE

TO_DSINTERVAL

Converts character string into INTERVAL DAY TO SECOND.

INTERVAL DAY TO SECOND

TZ_OFFSET

Returns the time zone offset with respect to UTC.

Character

The time zone is returned as a displacement with respect to UTC, and is displayed with a + or - sign together with an hours:minutes value. These functions are discussed with examples in the following sections.

6.5.3.1 DBTIMEZONE

The DBTIMEZONE function returns the value of the database time zone. We can use this function as we use SYSDATE:

SELECT DBTIMEZONE FROM  DUAL;

DBTIME
------
-07:00

6.5.3.2 SESSIONTIMEZONE

The SESSIONTIMEZONE function returns the value of the session time zone. We can use this function as we use SYSDATE:

SELECT SESSIONTIMEZONE FROM  DUAL;

SESSIONTIMEZONE
----------------
-06:00

6.5.3.3 SYSTIMESTAMP

The SYSTIMESTAMP function returns the value of the system date and time, including the fractional parts of a second and the time zone. This is the same as SYSDATE, but with additional information about fractional seconds and the time zone.

SELECT SYSTIMESTAMP FROM  DUAL;

SYSTIMESTAMP
------------------------------------
11-NOV-01 01.00.10.040438 AM -05:00

SYSTIMESTAMP returns a value in the TIMESTAMP WITH TIMEZONE datatype, and the precision of fractional seconds is always 6.

6.5.3.4 CURRENT_DATE

The CURRENT_DATE function returns the current date and time in the session time zone. The difference between SYSDATE and CURRENT_DATE is that while SYSDATE is based on the DBTIMEZONE, CURRENT_DATE is based on the SESSIONTIMEZONE.

SELECT SYSDATE, CURRENT_DATE FROM DUAL;

SYSDATE                 CURRENT_DATE
----------------------- -----------------------
11-NOV-2001 01:15:40 AM 11-NOV-2001 12:15:41 AM

Note that the CURRENT_DATE is behind the SYSDATE by one hour in this example. This is because the session time zone is one hour behind the database time zone.

6.5.3.5 CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function returns the current date, the time, the fractional parts of a second, and a time zone displacement. The value returned will be in the session time zone. Note that the difference between SYSTIMESTAMP and CURRENT_TIMESTAMP is that while SYSTIMESTAMP is based on the DBTIMEZONE, CURRENT_TIMESTAMP is based on the SESSIONTIMEZONE.

The function header of CURRENT_TIMESTAMP is:

CURRENT_TIMESTAMP [(precision)]

The precision argument specifies the precision of the fractional seconds, and is optional. The default precision is 6. The return value is of datatype TIMESTAMP WITH TIME ZONE.

SELECT CURRENT_TIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP
-----------------------------------------
11-NOV-01 01.42.40.099518 PM -06:00

6.5.3.6 LOCALTIMESTAMP

The LOCALTIMESTAMP function returns the current date, time, and the fractional parts of a second in the session time zone. The function header of LOCALTIMESTAMP is:

LOCALTIMESTAMP [(precision)]

The precision argument specifies the precision of the fractional seconds, and is optional. The default value is 6. The return value is of datatype TIMESTAMP.

SELECT LOCALTIMESTAMP FROM DUAL;

LOCALTIMESTAMP
--------------------------------------
11-NOV-01 01.42.55.852724 PM

Note that the only difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP is the return type. LOCALTIMESTAMP returns a TIMESTAMP, whereas CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE.

6.5.3.7 TO_TIMESTAMP

The TO_TIMESTAMP function is similar to the TO_DATE function. It converts a character string into a TIMESTAMP. The input to the TO_TIMESTAMP function can be a literal, a PL/SQL variable, or a database column of CHAR or VARCHAR2 datatype.

The TIMESTAMP keyword can also be used to generate a TIMESTAMP value, but the keyword can only be used with a literal value. TO_TIMESTAMP can operate on PL/SQL variables and database column values.

The function header of TO_TIMESTAMP function is:

TO_TIMESTAMP (string [,format])

The syntax elements are:

string: Specifies a character string or a numeric value that is convertible to a TIMESTAMP. The string or numeric value can be a literal, a value in a PL/SQL variable, or a value in a database column.
format: Specifies the format of the input string.

The format is optional. When the format is not specified, the input string is assumed to be in the default timestamp format. The default timestamp format is the default date format plus time in the format HH.MI.SS.xxxxxxxxx, where xxxxxxxxx represents fractional seconds. The following example converts a string in the default timestamp format into a timestamp:

SELECT TO_TIMESTAMP('11-NOV-01 10.32.22.765488123') FROM DUAL;

TO_TIMESTAMP('11-NOV-0110.32.22.765488123')
--------------------------------------------------------------------
11-NOV-01 10.32.22.765488123 AM

The following example specifies the format as the second input parameter to the TO_TIMESTAMP function:

SELECT TO_TIMESTAMP('12/10/01','MM/DD/YY') FROM DUAL;

TO_TIMESTAMP('12/10/01','MM/DD/YY')
------------------------------------------------------------
10-DEC-01 12.00.00 AM

Notice in this second example that since the time portion wasn't provided in the input string, the time is assumed to be the beginning of the day, i.e., 12:00:00 A.M.

6.5.3.8 TO_TIMESTAMP_TZ

The TO_TIMESTAMP_TZ function is similar to the TO_TIMESTAMP function. The only difference is the return datatype. The return type of TO_TIMESTAMP_TZ is TIMESTAMP WITH TIME ZONE. The input to the TO_TIMESTAMP_TZ function can be a literal, a PL/SQL variable, or a database column of CHAR or VARCHAR2 datatype.

The function header of TO_TIMESTAMP_TZ function is:

TO_TIMESTAMP_TZ (string [,format])

The syntax elements are:

string: Specifies a character string or a numeric value that is convertible to a TIMESTAMP WITH TIME ZONE. The string or numeric value can be a literal, a value in a PL/SQL variable, or a value in a database column.
format: Specifies the format of the input string.

The format is optional. When the format is not specified, the input string is assumed to be in the default format of the TIMESTAMP WITH TIME ZONE datatype. The following example converts a string in the default format into a TIMESTAMP WITH TIME ZONE:

SELECT TO_TIMESTAMP_TZ('11-NOV-01 10.32.22.765488123 AM -06:00') FROM DUAL;

TO_TIMESTAMP_TZ('11-NOV-0110.32.22.765488123')
----------------------------------------------------------------------
11-NOV-01 10.32.22.765488123 AM -06:00

The following example specifies the format as the second input parameter to the TO_TIMESTAMP_TZ function:

SELECT TO_TIMESTAMP_TZ('12/10/01','MM/DD/YY') FROM DUAL;

TO_TIMESTAMP_TZ('12/10/01','MM/DD/YY')
-------------------------------------------------------------
10-DEC-01 12.00.00.000000000 AM -06:00

Note that since the time portion wasn't provided in the input string, the time is assumed to be the beginning of the day, i.e., 12:00:00 A.M.

The TO_TIMESTAMP_TZ function doesn't convert the input string into a TIMESTAMP WITH LOCAL TIME ZONE datatype. Oracle doesn't provide any function for this purpose. To convert a value to TIMESTAMP WITH LOCAL TIME ZONE, we must use the CAST function, as in the following examples:

SELECT CAST('10-DEC-01' AS TIMESTAMP WITH LOCAL TIME ZONE) FROM DUAL;

CAST('10-DEC-01'ASTIMESTAMPWITHLOCALTIMEZONE)
---------------------------------------------------------------------------
10-DEC-01 12.00.00 AM

SELECT CAST(TO_TIMESTAMP_TZ('12/10/01','MM/DD/YY') 
            AS TIMESTAMP WITH LOCAL TIME ZONE)
FROM DUAL;

CAST(TO_TIMESTAMP_TZ('12/10/01','MM/DD/YY')ASTIMESTAMPWITHLOCALTIMEZONE)
---------------------------------------------------------------------------
10-DEC-01 12.00.00 AM

In the first example, the input string is in the default date format. Therefore, no date format is required for conversion. However, in the second example the input string is in a different format than the default; therefore, we must use a conversion function along with a format to convert the string into a value (e.g., TIMESTAMP WITH TIME ZONE) that can then be cast to a TIMESTAMP WITH LOCAL TIME ZONE. We can use either TO_DATE, TO_TIMESTAMP, or TO_TIMESTAMP_TZ, depending upon our input data.

The CAST function used in these examples is not a SQL function in the truest sense. CAST is actually a SQL expression like DECODE and CASE. The CAST expression converts a value in one datatype to a value in another datatype. In the first example, the CAST expression converts a CHAR literal into a value in the TIMESTAMP WITH LOCAL TIME ZONE datatype. In the second example, the CAST expression converts a value in the TIMESTAMP WITH TIME ZONE datatype into a value in the TIMESTAMP WITH LOCAL TIME ZONE datatype.

6.5.3.9 FROM_TZ

The FROM_TZ function takes separate TIMESTAMP and time zone values as input, and converts the inputs into a TIMESTAMP WITH TIME ZONE. The function header of the FROM_TZ function is:

FROM_TZ (timestamp, time_zone)

The syntax elements are:

timestamp: Specifies a literal string, a PL/SQL variable, or a database column. The input must contain a timestamp value.
time_zone: Specifies a string containing a time zone in the format [+|-]hh:mi.

The following example illustrates conversion of a timestamp and a time zone into a TIMESTAMP WITH TIME ZONE value:

SELECT FROM_TZ(TIMESTAMP '2001-12-10 08:30:00', '-5:00') FROM DUAL;

FROM_TZ(TIMESTAMP'2001-12-1008:30:00','-5:00')
-------------------------------------------------------------------------
10-DEC-01 08.30.00.000000000 AM -05:00

6.5.3.10 NUMTOYMINTERVAL

The NUMTOYMINTERVAL (NUM-TO-YM-INTERVAL) function converts a number input into an INTERVAL YEAR TO MONTH literal. The function header of NUMTOYMINTERVAL function is:

NUMTOYMINTERVAL (n, unit)

The syntax elements are:

n: Specifies a numeric literal or an expression convertible to a number.
unit: Specifies a character string containing the unit of n, and can be either 'YEAR' or 'MONTH'. This is case-insensitive.

The following example inserts a row into a table with a column of type INTERVAL YEAR TO MONTH. The NUMTOYMINTERVAL is used to convert a number into type INTERVAL YEAR TO MONTH.

INSERT INTO EVENT_HISTORY VALUES (5001, NUMTOYMINTERVAL(2,'YEAR'));

6.5.3.11 NUMTODSINTERVAL

The NUMTODSINTERVAL (NUM-TO-DS-INTERVAL) function converts a number input into an INTERVAL DAY TO SECOND literal. The function header of NUMTODSINTERVAL function is:

NUMTODSINTERVAL (n, unit)

The syntax elements are:

n: Specifies a numeric literal or an expression convertible to a number.
unit: Specifies a character string containing the unit of n, and can be either 'DAY', 'HOUR', 'MINUTE' or 'SECOND'. This is case-insensitive.

The following example inserts a row into a table with a column of type INTERVAL DAY TO SECOND. The NUMTODSINTERVAL is used to convert a number into type INTERVAL DAY TO SECOND.

INSERT INTO BATCH_JOB_HISTORY VALUES
(6001, NUMTODSINTERVAL(5369.2589,'SECOND'));

6.5.3.12 TO_YMINTERVAL

The TO_YMINTERVAL function is very similar to the TO_DATE function. It converts a character string into an INTERVAL YEAR TO MONTH. The input to the TO_YMINTERVAL function can be a literal, a PL/SQL variable, or a database column of CHAR or VARCHAR2 datatype.

The function header of TO_YMINTERVAL function is:

TO_YMINTERVAL (string)

The syntax element is:

string: Specifies a literal string, a PL/SQL variable, or a database column. The input string must contain character or numeric data convertible to an INTERVAL YEAR TO MONTH value. The input string must be in Y-M format, i.e., the year and month values must be separated by a dash ( - ). All components (year, month and -) must be present in the string.

The following example inserts a row into a table with a column of type INTERVAL YEAR TO MONTH. The TO_YMINTERVAL is used to convert a string into a type INTERVAL YEAR TO MONTH value.

INSERT INTO EVENT_HISTORY VALUES (5001, TO_YMINTERVAL('02-04'));

In this example, the string '02-04' represents an interval of 2 years and 4 months.

6.5.3.13 TO_DSINTERVAL

The TO_DSINTERVAL function is similar to the TO_DATE function. It converts a character string into an INTERVAL DAY TO SECOND. The input to the TO_DSINTERVAL function can be a literal, a PL/SQL variable, or a database column of CHAR or VARCHAR2 datatype.

The function header of TO_DSINTERVAL function is:

TO_DSINTERVAL (string)

The syntax element is:

string: Specifies a literal string, a PL/SQL variable, or a database column containing character numeric data convertible to an INTERVAL DAY TO SECOND value. The input string must be in D HH:MI:SS format. The day value of the interval is separated by a space from the time value, which is expressed in hours, minutes, and seconds, and is delimited by ":". All components must be present in the string in order for it to be converted to an INTERVAL DAY TO SECOND value.

The following example inserts a row into a table with a column of type INTERVAL DAY TO SECOND. The TO_DSINTERVAL is used to convert a string into type INTERVAL DAY TO SECOND.

INSERT INTO BATCH_JOB_HISTORY VALUES (6001, TO_DSINTERVAL('0 2:30:43'));

In this example, the string '0 2:30:43' represents an interval of 0 days, 2 hours, 30 minutes, and 43 seconds.

6.5.3.14 TZ_OFFSET

The TZ_OFFSET function returns the time zone offset of its input. The function header of TZ_OFFSET function is:

TZ_OFFSET (time_zone_name | time_zone_offset | DBTIMEZONE | SESSIONTIMEZONE)

The syntax elements are:

time_zone_name: Specifies a string containing a time zone name. A time zone name is given to all the time zones in the world, and we can query the V$TIMEZONE_NAMES dynamic view for a list of valid time zone names.
time_zone_offset: Specifies a string containing a time zone offset. A time zone offset takes the form of "{+ | -} hh:mi", i.e., hours and minutes preceded by a + or - sign.
DBTIMEZONE: DBTIMEZONE is a build-in function that returns the time zone of the database.
SESSIONTIMEZONE: SESSIONTIMEZONE is a build-in function that returns the time zone of the session.

The following example illustrates the use of the TZ_OFFSET function:

SELECT TZ_OFFSET('US/Pacific'), TZ_OFFSET('EST'), TZ_OFFSET('+6:30') FROM DUAL;

TZ_OFFS TZ_OFFS TZ_OFFS
------- ------- -------
-08:00  -05:00  +06:30

Note that time zone names such as 'US/Eastern' and 'US/Pacific' can be used as well as standard abbreviations such as 'EST', 'PST', and so on. The following example illustrates the use of DBTIMEZONE and SESSIONTIMEZONE with the TZ_OFFSET function:

SELECT TZ_OFFSET(DBTIMEZONE), TZ_OFFSET(SESSIONTIMEZONE) FROM DUAL;

TZ_OFFS TZ_OFFS
------- -------
-07:00  -06:00