MERCADOS FINANCIEROS

Track all markets on TradingView

jueves, 2 de abril de 2020

Oracle Date Timestamps

Oracle Dates, Timestamps and Intervals


Introduction

The way the Oracle database handles datetime values is pretty straightforward, but it seems to confuse many client-side and PL/SQL developers alike. The vast majority of problems people encounter are because of a misunderstanding about how dates are stored in the database. What you see on screen from a query is what's in the database right? Well actually, that is often not the case.
Client tools, like SQL*Plus, convert datetime column values into something much nicer to look at. In the process, they often miss out very important information that can confuse you if you are not careful. The following examples use the DATE type, but the issues apply equally to the TIMESTAMP type.
CREATE TABLE t1 (
  col1 DATE,
  col2 DATE
);

INSERT INTO t1 VALUES (TRUNC(SYSDATE), SYSDATE);
COMMIT;

SELECT * FROM t1;

COL1      COL2
--------- ---------
27-APR-13 27-APR-13

1 row selected.

SQL>
So both columns contain the same value right?
SELECT COUNT(*)
FROM   t1
WHERE  col1 = col2;

  COUNT(*)
----------
         0

1 row selected.

SQL>
Wrong!
Both DATE and TIMESTAMP columns contain a time component, which does not match in this case. SQL*Plus has converted the internal representation of the date into a nice string for us, but it has left out the time component. Why has it done this? Because it has used the format mask specified by the NLS_DATE_FORMAT parameter to decide how to implicitly convert the date to a string. You can display the current database, instance and session NLS parameter values using this script. To get the full data we have to either explicitly ask for it using the TO_CHAR function with a format mask.
SELECT TO_CHAR(col1, 'DD-MON-YYYY HH24:MI:SS') AS col1,
       TO_CHAR(col2, 'DD-MON-YYYY HH24:MI:SS') AS col2
FROM   t1;

COL1                 COL2
-------------------- --------------------
27-APR-2013 00:00:00 27-APR-2013 11:20:00

1 row selected.

SQL>
Or set the NLS_DATE_FORMAT to the desired format mask.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT * FROM t1;

COL1                 COL2
-------------------- --------------------
27-APR-2013 00:00:00 27-APR-2013 11:20:00

1 row selected.

SQL>
Another common mistake is when you specify a date as a string.
SELECT COUNT(*)
FROM   t1
WHERE  TRUNC(col1) = '27/04/2013';
              *
ERROR at line 3:ISO
ORA-01843: not a valid month

SQL>
That string looks perfectly acceptable to me, because I understand the variations in date formats and that looks like a UK representation of "27th April 2013" to me, but the database doesn't know that. To remedy this, we must either explicitly use the TO_DATE function with a format mask, set the NLS_DATE_FORMAT appropriately, or use an ANSI DATE literal.
-- Explicit using TO_DATE
SELECT COUNT(*)
FROM   t1
WHERE  TRUNC(col1) = TO_DATE('27/04/2013','DD/MM/YYYY');

  COUNT(*)
----------
  1

SQL> 

-- Implicit using NLS_DATE_FORMAT
ALTER SESSION SET nls_date_format='DD/MM/YYYY';

SELECT COUNT(*)
FROM   t1
WHERE  TRUNC(col1) = '27/04/2013';

  COUNT(*)
----------
  1

SQL>

-- Explicit using ANSI DATE literal 
SELECT COUNT(*)
FROM   t1
WHERE  TRUNC(col1) = DATE '2013-04-27';

  COUNT(*)
----------
  1

SQL>
When using Oracle DATE or TIMESTAMP values, remember the following simple rules and you will probably avoid most of the common pitfalls.
  • Both DATE and TIMESTAMP types *always* contain a date and time component. At exactly midnight the time is 00:00:00.
  • Never rely on implicit conversions of strings to dates, or dates to strings. Always explicitly perform the conversions with the TO_CHAR, TO_DATE and TO_TIMESTAMP functions, or use ASNI DATE or TIMESTAMP literals.
  • When doing date or timestamp comparisons, always consider the impact of the time component. If you want to discount the time component from the comparison, use the TRUNC or ROUND functions to remove it from both sides of the comparison.
The remainder of this article will discuss the DATE, TIMESTAMP and INTERVAL types in more detail.

DATE

The DATE datatype is used by Oracle to store all datetime information where a precision greater than 1 second is not needed. Oracle uses a 7 byte binary date format which allows Julian dates to be stored within the range of 01-Jan-4712 BC to 31-Dec-9999 AD. The following table shows how each of the 7 bytes is used to store the date information.
ByteMeaningNotationExample (10-JUL-2004 17:21:30)
1CenturyDivided by 100, excess-100120
2YearModulo 100, excess-100104
3Month0 base7
4Day0 base10
5Hourexcess-118
6Minuteexcess-122
7Secondexcess-131
The following example uses the dump function to show the contents of a stored date.
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

DROP TABLE date_test;

CREATE TABLE date_test AS
SELECT SYSDATE AS now
FROM   dual;

SELECT now,
       dump(now)
FROM   date_test;

NOW                  DUMP(NOW)
-------------------- -----------------------------------------
10-JUL-2004 17:21:30 Typ=12 Len=7: 120,104,7,10,18,22,31

1 row selected.
Comparing the date and dump values we see that subtracting 100 from the century component then multiplying the resulting value by 100 gives a value of 2000. Subtracting the 100 from the year component gives a value of 4. The month and day components need no modification, while subtracting 1 from the hour, minute and second components (18, 22 and 31) give values of 17, 21 and 30.
Since dates are actually numbers, certain simple mathematical operations to can be performed on them. Adding a whole number to a date is like adding the equivalent number of days, while adding a fraction to a date is like adding that fraction of a day to the date. The same is true in reverse for subtraction. The following table shows how each specific time periods can be calculated. All three expressions equate to the same value, so pick the one you prefer.
PeriodExpression 1Expression 2Expression 3Value
1 Day1111
1 Hour1/241/241/24.041666667
1 Minute1/24/601/(24*60)1/1440.000694444
1 Second1/24/60/601/(24*60*60)1/86400.000011574
The following query shows how we might use these expressions to modify the value of the current operating system date.
ALTER SESSION SET nls_date_format='DD/MM/YYYY HH24:MI:SS';

SELECT SYSDATE AS current_date,
       SYSDATE + 1 AS plus_1_day,
       SYSDATE + 2/24 AS plus_2_hours,
       SYSDATE + 10/24/60 AS plus_10_minutes,
       SYSDATE + 30/24/60/60 AS plus_30_seconds
FROM   dual;

CURRENT_DATE        PLUS_1_DAY          PLUS_2_HOURS        PLUS_10_MINUTES     PLUS_30_SECONDS
------------------- ------------------- ------------------- ------------------- -------------------
10/07/2004 17:57:30 11/07/2004 17:57:30 10/07/2004 19:57:30 10/07/2004 18:07:30 10/07/2004 17:58:00

1 row selected.

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
Oracle provides several date functions to make date manipulation simpler. The following table lists a selection of them and examples of their usage.
Date FunctionUsage
SYSDATE
Returns the current date-time from the operating system of the database server.
SELECT SYSDATE 
FROM   dual;

SYSDATE
-------------------
10/07/2004 18:34:12

1 row selected.
CURRENT_DATE
Similar to the sysdate function, but returns the current date-time within the sessions time zone.
SELECT CURRENT_DATE 
FROM   dual;

CURRENT_DATE
-------------------
10/07/2004 18:36:24

1 row selected.
ADD_MONTHS(date, months)
Adds or subtracts the specified number of months from the specified date.
SELECT SYSDATE, 
       ADD_MONTHS(SYSDATE, 2) 
FROM   dual;

SYSDATE             ADD_MONTHS(SYSDATE,
------------------- -------------------
10/07/2004 18:40:46 10/09/2004 18:40:46

1 row selected.
LAST_DAY(date)
Returns the last day of the month that contains the specified date.
SELECT SYSDATE, 
       LAST_DAY(SYSDATE) 
FROM   dual;

SYSDATE             LAST_DAY(SYSDATE)
------------------- -------------------
10/07/2004 18:42:14 31/07/2004 18:42:14

1 row selected.
MONTHS_BETWEEN(date, date)
Returns the number of months between two dates. If the first date is prior to the second, the result is negative, otherwise it is positive. If both dates are on the same day of the month, or both the last day of the month the returned value is an integer, otherwise the return value includes a fraction of the month difference.
SELECT MONTHS_BETWEEN(SYSDATE, SYSDATE+30) 
FROM   dual;

MONTHS_BETWEEN(SYSDATE,SYSDATE+30)
----------------------------------
                                -1

1 row selected.
NEXT_DAY(date, day)
Returns the date of the first day that matches the specified day that occurs after the specified date.
SELECT SYSDATE, 
       NEXT_DAY(SYSDATE, 'MONDAY') 
FROM   dual;

SYSDATE             NEXT_DAY(SYSDATE,'M
------------------- -------------------
10/07/2004 18:43:44 12/07/2004 18:43:44

1 row selected.
NEW_TIME(date, timezone1, timezone2)
Converts a date from timezone1 into the appropriate date for timeszone2.
SELECT SYSDATE, 
       NEW_TIME(SYSDATE, 'GMT', 'EST') 
FROM   dual;

SYSDATE             NEW_TIME(SYSDATE,'G
------------------- -------------------
10/07/2004 18:46:12 10/07/2004 13:46:12

1 row selected.
TO_CHAR(date, format)
Converts a specified date to a string using the specified format mask. If the format mask is omitted the NLS_DATE_FORMAT value is used. There is also an overload of this function to deal with timestamps where the default format mask is take from the NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT value.
SELECT TO_CHAR(SYSDATE, 'DD/MM/YY HH24:MI') 
FROM   dual;

TO_CHAR(SYSDAT
--------------
10/07/04 18:48

1 row selected.
TO_DATE(date_string, format)
Converts a specified string to a date using the specified format mask. If the format mask is omitted the NLS_DATE_FORMAT value is used.
SELECT TO_DATE('10/07/2004 13:31:45', 'DD/MM/YYYY HH24:MI:SS') 
FROM   dual;

TO_DATE('10/07/2004
-------------------
10/07/2004 13:31:45

1 row selected.
ROUND(date, format)
Returns a date rounded to the level specified by the format. The default value for the format is DD, returning the date without the fractional (time) component, making it represent midnight on the specified date, or the following date depending on the rounding.
SELECT SYSDATE, 
       ROUND(SYSDATE, 'HH24') 
FROM   dual;

SYSDATE             ROUND(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:54:24 10/07/2004 19:00:00

1 row selected.
TRUNC(date, format)
Returns a date truncated to the level specified by the format. The default value for the format is DD, truncating the fractional (time) component, making it represent midnight on the specified date. Using the TRUNC function allows comparison of dates without the time components distracting from the true meaning of the comparison. It is similar to the round function, except that it always rounds down.
SELECT SYSDATE, 
       TRUNC(SYSDATE, 'HH24') 
FROM   dual;

SYSDATE             TRUNC(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:55:44 10/07/2004 18:00:00

1 row selected.
The ROUND and TRUNC functions can be especially useful, so we will discuss their format models in more detail. The table below lists some of the available format models, their meanings and examples of their usage. The dates have been adjusted where necessary to show the difference between the return values of the functions.
Format ModelRounding or Truncating Unit
CC
SCC
To the first year of the century (1901, 2001, 2101 etc.)
SELECT SYSDATE,
       TRUNC(SYSDATE, 'CC'),
       ROUND(SYSDATE, 'CC')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'CC')  ROUND(SYSDATE,'CC')
-------------------- -------------------- --------------------
16-JAN-1999 08:48:09 01-JAN-1901 00:00:00 01-JAN-2001 00:00:00

1 row selected.
SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y
To the year. Rounds up on January 1st.
SELECT SYSDATE,
       TRUNC(SYSDATE, 'YY'),
       ROUND(SYSDATE, 'YY')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'YY')  ROUND(SYSDATE,'YY')
-------------------- -------------------- --------------------
08-JUL-2004 08:08:49 01-JAN-2004 00:00:00 01-JAN-2005 00:00:00

1 row selected.
IYYY
IYY
IY
I
To the ISO Year.
SELECT SYSDATE,
       TRUNC(SYSDATE, 'IY'),
       ROUND(SYSDATE, 'IY')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'IY')  ROUND(SYSDATE,'IY')
-------------------- -------------------- --------------------
08-JUL-2004 08:10:39 29-DEC-2003 00:00:00 03-JAN-2005 00:00:00

1 row selected.
Q
To the quarter, rounding up on the 16th day of the second month.
SELECT SYSDATE,
       TRUNC(SYSDATE, 'Q'),
       ROUND(SYSDATE, 'Q')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'Q')   ROUND(SYSDATE,'Q')
-------------------- -------------------- -------------------
22-AUG-2004 08:23:56 01-JUL-2004 00:00:00 01-OCT-2004 00:00:00

1 row selected.
MONTH
MON
MM
RM
To the month, rounding up on the 16th day.
SELECT SYSDATE,
       TRUNC(SYSDATE, 'MM'),
       ROUND(SYSDATE, 'MM')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'MM')  ROUND(SYSDATE,'MM')
-------------------- -------------------- --------------------
16-JUL-2004 08:15:31 01-JUL-2004 00:00:00 01-AUG-2004 00:00:00

1 row selected.
WW
To the same day of the week as the first day of the year.
SELECT SYSDATE,
       TRUNC(SYSDATE, 'WW'),
       ROUND(SYSDATE, 'WW')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'WW')  ROUND(SYSDATE,'WW')
-------------------- -------------------- --------------------
12-JUL-2004 08:20:28 08-JUL-2004 00:00:00 15-JUL-2004 00:00:00

1 row selected.
IW
To the same day of the week as the first day of the ISO year.
SELECT SYSDATE,
       TRUNC(SYSDATE, 'IW'),
       ROUND(SYSDATE, 'IW')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'IW')  ROUND(SYSDATE,'IW')
-------------------- -------------------- --------------------
16-JUL-2004 08:26:02 12-JUL-2004 00:00:00 19-JUL-2004 00:00:00

1 row selected.
W
To the same day of the week as the first day of the month.
SELECT SYSDATE,
       TRUNC(SYSDATE, 'W'),
       ROUND(SYSDATE, 'W')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'W')   ROUND(SYSDATE,'W')
-------------------- -------------------- --------------------
13-JUL-2004 08:28:10 08-JUL-2004 00:00:00 15-JUL-2004 00:00:00

1 row selected.
DDD
DD
J
To the day.
SELECT SYSDATE,
       TRUNC(SYSDATE, 'DD'),
       ROUND(SYSDATE, 'DD')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'DD')  ROUND(SYSDATE,'DD')
-------------------- -------------------- --------------------
08-JUL-2004 20:34:24 08-JUL-2004 00:00:00 09-JUL-2004 00:00:00

1 row selected.
DAY
DY
D
To the starting day of the week.
SELECT SYSDATE,
       TRUNC(SYSDATE, 'D'),
       ROUND(SYSDATE, 'D')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'D')   ROUND(SYSDATE,'D')
-------------------- -------------------- --------------------
09-JUL-2004 08:33:01 04-JUL-2004 00:00:00 11-JUL-2004 00:00:00

1 row selected.
HH
HH12
HH24
To the hour.
SELECT SYSDATE,
       TRUNC(SYSDATE, 'HH'),
       ROUND(SYSDATE, 'HH')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'HH')   ROUND(SYSDATE,'HH')
-------------------- -------------------- --------------------
08-JUL-2004 08:36:22 08-JUL-2004 08:00:00 08-JUL-2004 09:00:00

1 row selected.
MI
To the minute.
SELECT SYSDATE,
       TRUNC(SYSD'MI'),
       ROUND(SYSDAT
  'MI')
FROM   dual;

SYSDATE              TRUNC(SYSDATE,'HH')   ROUND(SYSDATE,'HH')
-------------------- -------------------- --------------------
08-JUL-2004 08:37:32 08-JUL-2004 08:37:00 08-JUL-2004 08:38:00

1 row selected.
Next we will discuss the TIMESTAMP datatype, which has many similarities with the DATE datatype.

TIMESTAMP

The TIMESTAMP datatype is an extension on the DATE datatype. In addition to the datetime elements of the DATE datatype, the TIMESTAMP datatype holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6. There are also two variants called TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. As their names imply, these timestamps also store time zone offset information.
Like dates, timestamps are stored using a binary date format. In the case of a TIMESTAMP this is 11 bytes long, while those with timezone information require 13 bytes. The following table shows how each of the 11-13 bytes is used to store the timestamp information.
ByteMeaningNotationExample (10-JUL-2004 17:21:30.662509 +01:00)
1CenturyDivided by 100, excess-100120
2YearModulo 100, excess-100104
3Month0 base7
4Day0 base10
5Hourexcess-1 (-offset)17
6Minuteexcess-122
7Secondexcess-131
8Fraction of a second9 digit integer stored in 4 bytes39,125,21,200
9
10
11
12Timezone Hourexcess-2021
13Timezone Minexcess-6060
The following example uses the dump function to show the contents of a stored timestamp.
ALTER SESSION SET nls_timestamp_tz_format = 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM';

DROP TABLE timestamp_test;

CREATE TABLE timestamp_test AS
SELECT SYSTIMESTAMP AS now
FROM   dual;

SELECT now,
       DUMP(now)
FROM   timestamp_test;

NOW                                                      DUMP(NOW)
-------------------------------------------------------- --------------------------------------------------------
31-JUL-04 11.15.05.662509 +01:00                         Typ=181 Len=13: 120,104,7,31,11,16,6,39,125,21,200,21,60

1 row selected.
The first 7 components match those of the DATE datatype, although they can look confusing due to the action of the offset. In this example the offset of +01:00 makes the hour component appear to be in 0 base notation rather than excess-1, but when we add the offset we can clearly see it is not. The offset component represents the number of minutes the time is offset due to the timezone.
The mathematical operations and most of the date functions mentioned previously are also valid for timestamps. In addition to the date functions Oracle provides several timestamp specific functions listed in the table below.
Timestamp FunctionUsage
SYSTIMESTAMP(precision)
Returns the current TIMESTAMP from the operating system of the database server to the specified precision. If no precision is specified the default is 6.
SELECT SYSTIMESTAMP(3) 
FROM   dual;

SYSTIMESTAMP(3)
-----------------------------
10-JUL-04 19.09.35.793 +01:00

1 row selected.
CURRENT_TIMESTAMP(precision)
Similar to the SYSTIMESTAMP function, but returns the current TIMESTAMP WITH TIME ZONE within the sessions time zone to the specified precision. If no precision is specified the default is 6.
SELECT CURRENT_TIMESTAMP(3) 
FROM   dual;

CURRENT_TIMESTAMP(3)
-----------------------------
10-JUL-04 19.11.12.686 +01:00

