Zurück

Date / Time Arithmetic with Oracle 9/10

Overview

If you store date and time information in Oracle, you have two different options for the column's datatype - DATE and TIMESTAMP.

DATE is the datatype that we are all familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype.

In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date:

SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "hiredate"
  FROM emp;

hiredate
-------------------
17.12.1980:00:00:00
20.02.1981:00:00:00

You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example:

  • SYSDATE + 1 is tomorrow
  • SYSDATE - 7 is one week ago
  • SYSDATE + (10/1440) is ten minutes from now.

Subtracting the HIREDATE column of the EMP table from SYSDATE returns the number of days since each employee was hired.

SELECT '03.12.2004:10:34:24' "Now",
       TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
       TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
       - hiredate "Hired since [Days]"
  FROM emp;
Now                 Hiredate            Hired since [Days]
------------------- ------------------- ------------------
03.12.2004:10:34:24 17.12.1980:00:00:00         8752.44056

You cannot multiply or divide DATE values. Oracle provides functions for many common date operations. For example, the ADD_MONTHS function lets you add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates.

Subtraction between Dates

The trouble people get into when using the DATE datatype is doing arithmetic on the column in order to figure out the number of years, weeks, days, hours, and seconds between two dates. What needs to be realized when doing the calculation is that when you do subtraction between dates, you get a number that represents the number of days. You should then multiply that number by the number of seconds in a day (86400) before you continue with calculations to determine the interval with which you are concerned.

DEFINE Today = TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')

SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
       TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
       trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",
       trunc((86400*(&Today-hiredate))/60)-60*(trunc(((86400*(&&Today-hiredate))/60)/60)) "Min",
       trunc(((86400*(&Today-hiredate))/60)/60)-24*(trunc((((86400*(&&Today-hiredate))/60)/60)/24)) "Hrs",
       trunc((((86400*(&Today-hiredate))/60)/60)/24) "Days"
FROM emp;

Hiredate            Today               Sec Min Hrs  Days
------------------- ------------------- --- --- --- -----
17.12.1980:00:00:00 03.12.2004:10:34:24  24  34  10  8752

Check out the above query for a possible solution on how to extract the individual time intervals for a subtraction of two dates. The fractions could be reduced but we wanted to show all the numbers to emphasize the calculation.

If you want a solution which breaks the days in years and month you can use the following query. We will use a leap year date, 01/01/2000 for example, for temporary purposes. This date will provide accurate calculation for most cases.

DEFINE DateDay = 8752.44056

SELECT
    TO_NUMBER(SUBSTR(A,1,4)) - 2000 years,
    TO_NUMBER(SUBSTR(A,6,2)) - 01 months,
    TO_NUMBER(SUBSTR(A,9,2)) - 01 days,
    SUBSTR(A,12,2) hours,
    SUBSTR(A,15,2) minutes,
    SUBSTR(A,18,2) seconds
FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD')
      + &DateDay,'YYYY MM DD HH24:MI:SS') A
      FROM DUAL);

     YEARS     MONTHS       DAYS HO MI SE
---------- ---------- ---------- -- -- --
        23         11         17 10 34 24

The new TIMESTAMP datatype

One of the main problems with the DATE datatype was its' inability to be granular enough to determine which event might have happened first in relation to another event. Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds.

Convert DATE datatype to TIMESTAMP datatype

If you want to convert a DATE datatype to a TIMESTAMP datatype format, just use the CAST function. As you can see, there is a fractional seconds part of '.000000' on the end of this conversion. This is only because when converting from the DATE datatype that does not have the fractional seconds it defaults to zeros and the display is defaulted to the default timestamp format (NLS_TIMESTAMP_FORMAT). If you are moving a DATE datatype column from one table to a TIMESTAMP datatype column of another table, all you need to do is a INSERT SELECT FROM and Oracle will do the conversion for you.

CREATE TABLE date_table (
  date1      DATE,
  time1      TIMESTAMP,
  time2      TIMESTAMP
);

