Oracle Timezone Conversion for Date

CREATE OR REPLACE FUNCTION dst_newtime(
    p_date            IN DATE,
    p_from_timezone   IN VARCHAR2,
    p_to_timezone     IN VARCHAR2,
    p_2am             IN NUMBER DEFAULT 1
)
    RETURN DATE
    DETERMINISTIC
IS
    v_year                 PLS_INTEGER;
    v_start_of_dst         DATE;
    v_end_of_dst           DATE;
    v_temp_timezone        VARCHAR2(3);
    v_temp_date            DATE;
    v_from_daylight_offset NUMBER;
    v_from_standard_offset NUMBER;
    v_to_daylight_offset   NUMBER;
    v_to_standard_offset   NUMBER;
    v_from_offset          NUMBER;

    c_dummy_date  CONSTANT DATE := TO_DATE('2000-01-01', 'yyyy-mm-dd');
BEGIN
    --
    -- If no conversions are between "Prevailing Time" timezones
    -- or if the input date is NULL then act as standard NEW_TIME
    --
    IF p_date IS NULL
    OR  (p_from_timezone NOT IN ('EPT', 'CPT', 'MPT', 'PPT', 'YPT', 'HPT')
     AND p_to_timezone NOT IN ('EPT', 'CPT', 'MPT', 'PPT', 'YPT', 'HPT'))
    THEN
        RETURN NEW_TIME(p_date, p_from_timezone, p_to_timezone);
    END IF;

    --
    -- For dates prior to the Uniform Time Act of 1966 we can't produce a standardized algorithm
    -- all "Prevailing" timezones are invalid for these dates, raise an error reporting them as such.
    --
    IF p_date < TO_DATE('1966-04-24', 'yyyy-mm-dd')
    THEN
        raise_application_error(
            -20000,
            'U.S. "prevailing" time not supported prior to The Uniform Time Act of 1966'
        );
    END IF;

    -- For simplicity of calculation,
    -- convert all "prevailing" times to and from GMT
    -- To calculate offsets, any date will do, so a dummy date is used
    IF p_from_timezone = 'EPT'
    THEN
        v_from_daylight_offset := -4;
        v_from_standard_offset := -5;
    ELSIF p_from_timezone = 'CPT'
    THEN
        v_from_daylight_offset := -5;
        v_from_standard_offset := -6;
    ELSIF p_from_timezone = 'MPT'
    THEN
        v_from_daylight_offset := -6;
        v_from_standard_offset := -7;
    ELSIF p_from_timezone = 'PPT'
    THEN
        v_from_daylight_offset := -7;
        v_from_standard_offset := -8;
    ELSIF p_from_timezone = 'YPT'
    THEN
        v_from_daylight_offset := -8;
        v_from_standard_offset := -9;
    ELSIF p_from_timezone = 'HPT'
    THEN
        v_from_daylight_offset := -9;
        v_from_standard_offset := -10;
    ELSE
        v_from_daylight_offset := NEW_TIME(c_dummy_date, p_from_timezone, 'GMT') - c_dummy_date;
        v_from_standard_offset := NEW_TIME(c_dummy_date, p_from_timezone, 'GMT') - c_dummy_date;
    END IF;

    IF p_to_timezone = 'EPT'
    THEN
        v_to_daylight_offset := -4;
        v_to_standard_offset := -5;
    ELSIF p_to_timezone = 'CPT'
    THEN
        v_to_daylight_offset := -5;
        v_to_standard_offset := -6;
    ELSIF p_to_timezone = 'MPT'
    THEN
        v_to_daylight_offset := -6;
        v_to_standard_offset := -7;
    ELSIF p_to_timezone = 'PPT'
    THEN
        v_to_daylight_offset := -7;
        v_to_standard_offset := -8;
    ELSIF p_to_timezone = 'YPT'
    THEN
        v_to_daylight_offset := -8;
        v_to_standard_offset := -9;
    ELSIF p_to_timezone = 'HPT'
    THEN
        v_to_daylight_offset := -9;
        v_to_standard_offset := -10;
    ELSE
        v_to_daylight_offset := NEW_TIME(c_dummy_date, p_to_timezone, 'GMT') - c_dummy_date;
        v_to_standard_offset := NEW_TIME(c_dummy_date, p_to_timezone, 'GMT') - c_dummy_date;
    END IF;

    --
    -- Determine the daylight/standard transition dates.
    --
    v_year := TO_NUMBER(TO_CHAR(p_date, 'yyyy'));

    IF v_year = 1974
    THEN
        -- Rule established: December 15, 1973 [P.L. 93-182] Nixon
        --   Law name: 'Emergency Daylight Saving Time Energy Conservation Act of 1973'
        v_start_of_dst := TO_DATE('1974-01-06', 'yyyy-mm-dd');
        v_end_of_dst := TO_DATE('1974-10-27', 'yyyy-mm-dd');
    ELSIF v_year = 1975
    THEN
        -- Rule established: December 15, 1973 [P.L. 93-182] Nixon
        --   Law name: 'Emergency Daylight Saving Time Energy Conservation Act of 1973'
        --   amended by [P.L. 93-434]
        v_start_of_dst := TO_DATE('1975-02-23', 'yyyy-mm-dd');
        -- DST ends Last Sundary in October
        v_end_of_dst := TO_DATE('1975-10-26', 'yyyy-mm-dd');
    ELSIF v_year <= 1986
    THEN
        -- Rule established: April 13, 1966 [Public Law 89-387] Johnson
        --   Law name: 'The Uniform Time Act of 1966' (15 U.S. Code Section 260a)
        -- DST starts on Last Sunday in April in 1986 and prior
        v_start_of_dst := NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-04-23', 'yyyy-mm-dd'), 'SUNDAY');
        -- DST ends on Last Sunday in October
        v_end_of_dst := NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-10-24', 'yyyy-mm-dd'), 'SUNDAY');
    ELSIF v_year < 2007
    THEN
        -- Rule established: October 8, 1986 [P.L. 99-359] Reagan
        --   Law name: 'Fire Prevention and Control Authorizations Act'
        -- DST starts on First Sunday in April after 1986
        v_start_of_dst := NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-03-31', 'yyyy-mm-dd'), 'SUNDAY');
        -- DST ends on Last Sunday in October
        v_end_of_dst := NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-10-24', 'yyyy-mm-dd'), 'SUNDAY');
    ELSE
        -- Rule established: August 8, 2005 [ HR6] Bush
        --   Law name: 'Energy Policy Act of 2005'
        -- DST starts on Second Sunday in March starting 2007
        v_start_of_dst :=
            NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-03-01', 'yyyy-mm-dd') - 1, 'SUNDAY') + 7;
        -- DST ends on First Sunday in November starting 2007
        v_end_of_dst := NEXT_DAY(TO_DATE(TO_CHAR(v_year) || '-10-31', 'yyyy-mm-dd'), 'SUNDAY');
    END IF;

    IF p_date <= (v_start_of_dst + 2 / 24)
    THEN
        v_from_offset := v_from_standard_offset;
    ELSIF p_date < (v_end_of_dst + 1 / 24)
    THEN
        v_from_offset := v_from_daylight_offset;
    ELSIF p_date <= (v_end_of_dst + 2 / 24)
    THEN
        -- For switch back to Standard time
        -- there are two periods of 1:00:00am to 1:59:59am on this day.
        -- 1:59:59am Daylight Saving "falls back" to 1:00:00am Standard
        IF p_2am = 1
        THEN
            -- At the instant the first 2am of the Fall switch date is reached
            -- Daylight Saving Time ends and it becomes 1am Standard instead
            v_from_offset := v_from_daylight_offset;
        ELSE
            -- The second 1am of the Fall switch date begins Standard Time
            v_from_offset := v_from_standard_offset;
        END IF;
    ELSE
        -- p_date is after 2am on Fall switch date where all times are Standard
        v_from_offset := v_from_standard_offset;
    END IF;

    IF v_temp_date < (v_start_of_dst + 2 / 24) + v_to_standard_offset
    THEN
        -- Standard time is before the first transition
        v_temp_date := v_temp_date - v_to_standard_offset + v_from_offset;
    ELSIF v_temp_date < (v_end_of_dst + 1 / 24) + v_to_daylight_offset
    THEN
        -- Between the Spring and Fall switch times is the Daylight Saving period
        v_temp_date := v_temp_date - v_to_daylight_offset + v_from_offset;
    ELSE
        -- p_date is on or after 2am on Fall switch date so it's Standard
        -- All times are standard so no further change is needed.
        v_temp_date := v_temp_date - v_to_standard_offset + v_from_offset;
    END IF;

    RETURN v_temp_date;
END dst_newtime;
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192: