Ian Hellström

• • 3 min

Oracle Date Arithmetic Weirdness

Although the date arithmetic in Oracle Database is well documented, it is not always as clear as it could be. In this blog post I want to point out a few common traps with regard to date calculations in Oracle that you should be aware of, especially with regard to intervals.

Alternatives

Oracle offers several options with regard to dates and times:

  • DATE: date and time
  • TIMESTAMP(n): DATE plus fractional second with 1 ≤ n ≤ 9 and a default of 6.
  • TIMESTAMP(n) WITH TIME ZONE: TIMESTAMP(n) with time zone region name or offset.
  • TIMESTAMP(n) WITH LOCAL TIME ZONE: TIMESTAMP(n) with automatically calculated offset.

I do not want to reiterate what Oracle has already written in the documentation. What is important to note though is that you cannot create constraints on columns with the data type TIMESTAMP WITH TIME ZONE; the same is true for BLOB and CLOB, but these types are obviously not dates.

What is more, Oracle has different time zones built in with UTC being the standard one. It is recommended that DBTIMEZONE is set to UTC for performance; the database time zone is only relevant for TIMESTAMP WITH LOCAL TIME ZONE, and having a database time zone different from UTC can cause ‘unnecessary’ time zone conversions. Another recommendation is that time zone information be stored with the time zone region name rather than an offset. Why? If you manually provide an offset, times won’t be adjusted for daylight-saving time.

Date Arithmetic

Most people simply use numbers when adding or subtracting amounts of time to or from dates. Since these numbers represent (fractions or multiples of) days, the interpretation may sometimes be difficult:

SELECT
  TO_DATE('2015-01-01','YYYY-MM-DD') + 0.00486 AS "Add some fraction"
, TO_DATE('2015-01-01','YYYY-MM-DD') + INTERVAL '7' MINUTE AS "Add 7 minutes"
FROM
  dual;

Sure you could have written 1/(24*36*7) instead of the approximation 0.00486 but you’ll often encounter developers who prefer to pre-compute the fractions for Oracle, although Oracle does that for you when it parses a statement. Don’t believe me? Just write a statement with a WHERE clause that has a mathematical expression and check the EXPLAIN PLAN:

EXPLAIN PLAN FOR
SELECT * FROM dual WHERE dummy > EXP(-4.2) + SIN(0.25);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Note that in SQL Developer all you need is the statement itself and your finger on F10, or whatever hotkey you have defined for the execution plan.

The execution plan is as follows, where the interesting information is in the section with the predicate information:

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("DUMMY")>.2623995360750006358088330650781179250134)

Voilà, the expression is fully evaluated, as promised.

The solution with INTERVAL seems so elegant that it seems almost silly not to define a coding standard for date arithmetic.

Before you start patting yourself on the back, hang on an INTERVAL '1' SECOND.

The interval calculations are colossally stupid. What I mean by that is that something like ... + INTERVAL '1' MONTH really does not check the calendar: it adds a 1 to the month irrespective of the day specified. The same is true for YEAR where Oracle does not consider leap years.

Examples 

SELECT
  TO_DATE('2015-03-31','YYYY-MM-DD') - 31 AS feb28
FROM
  dual;

We see that Oracle subtracts the 31 days March has and arrives at 28 February:

FEB28
-------------------
2015-02-28 00:00:00

So far so good.

The same result can be obtained with ADD_MONTHS(TO_DATE('2015-03-31','YYYY-MM-DD'), -1). The problems start when we do the following:

SELECT
  TO_DATE('2015-03-31','YYYY-MM-DD') - INTERVAL '1' MONTH AS error
FROM
  dual;

Oracle returns:

Error report -
SQL Error: ORA-01839: date not valid for month specified
01839.
00000 - "date not valid for month specified"
*Cause:
*Action:

What?! Well, 31 February does not exist and that is what this error is telling us. We can replace the month interval with INTERVAL '31' DAY and it works as before. In fact, everything up to MONTH works as you would expect.

YEAR is also an issue when there is a leap year:

SELECT
  TO_DATE('2016-02-29','YYYY-MM-DD') - INTERVAL '1' YEAR AS error
FROM
  dual;

This query won’t run because it stupidly subtracts the year and arrives at 29 February 2015, which does not exist.

All in all, the ‘fun’ thing about the interval calculations is that most options work as expected except that sometimes MONTH and YEAR mess up the dates, which can cause your applications to throw exceptions that may be difficult to track down.

There are some difficulties associated with leap seconds, since a date requires the seconds to be a number between 0 and 59 (not 60!), but I’ll not bore you with the details.

My Tips

Everything I have said can be summarized by two very simple rules when dealing with date arithmetic:

  1. Stay away from INTERVAL literals and use simple NUMBERs instead.
  2. Write out fractions of days by multiplying by 24 (hours), 24*60 (minutes), or 24*60*60 or 24*3600 (seconds) explicitly.

Oracle automatically evaluates such expression when they appear in predicates, so there is no need to use a calculator to pre-compute the numbers. It’s easier to read and adjust if there ever is a reason to change a value manually.

Related posts