INSERT INTO date_table (date1, time1, time2)
  VALUES (SYSDATE,
          TO_TIMESTAMP ('17.12.1980:00:00:00','DD.MM.YYYY:HH24:MI:SS'),
          TO_TIMESTAMP ('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
  );
COMMIT;

SELECT CAST(date1 AS TIMESTAMP) "Date" FROM date_table;

Date
---------------------------------------------------------------------------
03-DEC-04 11.36.45.000000 AM

The TO_TIMESTAMP function

The TO_TIMESTAMP function converts a string to a timestamp. The syntax for the to_timestamp function is:

TO_TIMESTAMP ( string , [ format_mask ]  [ 'nlsparam' ]  )

string is the string that will be converted to a timestamp.
format_mask is optional.  This is the format that will be used to convert string to a timestamp.

 The following is a list of options for the format_mask parameter  These parameters can be used in many combinations.

Parameter

Explanation

YYYY

4-digit year

MM

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

MONTH

Name of month, padded with blanks to length of 9 characters.

DD

Day of month (1-31).

HH

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

SS

Second (0-59).

 Formatting of the TIMESTAMP datatype

Formatting of the new TIMESTAMP datatype is the same as formatting the DATE datatype. Beware while the TO_CHAR function works with both datatypes, the TRUNC function will not work with a datatype of TIMESTAMP. This is a clear indication that the use of TIMESTAMP datatype should explicitly be used for date and times where a difference in time is of utmost importance, such that Oracle won't even let you compare like values. If you wanted to show the fractional seconds within a TIMESTAMP datatype, look at the 'FF3' to only showing 3 place holders for the fractional seconds.

Formatting of the TIMESTAMP datatype:

SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date"
  FROM date_table;

Date
-------------------
12/17/1980 00:00:00

Formatting of the TIMESTAMP datatype with fractional seconds:

SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date"
  FROM date_table;

Date
-----------------------------
12/17/1980 00:00:00:000

Subtraction of two TIMESTAMP datatypes

Calculating the time difference between two TIMESTAMP datatypes is much easier than the old DATE datatype. Look at what happens when you just do the same substraction as in the above queries:

SELECT SUBSTR(time1,1,30) "Time1",
       SUBSTR(time2,1,30) "Time2",
       SUBSTR((time2-time1),1,30) "Time1 - Time2"
 FROM date_table;

Time1                          Time2                          Time1 - Time2
------------------------------ ------------------------------ ---------------------------
17-DEC-80 12.00.00.000000 AM   03-DEC-04 10.34.24.000000 AM   +000008752 10:34:24.000000

As you can see, the results are much easier to recognize, 8752 days, 10 hours, 34 minutes, and 24 seconds. This means no more worries about how many seconds in a day and all those cumbersome calculations. And therefore the calculations for getting the weeks, days, hours, minutes, and seconds becomes a matter of picking out the number by using the SUBSTR function as can be seen next:

SELECT SUBSTR(time1,1,30) "Time1",
       SUBSTR(time2,1,30) "Time2",
       SUBSTR((time2-time1), INSTR((time2-time1),' ')+7,2) "SS",
       SUBSTR((time2-time1), INSTR((time2-time1),' ')+4,2) "MI",
       SUBSTR((time2-time1), INSTR((time2-time1),' ')+1,2) "HH",
       TRUNC(TO_NUMBER(SUBSTR((time2-time1),1, INSTR(time2-time1,' ')))) "Days"
  FROM date_table;

Time1                          Time2                          SS MI HH       Days
------------------------------ ------------------------------ -- -- -- ----------
17-DEC-80 12.00.00.000000 AM   03-DEC-04 10.34.24.000000 AM   24 34 10       8752

NEXT_DAY and LAST_DAY functions

The NEXT_DAY and LAST_DAY functions can be used to calculate for example «the last Saturday in any given month». You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one.

NEXT_DAY (date, char)

NEXT_DAY returns the date of the first weekday named by char that is later than date. The return type is always DATE, regardless of the datatype of date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.

Example

Return the date of the next Monday after now:

SELECT TO_CHAR(NEXT_DAY(sysdate,'MON'),'DD.MM.YYYY') "Next Monday from now"
  FROM DUAL;
Next Monday
-----------
 06.12.2004

LAST_DAY(date)

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the datatype of date.

Example

The following statement determines how many days are left in the current month:

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Last",
   LAST_DAY(SYSDATE) - SYSDATE "Days Left"
   FROM DUAL;
 
SYSDATE     Last     Days Left
--------- --------- ----------
03-DEC-04 31-DEC-04         28

Get the last date of a month:

SELECT LAST_DAY (TO_DATE ('02','MM')) FROM dual;

  LAST_DAY
---------
29-FEB-04

Return the last Saturday of each month for a given year

You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one.

DEFINE my_month = 12;

SELECT TO_CHAR (
  NEXT_DAY (
     LAST_DAY (TO_DATE (&my_month,'MM' )) - 7,
     TO_CHAR (TO_DATE ('29-01-1927', 'DD-MM-YYYY' ),'DAY')
  ),'DD.MM.YYYY') "Last Saturday in December 2004"
FROM dual;

Last Saturday in December 2004
------------------------------
25.12.2004

Return the last Saturdays for the current year.

SELECT TO_CHAR (
  NEXT_DAY (
    LAST_DAY (
        ADD_MONTHS (TRUNC(SYSDATE,'Y'),ROWNUM-1))-7,
        TO_CHAR (TO_DATE('29-01-1927', 'DD-MM-YYYY'),'DAY')
  ), 'DD.MM.YYYY') "Last Saturdays in 2004"
 FROM ALL_OBJECTS
WHERE ROWNUM <= 12;

Last Saturdays in 2004
----------------------
31.01.2004
28.02.2004
27.03.2004
24.04.2004
29.05.2004
26.06.2004
31.07.2004
28.08.2004
25.09.2004
30.10.2004
27.11.2004
25.12.2004

The "29-01-1927" is just a random date that we knew was a Saturday—any Saturday would do. This is done instead of using "SAT" in the query for international reasons, because in languages other than English, "SAT" isn't Saturday. This query should work in any language out there.