10.4.2 The Time Zone Functions

Sometimes you need to know what time zone your session or your database is in. You might also want to determine the offset from UTC for a given time zone. Use the following three functions to do these things:

DBTIMEZONE RETURN VARCHAR2

SESSIONTIMEZONE RETURN VARCHAR2

TZ_OFFSET (time_zone) RETURN VARCHAR2

DBTIMEZONE and SESSIONTIMEZONE return the database and session time zones, respectively. The time zones are identified by their offsets from UTC. For example:

BEGIN

DBMS_OUTPUT.PUT_LINE(DBTIMEZONE);

DBMS_OUTPUT.PUT_LINE(SESSIONTIMEZONE);

END;

The output is:

-07:00

-08:00

But what time zones do these offsets really represent? Recall that the V$TIMEZONE_NAMES view returns a list of time zone names and abbreviations. You can use the TZ_OFFSET function to get the current time zone offset for a given time zone region. The following query returns a list of all the time zone regions that currently correspond to an offset of -07:00. The query was executed when daylight savings time was not in effect; if it were in effect, you'd get different results. For example, America/Denver is UTC -07:00 during most of the year, but UTC -06:00 when daylight savings time is in effect.

SELECT TZNAME, TZABBREV

FROM V$TIMEZONE_NAMES

WHERE SUBSTR(TZ_OFFSET(TZNAME),1,6) = '-07:00';

TZNAME TZABBREV

---------------------------------------- ----------

America/Denver LMT

America/Denver MST

America/Denver MWT

America/Denver MDT

America/Edmonton LMT

America/Edmonton MST

America/Edmonton MDT

Why the use of the SUBSTR function in this example? In Oracle9i Release 1, there appears to be a bug with the TZ_OFFSET function in that it returns an extra zero byte at the end of the string. For example:

SELECT TZ_OFFSET('US/EASTERN') offset,

LENGTH(TZ_OFFSET('US/EASTERN')) length,

DUMP(TZ_OFFSET('US/EASTERN')) dump

FROM dual;

OFFSET LENGTH DUMP

------- ---------- ----------------------------------

-05:00 7 Typ=1 Len=7: 45,48,53,58,48,48,0

Note the zero byte at the end of the DUMP, and consider what happens when the following SELECT is issued:

SELECT TZNAME

FROM V$TIMEZONE_NAMES

WHERE TZ_OFFSET(TZNAME) = '-07:00';

*

ERROR at line 3:

ORA-01875: time zone minute must be between -59 and 59

At worst, this SELECT should return no rows, and in our testing on Windows XP this is indeed the result we got. Under Linux, however, we received the rather strange error you see here. Use SUBSTR to remove the extraneous zero, and you should be OK.

10.4.3 The add_months Function

The ADD_MONTHS function returns a new date with the specified number of months added to the input date. The specification for ADD_MONTHS is as follows:

FUNCTION ADD_MONTHS (date_in IN DATE, month_shift NUMBER) RETURN DATE

FUNCTION ADD_MONTHS (month_shift NUMBER, date_in IN DATE) RETURN DATE

ADD_MONTHS is an overloaded function. You can specify the date first and then the number of months by which you want to shift that date, or you can list the month_shift parameter first and then the date. Either order is acceptable, and both arguments are required.

If the month_shift parameter is positive, ADD_MONTHS returns a date for that number of months into the future. If the number is negative, ADD_MONTHS returns a date for that number of months in the past. Here are some examples that use ADD_MONTHS:

• Move ahead by 3 months:

ADD_MONTHS ('12-JAN-1995', 3) --> 12-APR-1995

• Move back by 12 months:

ADD_MONTHS (-12, '12-MAR-1990') --> 12-MAR-1989

ADD_MONTHS always shifts the date by whole months. You can provide a fractional value for the month_shift parameter, but ADD_MONTHS will round down to the next whole number nearest zero, as shown in the following example. Pay special attention to the direction in which a negative value of months is rounded.

BEGIN

--Adding 1.9999 is the same as adding 1

DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('28-FEB-1989', 1.9999));

DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('28-FEB-1989', 1));

DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('28-FEB-1989', -1.9999));

DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('28-FEB-1989', -1));

END;

The output is:

31-MAR-1989

31-MAR-1989

31-JAN-1989

31-JAN-1989

If you want to shift a date by a fraction of a month, simply add or subtract the required number of days. PL/SQL supports direct arithmetic operations between date values. For more information, see Section 10.3 earlier in this chapter.

If the input date to ADD_MONTHS does not fall on the last day of the month, the date returned by ADD_MONTHS falls on the same day in the new month as in the original month. If the day number of the input date is greater than the last day of the month returned by ADD_MONTHS, the function sets the day number to the last day in the new month. The following example illustrates both of these rules. The first call to ADD_MONTHS advances 15-Jan one month to 15-Feb. Because there is no 31st day in February, the second call returns the last day in February:

BEGIN

DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('15-JAN-1995', 1));

DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('31-JAN-1995', 1));

END;

The output is:

15-FEB-1995

28-FEB-1995

This behavior is perfectly reasonable. However, what if the input date falls on the last day of the month, and the new month has more days than the original month? If you shift two months forward from 28-FEB-1994, do you get back 30-APR-1994 (the last day of April) or 28-APR-1994 (the same day in the new month as in the old month)? The answer is that if the input date represents the last day of the month, so will the output date:

BEGIN

DBMS_OUTPUT.PUT_LINE(ADD_MONTHS ('28-FEB-1994', 2));

END;

30-APR-1994

If you pass to ADD_MONTHS a day representing the last day of the month, PL/ SQL always returns the last day of the resulting month, regardless of the number of actual days in each of the months. This behavior makes sense in some situations, but not in others. Be aware of it! (See the upcoming sidebar for a function that can help you get around this quirk.)

Getting Around ADD_MONTH's Month-End Quirk The fact that ADD_MONTHS always returns a month-end date whenever the input date represents a month-end date is sometimes inconvenient. The following PL/SQL function, my_add_months, will preserve the original day of the month whenever possible: FUNCTION my_add_months ( date_in IN DATE, months_shift IN NUMBER) RETURN DATE IS date_out DATE; day_in NUMBER; day_out NUMBER; BEGIN date_out := ADD_MONTHS(date_in, months_shift); day_in := TO_NUMBER(TO_CHAR(date_in,'DD')); day_out := TO_NUMBER(TO_CHAR(date_out,'DD')); IF day_out > day_in THEN date_out := date_out - (day_out - day_in); END IF; RETURN date_out; END; The only time this function adjusts the day of the month is when the day happens to be beyond the end of the target month. Add 1 month to 31-Jan-2002, and because February has only 28 days you'll get 28-Feb-2002 as the result. However, add 1 to 28-Feb-2002, and you'll get 28-Mar-2002—the day is preserved whenever possible. This function also preserves any time-of-day components such as hours, minutes, and seconds.