1 row selected.
LOCALTIMESTAMP(precision)
Similar to the current_timestamp function, but returns the current TIMESTAMP with time zone within the sessions time zone to the specified precision. If no precision is specified the default is 6.
SELECT LOCALTIMESTAMP(3) 
FROM   dual;

LOCALTIMESTAMP(3)
----------------------
10-JUL-04 19.12.21.859

1 row selected.
TO_TIMESTAMP(string, format)
Converts a specified string to a TIMESTAMP using the specified format mask. If the format mask is omitted the NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT value is used depending on the context.
SELECT TO_TIMESTAMP('10/07/2004', 'DD/MM/YYYY') 
FROM   dual;

TO_TIMESTAMP('10/07/2004','DD/MM/YYYY')
---------------------------------------
10-JUL-04 00.00.00.000000000

1 row selected.
TO_TIMESTAMP_TZ(string, format)
Converts a specified string to a TIMESTAMP WITH TIME ZONE using the specified format mask. If the format mask is omitted the NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT value is used depending on the context.
SELECT TO_TIMESTAMP_TZ('10/07/2004', 'DD/MM/YYYY') 
FROM   dual;

TO_TIMESTAMP_TZ('10/07/2004','DD/MM/YYYY')
------------------------------------------
10-JUL-04 00.00.00.000000000 +01:00

1 row selected.
FROM_TZ(timestamp, timezone)
Converts a TIMESTAMP and a string representing the time zone to a TIMESTAMP WITH TIME ZONE.
SELECT FROM_TZ(LOCALTIMESTAMP, '3:00') 
FROM   dual;

FROM_TZ(LOCALTIMESTAMP,'3:00')
--------------------------------
10-JUL-04 19.19.07.385684 +03:00

1 row selected.
DBTIMEZONE
Returns the database time zone.
SELECT DBTIMEZONE  
FROM   dual;

DBTIME
------
+00:00

1 row selected.
SESSIONTIMEZONE
Returns the current sessions time zone.
SELECT SESSIONTIMEZONE  
FROM ual;

SESSIONTIMEZONE
---------------
+01:00

1 row selected.
SYS_EXTRACT_UTC(timestamp)
Returns the UTC, or GMT timestamp from a specified TIMESTAMP WITH TIME ZONE.
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP)  
FROM dual;

SYS_EXTRACT_UTC(SYSTIMESTAMP)
-----------------------------
10-JUL-04 18.23.09.393478

1 row selected.
EXTRACT(datepart FROM date)
Extracts the specified datepart from the specified timestamp.
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) 
FROM dual;

EXTRACT(HOURFROMSYSTIMESTAMP)
-----------------------------
                           18

1 row selected.
Next we will see how to convert between timestamps and dates.

Converting Between Timestamps and Dates

The CAST function can be used to convert a TIMESTAMP to a DATE and vice versa. First let's convert a TIMESTAMP to a DATE.
SELECT CAST(SYSTIMESTAMP AS DATE) ts_to_date
FROM   dual;

TS_TO_DAT
---------
08-SEP-13

SQL>
To convert a DATE to a TIMESTAMP do the following.
SELECT CAST(SYSDATE AS TIMESTAMP) date_to_ts
FROM   dual;

DATE_TO_TS
---------------------------------------------------------------------------
08-SEP-13 09.21.45.000000 AM

SQL>
Next we will see how intervals can be stored in the database and defined using the interval literal syntax.

INTERVAL

Intervals provide a way of storing a specific period of time that separates two datetime values. There are currently two supported types of interval, one specifying intervals in years and months, the other specifying intervals in days, hours, minutes and seconds. The syntax of these datatypes is shown below.
INTERVAL YEAR [(year_precision)] TO MONTH
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
The precision elements are defined as follows.
  • year_precision – The maximum number of digits in the year component of the interval, such that a precision of 3 limits the interval to a maximum of 999 years. The default value is 2.
  • day_precision – The maximum number of digits in the day component of the interval, such that a precision of 4 limits the interval to a maximum of 9999 days. The day precision can accept a value from 0 to 9, with the default value being 2.
  • fraction_second_precision – The number of digits in the fractional component of the interval. Values between 0 and 9 are allowed, with the default value being 6.
