add_months() to timestamps in Oracle

Oracle has two classes of date/time types, "date" and "timestamp". The "date" type is very mature and has a variety of useful manipulation functions. Some of these functions are lacking in the more precise "timestamp" data types.

Today I'm going to provide one way of implementing the "date" type workhourse "add_months" for timestamps.

Why would you need this? Well, in my case, I'm writing a global application where users can request data for the next N months. All dates and times are implemented as timestamps with local time zone to present dates/times in a user's timezone. This is done so the user doesn't have to try and translate times from halfway around the world.

My problem is that Oracle's add_months() will work on a timestamp, but it doesn't reliably save and return the timezone data. I want to be sure timezone data is preserved, so I wrote my own add_months_tz().

My function performs the following basic steps:

• Extract seconds (and fractions of a second) and time zone data from incoming timestamp
• Alter the session time zone to match the incoming timestamp's time zone
• Use Oracle's built-in add_months (which returns a date)
• Use conversion functions to convert the date into a timestamp using the saved seconds
• Restore the original session time zone
• Return the newly built timestamp

In testing  I've noticed that sometimes (and with no visible consistency) I got the following error:

ORA-01862: the numeric value does not match the length of the format item

To prevent this from breaking my application, I wrapped the timestamp creation logic in a block and used the exception handler to create the timestamp without the fractions of a second.

Now that you have the theory and reasoning, here is the code:

create or replace function add_months_tz ( p_timestamp in timestamp with time zone, p_added_months in number) return timestamp with time zone is
--Created by Matthew Cox
--Please feel free to copy, use or alter as you see fit
--I'd appreciate it if you give me credit in your application comments
v_location_i number := 100; o_secs number; o_region varchar2(128); t_date date; r_timestamp timestamp with local time zone; v_session_timezone varchar2(128); begin --save session timezone so we can put it back v_session_timezone := sessiontimezone; v_location_i := 1000; --Extract the seconds and timezone region from the incoming timestamp o_secs := extract(second from p_timestamp); o_region := extract (timezone_region from p_timestamp); --If there is no region name specified, use the offsets if (o_region = 'UNKNOWN') then o_region := extract(timezone_hour from p_timestamp)||':'||extract(timezone_minute from p_timestamp); end if; --Alter the session timezone to ensure proper translation in our cast functions execute immediate 'alter session set time_zone = '''||o_region||''''; v_location_i := 3000; --We convert the timestamp to a date to do the month math t_date := trunc(p_timestamp, 'MI'); --Debug output --dbms_output.put_line('Extracted seconds: '||o_secs); --dbms_output.put_line('Extracted region: '||o_region); --dbms_output.put_line('converted date: '||t_date); v_location_i := 4000; t_date := add_months(t_date, p_added_months); --dbms_output.put_line('after add_months date: '||t_date); v_location_i := 5000; --Now we build a new timestamp with our manipulated date and extracted seconds begin r_timestamp := to_timestamp(to_char(t_date, 'DD-MON-YYYY HH24:MI')||o_secs, 'DD-MON-YYYY HH24:MI:SS.FF'); exception when others then v_location_i := 5100; --Use the simple date to the second r_timestamp := to_timestamp(to_char(t_date,'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS'); end; --Debugging output --dbms_output.put_line('final timestamp: '||r_timestamp); --dbms_output.put_line('final timestamp region name: '||extract(timezone_region from r_timestamp)); --dbms_output.put_line('final timestamp region offset: '||extract(timezone_hour from r_timestamp)||':'||extract(timezone_minute from r_timestamp)); --Put the session time zone back execute immediate 'alter session set time_zone = '''||v_session_timezone||''''; return r_timestamp; exception when others then dbms_output.put_line('Error at '||v_location_i||': '||sqlerrm); return null; end add_months_tz; /

Feel free to use this code as you see fit, but please be courteous and give me credit (even if only in your pl/sql comments).

---