Database: Part 2 - Date&Time Arithmetic with Oracle 10

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:

1 2	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:

1 2	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:

1 2 3 4

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:

1 2 3 4 5 6 7

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) 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:

1 2 3

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:

1 2 3 4

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:

1	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.

1 2 3 4 5 6 7

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.

1 2 3 4 5

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_OBJECTSWHERE 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.