The following table is created to show how intervals can be used as column definitions.
CREATE TABLE test_interval_table (
  id             NUMBER(10),
  time_period_1  INTERVAL YEAR TO MONTH,
  time_period_2  INTERVAL DAY TO SECOND,
  time_period_3  INTERVAL YEAR (3) TO MONTH,
  time_period_4  INTERVAL DAY (4) TO SECOND (9)
);

SQL> DESC test_interval_table
 Name                             Null?    Type
 -------------------------------- -------- ------------------------------------
 ID                                        NUMBER(10)
 TIME_PERIOD_1                             INTERVAL YEAR(2) TO MONTH
 TIME_PERIOD_2                             INTERVAL DAY(2) TO SECOND(6)
 TIME_PERIOD_3                             INTERVAL YEAR(3) TO MONTH
 TIME_PERIOD_4                             INTERVAL DAY(4) TO SECOND(9)
Interval literals are used to define intervals in an easy to understand manner. There are two separate syntax definitions, one for each type of interval. The full syntax definitions can be a little confusing so we will skip those in favor of examples that should make their usage clear.
First we will start with the YEAR TO MONTH interval literal syntax. The default precision for the fields is listed below, along with the allowable values if specified as a trailing field.
  • YEAR - Number of years with a default precision of 2 digits.
  • MONTH - Number of months with a default precision of 4 digits. If specified as a trailing field it has allowable values of 0 to 11.
Interval LiteralMeaning
INTERVAL '21-2' YEAR TO MONTHAn interval of 21 years and 2 months.
INTERVAL '100-5' YEAR(3) TO MONTHAn interval of 100 years and 5 months. The leading precision is specified, as it is greater than the default of 2.
INTERVAL '1' YEARAn interval of 1 year.
INTERVAL '20' MONTHAn interval of 20 months.
INTERVAL '100' YEAR(3)An interval of 100 years. The precision must be specified as this value is beyond the default precision.
INTERVAL '10000' MONTH(5)An interval of 10,000 months. The precision must be specified as this value is beyond the default precision.
INTERVAL '1-13' YEAR TO MONTHError produced. When the leading field is YEAR the allowable values for MONTH are 0 to 11.
These intervals can be tested by substituting them into the following query. Notice how month syntax is converted into a years and months value.
SELECT INTERVAL '20' MONTH
FROM   dual;

INTERVAL'20'MONTH
---------------------------------------------------------------------------
+01-08

1 row selected.
A YEAR TO MONTH interval can be added to, or subtracted from, another with the result being another YEAR TO MONTH interval.
SELECT INTERVAL '1' YEAR – INTERVAL '1' MONTH
FROM dual;

INTERVAL'1'YEAR-INTERVAL'1'MONTH
---------------------------------------------------------------------------
+000000000-11

1 row selected.
The following examples relate to the DAY TO SECOND interval literal syntax. As with the previous example, if a trailing field is specified it must be less significant than the previous field.
  • DAY - Number of days with a default precision of 2 digits.
  • HOUR - Number of hours with a default precision of 3 digits. If specified as a trailing field it has allowable values of 0 to 23.
  • MINUTE - Number of minutes with a default precision of 5 digits. If specified as a trailing field it has allowable values of 0 to 59.
  • SECOND - Number of seconds with a default precision of 7 digits before the decimal point and 6 digits after. If specified as a trailing field is has allowable values of 0 to 59.999999999.
