Twice a year, local clocks in many countries jump. In spring an hour of local time never happens, and in autumn an hour happens twice. Oracle's TIMESTAMP WITH TIME ZONE values with a region name, such as America/New_York, know these rules, so arithmetic stays correct. But a timestamp written inside the missing hour is invalid, and one inside the repeated hour is ambiguous. This guide shows how Oracle behaves in both cases and how to handle them.
Code for This Guide
The main examples are in the examples/date-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
The Spring Gap
On 8 March 2026, clocks in New York jump from 02:00 to 03:00, so local times from 02:00 to 02:59 do not exist. Adding an hour to 01:30 handles the jump and returns 03:30. Writing a time inside the gap fails.
Example:
-- clocks in New York jump from 02:00 to 03:00 on 8 March 2026
select timestamp '2026-03-08 01:30:00 America/New_York' + interval '1' hour
as elapsed_hour
from dual;
select timestamp '2026-03-08 02:30:00 America/New_York' as in_the_gap from dual;Output:
ELAPSED_HOUR ________________________________________ 08-MAR-2026 03:30:00 AMERICA/NEW_YORK Error starting at line : 6 In command - select timestamp '2026-03-08 02:30:00 America/New_York' as in_the_gap from dual Error at Command Line : 6 Column : 18 Error report - SQL Error: ORA-01878: specified field not found in datetime or interval
ORA-01878 is common when loading local times from outside the database, such as spreadsheets or devices that record wall-clock time without a zone. Fix the data at the source, or convert such values from a known UTC time instead of from local text.
The Autumn Overlap
On 1 November 2026, clocks in New York go back from 02:00 to 01:00, so 01:30 happens twice: first in daylight time (EDT), then in standard time (EST). Adding the abbreviation to the literal says which one you mean.
Example:
-- clocks in New York go back from 02:00 to 01:00 on 1 November 2026: 01:30 happens twice
select timestamp '2026-11-01 01:30:00 America/New_York EDT' as first_0130,
timestamp '2026-11-01 01:30:00 America/New_York EST' as second_0130,
timestamp '2026-11-01 01:30:00 America/New_York EST'
- timestamp '2026-11-01 01:30:00 America/New_York EDT' as apart
from dual;
select to_char(timestamp '2026-11-01 01:30:00 America/New_York', 'HH24:MI TZD') as no_tzd
from dual;Output:
FIRST_0130 SECOND_0130 APART ________________________________________ ________________________________________ ______________________ 01-NOV-2026 01:30:00 AMERICA/NEW_YORK 01-NOV-2026 01:30:00 AMERICA/NEW_YORK +00 01:00:00.000000 NO_TZD ____________ 01:30 EST
Both values display as 01:30, but they are an hour apart. Without the abbreviation, Oracle chooses standard time, as the TZD element shows. If your data records which of the two was meant, keep that information, for example as an offset or a UTC value.
Arithmetic Across a Change
Intervals added to TIMESTAMP WITH TIME ZONE values with a region are elapsed time, and the result is shown in the region's local time. So 24 hours after noon on the day before a change is not noon the next day, and the difference between two wall-clock times across a change is not what the clock suggests. Use a DATE or a plain TIMESTAMP when you want wall-clock arithmetic, and a TIMESTAMP WITH TIME ZONE when you want real elapsed time.
When the Rules Change: ORA_DST Functions
Governments change daylight saving rules, and Oracle ships them in new time zone files. When the DBA upgrades the time zone file with the DBMS_DST package, stored timestamps may need converting. During that upgrade window, ORA_DST_AFFECTED tells whether a value is affected by the new rules, ORA_DST_CONVERT converts it, and ORA_DST_ERROR reports a problem with it. Outside an upgrade window they raise an error.
Example:
select ora_dst_affected(timestamp '2026-03-15 10:00:00 Europe/London') as affected from dual;
Output:
Error starting at line : 1 In command - select ora_dst_affected(timestamp '2026-03-15 10:00:00 Europe/London') as affected from dual Error at Command Line : 1 Column : 8 Error report - SQL Error: ORA-30091: secondary translation table not loaded
Things to Know
- Store time zone regions such as Europe/London rather than fixed offsets, so the rules apply automatically.
- Store or compare instants in UTC (SYS_EXTRACT_UTC) when exactness matters, and convert to local time only for display.
- Test date logic with dates on both sides of a change; most bugs appear only twice a year.
Related Guides
- Oracle FROM_TZ Function: A Simple Guide
- Oracle SYS_EXTRACT_UTC Function: A Simple Guide
- Oracle TIMESTAMPDIFF Function
Conclusion
Region-based time zones make Oracle's timestamp arithmetic correct across daylight saving changes. A local time inside the spring gap raises ORA-01878, and a time inside the autumn overlap is ambiguous unless you add the TZD abbreviation; Oracle otherwise picks standard time. Store regions or UTC, and test dates on both sides of each change.
