James's Blog

Oracle: PL/SQL interval datatype

Old fashion way:

declare
begin_2000 date := to_date('1-Jan-2000','dd-Mon-yyyy');
begin_2001 date := to_date('1-Jan-2001','dd-Mon-yyyy');
days_in_2000 number;
begin
days_in_2000 := begin_2001 - begin_2000;
dbms_output.put_line(days_in_2000);
end;

To advance a DATE to the next day, you add 1; to advance a DATE by 1 hour, you add 1/24; and so forth.

New interval datatype:

DECLARE
begin_2000 TIMESTAMP := TO_TIMESTAMP('1-Jan-2000','dd-Mon-yyyy');
begin_2001 TIMESTAMP := TO_TIMESTAMP('1-Jan-2001','dd-Mon-yyyy');
days_in_2000 INTERVAL DAY(3) TO SECOND;
BEGIN
days_in_2000 := begin_2001 Ð begin_2000;

END;
/


DECLARE
x TIMESTAMP WITH TIME ZONE;
y INTERVAL YEAR TO MONTH;
BEGIN

x := TIMESTAMP '2002-07-14 21:15:00 -07:00';
y := INTERVAL '0-1' YEAR TO MONTH;
x := x + y;
x := x + INTERVAL '1 00:00:00' DAY TO SECOND;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(
x, 'yyyy-mm-dd hh24:mi:ss tzh:tzm'));
END;
/

posted by James Pei @ 12:25 PM