Interval LiteralMeaning
INTERVAL '2 3:04:11.333' DAY TO SECOND(3)2 days, 3 hours, 4 minutes, 11 seconds and 333 thousandths of a second.
INTERVAL '2 3:04' DAY TO MINUTE2 days, 3 hours, 4 minutes.
INTERVAL '2 3' DAY TO HOUR2 days, 3 hours.
INTERVAL '2' DAY2 days.
INTERVAL '03:04:11.333' HOUR TO SECOND3 hours, 4 minutes, 11 seconds and 333 thousandths of a second.
INTERVAL '03:04' HOUR TO MINUTE3 hours, 4 minutes.
INTERVAL '40' HOUR40 hours.
INTERVAL '04:11.333' MINUTE TO SECOND4 minutes, 11 seconds and 333 thousandths of a second.
INTERVAL '70' MINUTE70 minutes.
INTERVAL '70' SECOND70 seconds.
INTERVAL '03:70' HOUR TO MINUTEError produced. When the leading field is specified the allowable values for the trailing field must be within normal range.
Substituting the above intervals into the following query will allow you to test them. Notice how the default precision for seconds is used because we have not limited it to 3 decimal places.
SELECT INTERVAL '2 3:04:11.333' DAY TO SECOND
FROM   dual;

INTERVAL'23:04:11.333'DAYTOSECOND
---------------------------------------------------------------------------
+02 03:04:11.333000

1 row selected.
A DAY TO SECOND interval can be added to, or subtracted from, another with the result being another DAY TO SECOND interval.
SELECT INTERVAL '1' DAY – INTERVAL '1' SECOND
FROM dual;

INTERVAL'1'DAY-INTERVAL'1'SECOND
---------------------------------------------------------------------------
+000000000 23:59:59.000000000

1 row selected.
Intervals can also be combined with dates to manipulate date values. The following query shows how.
SELECT SYSDATE,
       SYSDATE + INTERVAL '1' MONTH + INTERVAL '1' DAY – INTERVAL '3' SECOND
FROM   dual;

SYSDATE              SYSDATE+INTERVAL'1'M
-------------------- --------------------
10-JUL-2004 19:55:53 11-AUG-2004 19:55:50

1 row selected.
Oracle provides several interval specific functions, which are listed in the table below.

Interval FunctionUsage
NUMTOYMINTERVAL(integer, unit)
Converts the specified integer to a YEAR TO MONTH interval where the integer represents the number of units.
SELECT NUMTOYMINTERVAL(2, 'MONTH') 
FROM   dual;

NUMTOYMINTERVAL(2,'MONTH')
--------------------------
+000000000-02

1 row selected.
NUMTODSINTERVAL(integer, unit)
Converts the specified integer to DAY TO SECOND interval where the integer represents the number of units.
SELECT NUMTODSINTERVAL(2, 'HOUR') 
FROM   dual;

NUMTODSINTERVAL(2,'HOUR')
-----------------------------
+000000000 02:00:00.000000000

1 row selected.
TO_YMINTERVAL(interval_string)
Converts a string representing an interval into a YEAR TO MONTH interval.
SELECT TO_YMINTERVAL('3-10') 
FROM   dual;

TO_YMINTERVAL('3-10')
---------------------
+000000003-10

1 row selected.
TO_DSINTERVAL(interval_string)
Converts a string representing an interval into a DAY TO SECOND interval.
SELECT TO_DSINTERVAL('2 10:3:45.123') 
FROM   dual;

TO_DSINTERVAL('210:3:45.123')
-----------------------------
+000000002 10:03:45.123000000

1 row selected.
EXTRACT(datepart FROM interval)
Extracts the specified datepart from the specified interval.
SELECT EXTRACT(HOUR FROM NUMTODSINTERVAL(2, 'HOUR')) 
FROM   dual;

EXTRACT(HOURFROMNUMTODSINTERVAL(2,'HOUR'))
------------------------------------------
                                         2

1 row selected.
Publicado por en

No hay comentarios:

Publicar un comentario

Suscribirse a: Enviar comentarios (Atom)