Adding and subtracting numeric values

Integervalues represent days when doing date arithmetic. To move a date one day into the future, simply add 1 to the date as shown:

hire_date + 1

You can even add a fractional value to a date. Afractional value represents less than one day. You'll find fractional values most useful when you work in multiples of the values shown in Table 10-4.

Table 10-4. Fractional values in date arithmetic
Value Represents
1/24 One hour
1/1440 One minute
1/86400 One second

In the following example, 8/24 is added to hire_date to change the time component from midnight to the more reasonable 8:00 AM:

DECLARE start_time DATE := DATE '2000-09-01';BEGIN DBMS_OUTPUT.PUT_LINE(TO_CHAR(start_time,'YYYY-MM-DD HH:MI AM')); DBMS_OUTPUT.PUT_LINE(TO_CHAR(start_time+8/24,'YYYY-MM-DD HH:MI AM'));END;

The output is:

2000-09-01 12:00 AM2000-09-01 08:00 AM

Note that we specified 8/24 for 8 hours, and not 1/3 or the decimal equivalent of 0.33333333. By using 8/24, we make it reasonably clear to other programmers that we are adding 8 hours to the date. Always use one of the denominators shown in Table 10-4 when adding or subtracting numbers to a DATE. Once you memorize those denominators, the implications of any date arithmetic that uses them becomes very easy to understand. Following are some examples:

· To add 12 hours to a date, use 12/24 rather than 1/2.

· To add 1 day and 12 hours to a date value, use either 1+12/24 or 36/24, but never 1.5.

· You can specify one minute as 60 seconds (60/86400) or 1 minute (1/1440), depending on whether your application drives you to think in terms of minutes or seconds.

Avoid using denominators other than those shown in Table 10-4. Quick! How much time does 1/48 represent? Hmmm . . . had to think about that, didn't you? If we'd asked about 30/1440, you would have known immediately that we were talking about 30 minutes. For the record, 30/1440 reduces to 1/48 and represents 30 minutes. Use 1/48 for your math homework, but use 30/1440 for clarity in your code.

Adding or subtracting numbers to or from any of the TIMESTAMP types causes an implicit conversion to DATE.

 

 

Add or subtract numeric values as shown in this section only when using DATE variables. The following example demonstrates that a TIMESTAMP WITH TIME ZONE is implicitly converted to type DATE when a numeric value is added to it:

DECLARE start_time TIMESTAMP WITH TIME ZONE := TIMESTAMP '2000-09-01 00:00:00.00 -7:00';BEGIN DBMS_OUTPUT.PUT_LINE(start_time); DBMS_OUTPUT.PUT_LINE(start_time+8/24); DBMS_OUTPUT.PUT_LINE(TO_CHAR(start_time+8/24,'DD-MON-YY HH.MI.SS'));END;

The output is:

01-SEP-00 12.00.00.000000 AM -07:0001-SEP-0001-SEP-00 08.00.00

Ignore the time zone for a moment, and you can see that in each case 8 hours have been added to the time of day. Note that the second line of output is formatted as a DATE value, not a timestamp—that's evidence right there of the implicit conversion. The third line of output shows that the time of day was preserved during the implicit cast, but rest assured that the time zone and any fractional seconds have not been preserved.


php"; ?>