(1)-Concepts
(2)-Examples
(3)-Assignments
(4)-Comparisons
(5)-SQL Arithmetic
(6)-PLSQL Arithmetic
This small Oracle Datetime Cookbook is made of a series of 6 Blogs:
Oracle Datetime (1) - Concepts
Oracle Datetime (2) - Examples
Oracle Datetime (3) - Assignments
Oracle Datetime (4) - Comparisons
Oracle Datetime (5) - SQL Arithmetic
Oracle Datetime (6) - PLSQL Arithmetic
1. Data Types
Oracle Datetime consists of 4 basic Data Types:
DATE stores as a literal constant, no TimeZone.
TIMESTAMP stores as a literal constant, no TimeZone.
TIMESTAMP WITH TIME ZONE stores with explicit TimeZone.
TIMESTAMP WITH LOCAL TIME ZONE stores with implicit sessiontimezone as default TimeZone.
The difference between DATE and TIMESTAMP is precision (fractional part of the SECOND),
and the difference between TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE is the later one with a default TimeZone,
but the common usage is same.
Therefore, there are two essential Data Types: TIMESTAMP and TIMESTAMP WITH TIME ZONE. Even TIMESTAMP can be considered
as a Subtype of TIMESTAMP WITH TIME ZONE without time zone.
2. Oracle built-in Functions
SYSDATE returns current Datetime in the server (OS) time zone in datatype DATE.
SYSTIMESTAMP returns current Datetime in the server (OS) time zone (Unix TZ variable)
in datatype TIMESTAMP WITH TIME ZONE.
CURRENT_DATE returns current Datetime in the session time zone in datatype DATE.
CURRENT_TIMESTAMP returns current Datetime in the session time zone in datatype
TIMESTAMP WITH TIME ZONE.
LOCALTIMESTAMP returns the current Datetime in the session time zone in datatype TIMESTAMP.
The first two are from OS Server point of view, irrelevant to Oracle, just like date command.
SYSDATE is SYSTIMESTAMP by dropping Time Zone info.
The next two are the counterparts from Oracle session point of view, depending on each Oracle session setting.
CURRENT_DATE and LOCALTIMESTAMP are CURRENT_TIMESTAMP by removing Time Zone info.
The last one is an Oracle special mixed variant (not in SQL-92), API similar to TIMESTAMP WITH TIME ZONE, internal storage as TIMESTAMP relative to DBTIMEZONE (which is invented only for TIMESTAMP WITH LOCAL TIME ZONE).
This automatic conversion probably implicates certain performance difference, as tested, it is about 30%
(1000,000 calls takes less than 1 second).
In fact, LOCALTIMESTAMP is a cast of CURRENT_TIMESTAMP as data type timestamp_unconstrained
(see Oracle package SYS.STANDARD spec and body), whereas internally timestamp_unconstrained is defined as Typ=180.
type TIMESTAMP is new DATE_BASE;
SUBTYPE TIMESTAMP_UNCONSTRAINED IS TIMESTAMP(9);
SUBTYPE TIMESTAMP_TZ_UNCONSTRAINED IS TIMESTAMP(9) WITH TIME ZONE;
FUNCTION localtimestamp RETURN timestamp_unconstrained
IS t timestamp_tz_unconstrained := current_timestamp;
BEGIN
RETURN (cast(t AS timestamp_unconstrained));
END;
declare
l_tz_unconstrained timestamp_unconstrained := (cast(current_timestamp AS timestamp_unconstrained));
l_dump varchar2(100);
begin
select (dump(l_tz_unconstrained, 1016)) into l_dump from dual;
dbms_output.put_line('timestamp_unconstrained DUMP: '||l_dump);
end;
/
timestamp_unconstrained DUMP: Typ=180 Len=11: 78,78,b,8,b,23,20,5,2a,38,48
For performance discussion on datetime indexing, see Blog:
Tony’s Tirade against TIMESTAMP WITH TIME ZONE
Here is a basic test and its output:
col dbtimezone format a15
col sessiontimezone format a15
col sysdate format a25
col systimestamp format a35
col current_date format a25
col current_timestamp format a50
col localtimestamp format a35
ALTER SESSION SET NLS_DATE_FORMAT ='YYYY*MON*DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='YYYY*MON*DD HH24:MI:SS.FF9';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='YYYY-MON-DD HH24:MI:SS.FF9 TZR TZD';
-- ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='SYYYY-MON-DD HH24:MI:SS.ff9 TZR TZD';
-- ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT ='YYYY-MON-DD HH24:MI:SS.ff9 TZR TZD AD';
alter session set time_zone = 'Europe/Paris';
select dbtimezone, sessiontimezone,
sysdate, systimestamp,
current_date, current_timestamp, localtimestamp
from dual;
DBTIMEZONE : +01:00
SESSIONTIMEZONE : Europe/Paris
SYSDATE : 2017*MAY*02 07:52:50
SYSTIMESTAMP : 2017-MAY-02 07:52:50.123456000 +02:00
CURRENT_DATE : 2017*MAY*02 07:52:50
CURRENT_TIMESTAMP : 2017-MAY-02 07:52:50.123458000 EUROPE/PARIS CEST
LOCALTIMESTAMP : 2017*MAY*02 07:52:50.123458000
Oracle MOS Note*: The Priority of NLS Parameters Explained (Where To Define NLS Parameters) (Doc ID 241047.1)
NLS_TIME_FORMAT and NLS_TIME_TZ_FORMAT, are currently used for internal purposes only.
We strongly suggest to NOT define them. If they are visible in the NLS_INSTANCE_PARAMETERS
then please DO remove them and bounce the database. If set they may cause errors
like ORA-1821: date format not recognized, ORA-6512: at "SYS.DBMS_SCHEDULER"
when submitting / running DBMS_SCHEDULER jobs.
ALTER SESSION SET NLS_TIME_FORMAT ='HH.MI.SSXFF AM';
ALTER SESSION SET NLS_TIME_TZ_FORMAT ='HH.MI.SSXFF AM TZR';
3. Time Zone and Time Zone Abbreviation
(a). Time Zone can be represented in either Named TZ (TZR), or Offset TZ ( TZH:TZM) format for example:
alter session set time_zone='Europe/Paris';
alter session set time_zone='+01:00';
Named TZ is DST aware; If the Named region is DST sensitive, it is varied between Standard and DST. Otherwise it is static with a constant offset.
Offset TZ is DST unaware with a constant offset.
(b). Time Zone Abbreviation is DST unaware, defined with a constant offset
It is used to distinguish ambiguous overlap timestamp during transit from DST to Standard when ERROR_ON_OVERLAP_TIME is enabled.
If ERROR_ON_OVERLAP_TIME is disabled, it takes Standard as the default in case of ambiguity.
Here is an example:
alter session set ERROR_ON_OVERLAP_TIME=FALSE;
select TIMESTAMP '2017-10-29 02:52:00 Europe/Paris' from dual;
2017-OCT-29 02:52:00 EUROPE/PARIS CET
alter session set ERROR_ON_OVERLAP_TIME=TRUE;
select TIMESTAMP '2017-10-29 02:52:00 Europe/Paris' from dual;
ORA-01883: overlap was disabled during a region transition
select TIMESTAMP '2017-10-29 02:52:00 Europe/Paris CEST',
TIMESTAMP '2017-10-29 02:52:00 Europe/Paris CET' from dual;
2017-OCT-29 02:52:00 EUROPE/PARIS CEST
2017-OCT-29 02:52:00 EUROPE/PARIS CET
CET is a special twofold shortcut. It denotes a Time Zone Region (TZR, such as Europe/Paris),
and it also denotes a static Time Zone Abbreviation (TZD: Time Zone Designator, such as CEST).
The difference is that TZR CET is DST aware, in Winter it is (UTC +1), in Summer, it is (UTC +2).
Whereas TZD CET is static, it represents fixed Central European Time (UTC +1).
So in Winter, TZR CET is TZD CET (UTC +1); whereas in Summer, TZR CET is TZD CEST (UTC +2).
We can say that TZD is same as Offset TZ ( TZH:TZM), or TZD is a symbolic notation of TZH:TZM.
For example,
select TIMESTAMP '2017-10-29 02:52:00 CET CET' from dual;
2017-OCT-29 02:52:00 CET CET
select * from v$timezone_names where tzname = 'CET' and tzabbrev = 'CET';
TZNAME TZABBREV
--------- ---------
CET CET
select TIMESTAMP '2017-10-28 02:52:00 CET CEST',
TIMESTAMP '2017-10-28 02:52:00 Europe/Paris CEST',
TIMESTAMP '2017-10-30 02:52:00 CET CET',
TIMESTAMP '2017-10-30 02:52:00 Europe/Paris CET' from dual;
2017-OCT-28 02:52:00 CET CEST
2017-OCT-28 02:52:00 EUROPE/PARIS CEST
2017-OCT-30 02:52:00 CET CET
2017-OCT-30 02:52:00 EUROPE/PARIS CET
4. Server Time Zone
Oracle does not have support to get Named TZ of Server. SYSTIMESTAMP output has an offset from UTC,
defined not to include an actual named timezone, for example,
select extract(TIMEZONE_OFFSET from systimestamp), extract(TIMEZONE_REGION from systimestamp) from dual;
+000000000 02:00:00.000000000
UNKNOWN
There exists an internal implementation in DBMS_SCHEDULER to get Named TZ (one DBMS_SCHEDULER attribute):
select DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
select * from dba_scheduler_global_attribute where attribute_name='DEFAULT_TIMEZONE';
It works in AIX, Solaris, Linux in most case, except HP.
UNIX environment variable TZ has two TZ format: POSIX and Olson.
It seems that Java is able to get a unified Name TZ with Class TimeZone (see JDK 8 "tzdb.dat"):
import java.time.Instant;
import java.util.TimeZone;
public class TimeZoneTest {
public static void main(String args[]) {
TimeZone tz = TimeZone.getDefault();
// TimeZone tz = Calendar.getInstance().getTimeZone();
System.out.println("ID=" + tz.getID());
System.out.println("Name=" + tz.getDisplayName());
final String clientTimeZoneOffset = tz.toZoneId().getRules().getOffset(Instant.now()).getId();
System.out.println("TimeZoneOffset = " + clientTimeZoneOffset);
}
}
Here one set of test results on different UNIX:
(Unix TZ environment format: AIX in POSIX. Linux, Solaris in Olson. HP in special format)
| OS |
AIX |
Solaris |
Linux |
HP |
| echo $TZ |
CET-1CEST,M3.5.0,M10.5.0 |
Europe/Zurich |
Europe/Zurich |
MET-1METDST |
| dbms_scheduler.get_sys_time_zone_name |
Europe/Vienna |
Europe/Zurich |
Europe/Zurich |
|
| TimeZoneTest.java ID |
Europe/Paris |
Europe/Zurich |
Europe/Zurich |
Europe/Paris |
| TimeZoneTest.java Name |
Central European Time |
Central European Time |
Central European Time |
Central European Time |
Offset TZ in SYSTIMESTAMP is determined by the shell TZ of UNIX process, from which the Oracle session is spawned.
Therefore, we can have 3 different connection scenarios, each with its own shell TZ.
If they are all configured differently, we may end up 3 different Offset TZ in SYSTIMESTAMP (although this is not a recommended practice):
(1). TZ of UNIX process which starts up DB.
This TZ is used by Oracle processes to get SYSTIMESTAMP and SYSDATE when recording logs and traces.
Content in alert.log and trace files are stamped in SYSDATE.
Datetime in alert/log.xml is enhanced and marked in SYSTIMESTAMP, i.e, with Time Zone info.
DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME returns this shell TZ (in Named TZ).
By the way, Oracle 12.2.0.1 introduced UNIFORM_LOG_TIMESTAMP_FORMAT
to specify a uniform timestamp format in trace (.trc) files and alert log.
(2). TZ of UNIX process which starts a local connection via Bequeath Protocol (sqlplus / as sysdba)
(3). TZ of UNIX process which starts TNS Listener, and Oracle session is connected via Listener.
DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME Test
DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME is a call of subroutine "jsxsgetsystimezonename" in SYS.DBMS_ISCHED.GET_SYS_TIME_ZONE_NAME.
Oracle MOS "DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained (Doc ID 467722.1)" wrote:
SELECT DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME FROM DUAL; is not related to the DEFAULT_TIMEZONE.
(select value from dba_scheduler_global_attribute where attribute_name='DEFAULT_TIMEZONE';)
DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME provides the TimeZone set on the OS level trough the TZ variable,
it will return the named timezone only if the OS TZ setting is also known in Oracle.
Only when no OS TZ variable is set it will report the DEFAULT_TIMEZONE.
We will make 4 different TZ tests and watch the output (The output contains more info and not all fits to above MOS Docu).
The test is performed in Linux - Oracle 19.17 with following time and DEFAULT_TIMEZONE settings:
$ timedatectl
Local time: Sun 2023-06-11 13:57:12 GMT
Universal time: Sun 2023-06-11 13:57:12 UTC
RTC time: Sun 2023-06-11 13:57:12
Time zone: Etc/GMT (GMT, +0000)
NTP enabled: no
NTP synchronized: yes
RTC in local TZ: no
DST active: n/a
SQL> select value from dba_scheduler_global_attribute where attribute_name='DEFAULT_TIMEZONE';
Europe/Zurich
Case-1 unset TZ
GET_SYS_TIME_ZONE_NAME return depends on session time_zone setting.
--===================================== unset TZ ===============================================
$ unset TZ
$ export TZ
SQL> startup force
SQL> host echo $TZ -- in case of PC Window Sqlplus, check Date&Time -> Time Zone setting
-- no value return
SQL> select value from dba_scheduler_global_attribute where attribute_name='DEFAULT_TIMEZONE';
Europe/Zurich
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+00:00 Etc/GMT
SQL> alter session set time_zone = 'Europe/Paris';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
Europe/Paris -- no value for GET_SYS_TIME_ZONE_NAME
SQL> alter session set time_zone = 'Etc/GMT';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+00:00 Etc/GMT
SQL> alter session set time_zone = '+02:00';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+02:00 -- no value for GET_SYS_TIME_ZONE_NAME
SQL> alter session set time_zone = '+00:00';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+00:00 Etc/GMT
Case-2 TZ=""
All GET_SYS_TIME_ZONE_NAME returns "UTC".
--=============== TZ="" (without: export TZ="", output can be different) ===================
$ set TZ=""
$ export TZ=""
SQL> startup force
SQL> host echo $TZ -- in case of PC Window Sqlplus, check Date&Time -> Time Zone setting
-- no value return
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+00:00 UTC
SQL> alter session set time_zone = 'Europe/Paris';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+00:00 UTC
SQL> alter session set time_zone = 'Etc/GMT';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+00:00 UTC
SQL> alter session set time_zone = '+02:00';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+00:00 UTC
SQL> alter session set time_zone = '+00:00';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+00:00 UTC
SQL> alter session set time_zone = 'UTC';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
UTC UTC
Case-3 TZ="Europe/Paris"
All GET_SYS_TIME_ZONE_NAME returns "Europe/Paris".
--===================================== TZ="Europe/Paris" ====================================
$ set TZ="Europe/Paris"
$ export TZ="Europe/Paris"
SQL> startup force
SQL> host echo $TZ -- in case of PC Window Sqlplus, check Date&Time -> Time Zone setting
Europe/Paris
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+02:00 Europe/Paris
SQL> alter session set time_zone = 'Europe/Paris';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
Europe/Paris Europe/Paris
SQL> alter session set time_zone = 'Etc/GMT';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
Etc/GMT Europe/Paris
SQL> alter session set time_zone = '+02:00';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+02:00 Europe/Paris
SQL> alter session set time_zone = '+00:00';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+00:00 Europe/Paris
Case-4 TZ="Europe/Pariz" (wrong name: Pariz)
There are no values for GET_SYS_TIME_ZONE_NAME.
--============================= TZ="Europe/Pariz" (wrong name: Pariz) ========================
$ set TZ="Europe/Pariz"
$ export TZ="Europe/Pariz"
SQL> startup force
SQL> host echo $TZ -- in case of PC Window Sqlplus, check Date&Time -> Time Zone setting
Europe/Pariz
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+00:00 -- no value for GET_SYS_TIME_ZONE_NAME
SQL> alter session set time_zone = 'Europe/Paris';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
Europe/Paris -- no value for GET_SYS_TIME_ZONE_NAME
SQL> alter session set time_zone = 'Etc/GMT';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
Etc/GMT -- no value for GET_SYS_TIME_ZONE_NAME
SQL> alter session set time_zone = '+02:00';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+02:00 -- no value for GET_SYS_TIME_ZONE_NAME
SQL> alter session set time_zone = '+00:00';
SQL> select SESSIONTIMEZONE, sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME from dual;
+00:00 -- no value for GET_SYS_TIME_ZONE_NAME
When setting TZ with POSIX time zone format (in AIX or Linux), one should specify timezone names which are listed in V$TIMEZONE_NAMES,
for example, following POSIX time zone format (Olson equivalent TZ=Europe/Zurich):
export TZ=CET-1CEST,M3.5.0,M10.5.0
If using AIX specific format:
export TZ=NFT-1DFT,M3.5.0,M10.5.0
The query does not return any row:
select sys.DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME SYS_ZONE_NAME from dual;
We can use AIX zdump to display the time zone information:
$ > zdump -v -c 2025,2027 Europe/Vienna
Europe/Vienna Fri Dec 13 20:45:52 1901 UT = Fri Dec 13 21:45:52 1901 CET isdst=0 gmtoff=3600
Europe/Vienna Sat Dec 14 20:45:52 1901 UT = Sat Dec 14 21:45:52 1901 CET isdst=0 gmtoff=3600
Europe/Vienna Sun Mar 30 00:59:59 2025 UT = Sun Mar 30 01:59:59 2025 CET isdst=0 gmtoff=3600
Europe/Vienna Sun Mar 30 01:00:00 2025 UT = Sun Mar 30 03:00:00 2025 CEST isdst=1 gmtoff=7200
Europe/Vienna Sun Oct 26 00:59:59 2025 UT = Sun Oct 26 02:59:59 2025 CEST isdst=1 gmtoff=7200
Europe/Vienna Sun Oct 26 01:00:00 2025 UT = Sun Oct 26 02:00:00 2025 CET isdst=0 gmtoff=3600
Europe/Vienna Sun Mar 29 00:59:59 2026 UT = Sun Mar 29 01:59:59 2026 CET isdst=0 gmtoff=3600
Europe/Vienna Sun Mar 29 01:00:00 2026 UT = Sun Mar 29 03:00:00 2026 CEST isdst=1 gmtoff=7200
Europe/Vienna Sun Oct 25 00:59:59 2026 UT = Sun Oct 25 02:59:59 2026 CEST isdst=1 gmtoff=7200
Europe/Vienna Sun Oct 25 01:00:00 2026 UT = Sun Oct 25 02:00:00 2026 CET isdst=0 gmtoff=3600
Europe/Vienna Mon Jan 18 03:14:07 2038 UT = Mon Jan 18 04:14:07 2038 CET isdst=0 gmtoff=3600
Europe/Vienna Tue Jan 19 03:14:07 2038 UT = Tue Jan 19 04:14:07 2038 CET isdst=0 gmtoff=3600
$ > zdump -v -c 2025,2027 CET
CET Fri Dec 13 20:45:52 1901 UT = Fri Dec 13 21:45:52 1901 CET isdst=0 gmtoff=3600
CET Sat Dec 14 20:45:52 1901 UT = Sat Dec 14 21:45:52 1901 CET isdst=0 gmtoff=3600
CET Sun Mar 30 00:59:59 2025 UT = Sun Mar 30 01:59:59 2025 CET isdst=0 gmtoff=3600
CET Sun Mar 30 01:00:00 2025 UT = Sun Mar 30 03:00:00 2025 CEST isdst=1 gmtoff=7200
CET Sun Oct 26 00:59:59 2025 UT = Sun Oct 26 02:59:59 2025 CEST isdst=1 gmtoff=7200
CET Sun Oct 26 01:00:00 2025 UT = Sun Oct 26 02:00:00 2025 CET isdst=0 gmtoff=3600
CET Sun Mar 29 00:59:59 2026 UT = Sun Mar 29 01:59:59 2026 CET isdst=0 gmtoff=3600
CET Sun Mar 29 01:00:00 2026 UT = Sun Mar 29 03:00:00 2026 CEST isdst=1 gmtoff=7200
CET Sun Oct 25 00:59:59 2026 UT = Sun Oct 25 02:59:59 2026 CEST isdst=1 gmtoff=7200
CET Sun Oct 25 01:00:00 2026 UT = Sun Oct 25 02:00:00 2026 CET isdst=0 gmtoff=3600
CET Mon Jan 18 03:14:07 2038 UT = Mon Jan 18 04:14:07 2038 CET isdst=0 gmtoff=3600
CET Tue Jan 19 03:14:07 2038 UT = Tue Jan 19 04:14:07 2038 CET isdst=0 gmtoff=3600
$ > zdump -v -c 2025,2027 CEST
CEST Fri Dec 13 20:45:52 1901 UT = Fri Dec 13 20:45:52 1901 CEST isdst=0 gmtoff=0
CEST Sat Dec 14 20:45:52 1901 UT = Sat Dec 14 20:45:52 1901 CEST isdst=0 gmtoff=0
CEST Mon Jan 18 03:14:07 2038 UT = Mon Jan 18 03:14:07 2038 CEST isdst=0 gmtoff=0
CEST Tue Jan 19 03:14:07 2038 UT = Tue Jan 19 03:14:07 2038 CEST isdst=0 gmtoff=0
$ > zdump -v -c 2025,2027 NFT
NFT Fri Dec 13 20:45:52 1901 UT = Fri Dec 13 20:45:52 1901 NFT isdst=0 gmtoff=0
NFT Sat Dec 14 20:45:52 1901 UT = Sat Dec 14 20:45:52 1901 NFT isdst=0 gmtoff=0
NFT Mon Jan 18 03:14:07 2038 UT = Mon Jan 18 03:14:07 2038 NFT isdst=0 gmtoff=0
NFT Tue Jan 19 03:14:07 2038 UT = Tue Jan 19 03:14:07 2038 NFT isdst=0 gmtoff=0
$ > zdump -v -c 2025,2027 DFT
DFT Fri Dec 13 20:45:52 1901 UT = Fri Dec 13 20:45:52 1901 DFT isdst=0 gmtoff=0
DFT Sat Dec 14 20:45:52 1901 UT = Sat Dec 14 20:45:52 1901 DFT isdst=0 gmtoff=0
DFT Mon Jan 18 03:14:07 2038 UT = Mon Jan 18 03:14:07 2038 DFT isdst=0 gmtoff=0
DFT Tue Jan 19 03:14:07 2038 UT = Tue Jan 19 03:14:07 2038 DFT isdst=0 gmtoff=0
gdb debug
For Case-1 unset TZ, if we gdb the Sqlplus session, in case of DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME returning "Etc/GMT",
we can see following output.
(no Breakpoint stopped in case of "no value return")
break *OCIStringAssignText
break *jsxsGetSysTimeZoneName+938
display /s $rbx
display /s $rsp
Breakpoint 2, 0x0000000004b50f00 in OCIStringAssignText ()
2: x/s $rsp 0x7ffc9e55bd28: "\372Yt\f"
1: x/s $rbx 0xa5dc026d: "Etc/GMT"
(gdb) c
Continuing.
Breakpoint 1, 0x000000000c7459fa in jsxsGetSysTimeZoneName ()
2: x/s $rsp 0x7ffc9e55bd30: "GMT"
1: x/s $rbx 0xa5dc026d: "Etc/GMT"
The call stack and part of jsxsGetSysTimeZoneName code lines are as follows:
Breakpoint 1, 0x0000000004313c70 in LdiDateComparei ()
(gdb) bt
#0 0x0000000004313c70 in LdiDateComparei ()
#1 0x000000000434768a in ltzGetIndex ()
#2 0x0000000004347560 in ltzGet ()
#3 0x0000000004327884 in sLdiGetLazyDt_int ()
#4 0x00000000043274ec in sLdiGetDate ()
#5 0x000000000c745750 in jsxsGetSysTimeZoneName ()
0x000000000c745716 <+198>: callq 0x43274a0
...
0x000000000c74585c <+524>: callq 0x1032ad40
...
0x000000000c74599c <+844>: callq 0x1032ad40
0x000000000c7459d4 <+900>: jne 0xc7459e1
0x000000000c7459d6 <+902>: mov %rdx,%rax
0x000000000c7459d9 <+905>: add %rcx,%rdx
0x000000000c7459dc <+908>: callq 0x6fcac60 <__intel_sse2_strlen>
0x000000000c7459e1 <+913>: mov %rbx,%rdx
0x000000000c7459e4 <+916>: mov %eax,%ecx
0x000000000c7459e6 <+918>: mov -0x78(%rbp),%rdi
0x000000000c7459ea <+922>: lea -0x98(%rbp),%r8
0x000000000c7459f1 <+929>: mov -0x38(%rbp),%rsi
0x000000000c7459f5 <+933>: callq 0x4b50f00
=> 0x000000000c7459fa <+938>: xor %eax,%eax
0x000000000c7459fc <+940>: mov %ax,(%r15)
0x000000000c745a00 <+944>: lea -0x28(%rbp),%rsp
0x000000000c745a04 <+948>: pop %rbx
0x000000000c745a05 <+949>: pop %r15
0x000000000c745a07 <+951>: pop %r14
0x000000000c745a09 <+953>: pop %r13
0x000000000c745a0b <+955>: pop %r12
0x000000000c745a0d <+957>: pop %rbp
0x000000000c745a0e <+958>: retq
#6 0x0000000005833a3d in spefcmpa ()
#7 0x000000000580df7b in spefmccallstd ()
#8 0x00000000057aed2b in peftrusted ()
#9 0x000000000426b69d in psdexsp ()
#10 0x0000000012de0e84 in rpiswu2 ()
#11 0x000000000384646a in kxe_push_env_internal_pp_ ()
#12 0x00000000038b9675 in kkx_push_env_for_ICD_for_new_session ()
#13 0x000000000426b083 in psdextp ()
#14 0x00000000057a8bb7 in pefccal ()
#15 0x00000000057a846f in pefcal ()
#16 0x000000000566b34b in pevm_FCAL ()
#17 0x000000000564c8ae in pfrinstr_FCAL ()
#18 0x00000000130ea88c in pfrrun_no_tool ()
#19 0x00000000130e91f6 in pfrrun ()
#20 0x00000000130f4dbb in plsql_run ()
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE Test (on Linux)
-- $echo $TZ
-- Europe/Zurich
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE ('DEFAULT_TIMEZONE', 'Europe/Zurich');
select DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME value from dual;
-- 'Europe/Zurich'
select value from dba_scheduler_global_attribute where attribute_name='DEFAULT_TIMEZONE';
-- 'Europe/Zurich'
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE ('DEFAULT_TIMEZONE', 'Europe/London');
select DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME value from dual;
-- 'Europe/Zurich'
select value from dba_scheduler_global_attribute where attribute_name='DEFAULT_TIMEZONE';
-- 'Europe/London'
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE ('DEFAULT_TIMEZONE', NULL);
select DBMS_SCHEDULER.GET_SYS_TIME_ZONE_NAME value from dual;
-- 'Europe/Zurich'
select value from dba_scheduler_global_attribute where attribute_name='DEFAULT_TIMEZONE';
-- null return
5. ORA-01878: specified field not found in datetime or interval
ORA-01878 recurs due to illegal datetime obtained in DST aware Named TZ. Often datetime is initiated by Oracle built-in functions
(sysdate, systimestamp, current_date, current_timestamp, localtimestamp), and then manipulated by arithmetic operators.
All of year application runs without problem except once or twice it gets runtime ORA-01878 during Standard/DST switches,
which is hard to reproduce, and hence held off by wait and hesitate to next switch.
(a). Time Zone Appending
when assigning from TIMESTAMP (or DATE) to TIMESTAMP WITH LOCAL TIME ZONE, default sessiontimezone is appended, and results in a non-exist datetime.
For example,
alter session set time_zone = 'Europe/Paris';
declare
l_date DATE := to_date('2017-03-26 01:52:00', 'yyyy-mm-dd hh24:mi:ss');
l_timestp_ltz TIMESTAMP WITH LOCAL TIME ZONE;
begin
l_timestp_ltz := l_date + 8/1440;
end;
/
ORA-01878: specified field not found in datetime or interval
ORA-06512: at line 5
Note: Clock Changes in Paris, Île-de-France, France 2017
26 Mar 2017 - Daylight Saving Time Started
When local standard time was about to reach
Sunday, 26 March 2017, 02:00:00 clocks were turned forward 1 hour to
Sunday, 26 March 2017, 03:00:00 local daylight time instead.
29 Oct 2017 - Daylight Saving Time Ended
When local daylight time was about to reach
Sunday, 29 October 2017, 03:00:00 clocks were turned backward 1 hour to
Sunday, 29 October 2017, 02:00:00 local standard time instead.
Following test code shows that localtimestamp evolution in expression (on the fly) is session time_zone irrelevant.
However, when it refers to a SQL or PLSQL defined localtimestamp column or variable,
it is appended with session time_zone, i.e, landed on that time_zone, hence has to be validated againt that time_zone.
create table ltz_tab(loc_ltz TIMESTAMP WITH LOCAL TIME ZONE);
insert into ltz_tab values(localtimestamp);
commit;
alter session set time_zone = 'Europe/Paris';
declare
l_intv interval day (6) to second (0) := localtimestamp - timestamp'2017-03-26 02:17:39';
l_ltz_new timestamp with local time zone;
l_val number;
l_boolean boolean;
l_ltz_init timestamp with local time zone;
begin
-- NO ORA-01878 in expression (on the fly)
dbms_output.put_line('ltz calc = '||(localtimestamp - l_intv));
-- ORA-01878 when SQL compare
begin
select 1 into l_val from ltz_tab where loc_ltz > (localtimestamp - l_intv);
exception when others then dbms_output.put_line('ltz SQL compare: '||SQLERRM);
end;
-- ORA-01878 in SQL assigment
begin
select (localtimestamp - l_intv) into l_ltz_new from dual;
exception when others then dbms_output.put_line('ltz SQL assigment: '||SQLERRM);
end;
-- ORA-01878 in PL/SQL compare
begin
l_boolean := l_ltz_new > (localtimestamp - l_intv);
exception when others then dbms_output.put_line('ltz PL/SQL compare: '||SQLERRM);
end;
-- ORA-01878 in PL/SQL assigment
begin
l_ltz_new := (localtimestamp - l_intv);
exception when others then dbms_output.put_line('ltz PL/SQL assigment: '||SQLERRM);
end;
-- workaround-1, using time zone aware variable, '2017-03-26 02:17:39' mapped to '2017-03-26 03:17:39'
l_ltz_init := localtimestamp;
l_ltz_new := l_ltz_init - l_intv;
dbms_output.put_line('ltz (workaround-1) = '||l_ltz_new);
-- workaround-2, using time zone aware function. current_timestamp is localtimestamp with session time zone
l_ltz_new := current_timestamp - l_intv;
dbms_output.put_line('ltz (workaround-2) = '||l_ltz_new);
end;
/
---- Output ----
ltz calc = 2017-03-26 02:17:39
ltz SQL compare: ORA-01878: specified field not found in datetime or interval
ltz SQL assigment: ORA-01878: specified field not found in datetime or interval
ltz PL/SQL compare: ORA-01878: specified field not found in datetime or interval
ltz PL/SQL assigment: ORA-01878: specified field not found in datetime or interval
ltz (workaround-1) = 2017-03-26 03:17:39
ltz (workaround-2) = 2017-03-26 03:17:39
If we make the following 5 tests with time_zone = 'Asia/Singapore',
only the first and last have no errors, all other 3 hit: ORA-01878
since there does not exist Singapore time in interval ['1982-JAN-01 00:00:00', '1982-JAN-01 00:30:00').
(see
Singapore Standard Time)
alter session set time_zone = 'Asia/Singapore';
select to_timestamp_tz('1982-DEC-31 23:59:59', 'YYYY-MON-DD hh24:mi:ss') from dual;
select to_timestamp_tz('1982-JAN-01 00:00:00', 'YYYY-MON-DD hh24:mi:ss') from dual;
select to_timestamp_tz('1982-JAN-01 00:00:01', 'YYYY-MON-DD hh24:mi:ss') from dual;
select to_timestamp_tz('1982-JAN-01 00:29:59', 'YYYY-MON-DD hh24:mi:ss') from dual;
select to_timestamp_tz('1982-JAN-01 00:30:00', 'YYYY-MON-DD hh24:mi:ss') from dual;
In 16 February 1942 (till 11 September 1945), Singapore Time offset was changed from GMT+07:30 to GMT+09:00 (east shift 1 and half hour)
(see https://en.wikipedia.org/wiki/Singapore_Time).
Here some tests:
alter session set time_zone = 'ASIA/Singapore';
--OK
select '-1 second OK', to_timestamp_tz(to_char(date'1942-02-16' - interval '1' second, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS') from dual;
--ERR ORA-01878
select '+0 second ERR', to_timestamp_tz(to_char(date'1942-02-16', 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS.FF') from dual;
select '+1 hour ERR', to_timestamp_tz(to_char(date'1942-02-16' + interval '1' hour, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS') from dual;
select '+89:59 minute to second ERR', to_timestamp_tz(to_char(date'1942-02-16' + interval '89:59' minute to second, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS') from dual;
--OK
select '+90 minute OK', to_timestamp_tz(to_char(date'1942-02-16' + interval '90' minute, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY HH24:MI:SS') from dual;
--No problem if TZNAME are Hong_Kong or Paris
alter session set time_zone = 'Asia/Hong_Kong';
alter session set time_zone = 'Europe/Paris';
We can also look the time difference:
alter session set time_zone = 'ASIA/Singapore';
-- Delta = 07:30:00 in 1942-FEB-15
select
from_tz(timestamp '1942-02-15 10:00:00', 'GMT') London_Time,
from_tz(timestamp '1942-02-15 10:00:00', 'Asia/Singapore') Singapore_Time,
from_tz(timestamp '1942-02-15 10:00:00', 'GMT') - from_tz(timestamp '1942-02-15 10:00:00', 'Asia/Singapore') Delta
from dual;
-- Delta = 09:00:00 in 1942-FEB-16
select
from_tz(timestamp '1942-02-16 10:00:00', 'GMT') London_Time,
from_tz(timestamp '1942-02-16 10:00:00', 'Asia/Singapore') Singapore_Time,
from_tz(timestamp '1942-02-16 10:00:00', 'GMT') - from_tz(timestamp '1942-02-16 10:00:00', 'Asia/Singapore') Delta
from dual;
-- TS_GMT_TZ = 1942-FEB-15 02:30:00 GMT GMT in 1942-FEB-15
select cast(timestamp'1942-02-15 10:00:00' as timestamp with time zone) ts,
cast(timestamp'1942-02-15 10:00:00 Asia/Singapore' at time zone 'GMT' as timestamp) ts_gmt,
cast(timestamp'1942-02-15 10:00:00 Asia/Singapore' at time zone 'GMT' as timestamp with time zone) ts_gmt_tz from dual;
–-TS_GMT_TZ = 1942-FEB-16 01:00:00 GMT GMT in 1942-FEB-16
select cast(timestamp'1942-02-16 10:00:00' as timestamp with time zone) ts,
cast(timestamp'1942-02-16 10:00:00 Asia/Singapore' at time zone 'GMT' as timestamp) ts_gmt,
cast(timestamp'1942-02-16 10:00:00 Asia/Singapore' at time zone 'GMT' as timestamp with time zone) ts_gmt_tz from dual;
(b). Time Zone Converting
When performing TIMESTAMP WITH TIME ZONE arithmetic, implicit timezone conversion is involved. For example,
alter session set time_zone = 'Europe/London';
declare
l_timestp_tz TIMESTAMP WITH TIME ZONE;
l_timestp_tz2 TIMESTAMP WITH TIME ZONE;
begin
l_timestp_tz := to_timestamp_tz('2017-03-26 01:52:00 Europe/Paris', 'yyyy-mm-dd hh24:mi:ss tzr');
dbms_output.put_line('l_timestp_tz = '||l_timestp_tz);
l_timestp_tz2 := l_timestp_tz + numtodsinterval(8, 'Minute'); -- OK
dbms_output.put_line('l_timestp_tz + interval = '||l_timestp_tz2);
l_timestp_tz2 := l_timestp_tz + 8/1440; -- ORA-01878
dbms_output.put_line('l_timestp_tz + number = '||l_timestp_tz2);
end;
/
l_timestp_tz = 2017-MAR-26 01:52:00 EUROPE/PARIS CET
l_timestp_tz + interval = 2017-MAR-26 03:00:00 EUROPE/PARIS CEST
l_timestp_tz + number = 2017-MAR-26 02:00:00 EUROPE/LONDON BST
Run the same code in 'Europe/Paris', hit ORA-01878:
alter session set time_zone = 'Europe/Paris';
declare
l_timestp_tz TIMESTAMP WITH TIME ZONE;
l_timestp_tz2 TIMESTAMP WITH TIME ZONE;
begin
l_timestp_tz := to_timestamp_tz('2017-03-26 01:52:00 Europe/Paris', 'yyyy-mm-dd hh24:mi:ss tzr');
dbms_output.put_line('l_timestp_tz = '||l_timestp_tz);
l_timestp_tz2 := l_timestp_tz + numtodsinterval(8, 'Minute'); -- OK
dbms_output.put_line('l_timestp_tz + interval = '||l_timestp_tz2);
l_timestp_tz2 := l_timestp_tz + 8/1440; -- ORA-01878, line 10
dbms_output.put_line('l_timestp_tz + number = '||l_timestp_tz2);
end;
/
l_timestp_tz = 2017-MAR-26 01:52:00 EUROPE/PARIS CET
l_timestp_tz + interval = 2017-MAR-26 03:00:00 EUROPE/PARIS CEST
ORA-01878: specified field not found in datetime or interval
ORA-06512: at line 10
It looks like that interval arithmetic is performed in natural sense, whereas number arithmetic is a pure math computation.
With Oracle function from_tz, we can construct a TIMESTAMP WITH TIME ZONE value by appending a time zone to a TIMESTAMP value.
Following three tests showed different behaviour of Plsql vs Sql
(Note that outer from_tz is a wrong usage since inner from_tz returns datatype TIMESTAMP WITH TIME ZONE, not datatype TIMESTAMP).
alter session set time_zone = 'Europe/Paris';
select sessiontimezone, dbtimezone from dual;
SESSIONTIMEZONE DBTIME
--------------- ------
Europe/Paris +01:00
begin
dbms_output.put_line(
from_tz(from_tz(timestamp'2021-03-28 02:00:00', 'UTC'), sessiontimezone)
);
end;
/
ORA-01878: specified field not found in datetime or interval
ORA-06512: at line 2
select
from_tz(from_tz(timestamp'2021-03-28 02:00:00', 'UTC'), sessiontimezone)
from dual;
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected TIMESTAMP got TIMESTAMP WITH TIME ZONE
with function func_ret (p_ts timestamp) return timestamp with time zone as
begin
return from_tz(from_tz(p_ts, 'UTC'), sessiontimezone);
end;
select func_ret(timestamp'2021-03-28 02:00:00')
from dual
/
ORA-01878: specified field not found in datetime or interval
ORA-06512: at line 5
By the way, tzname 'CET' and 'UTC' are different in respect of DST. In 'CET', Daylight Saving Time (DST) is in effect (DST aware);
but in 'UTC', Daylight Saving Time has never been used (Not DST aware), so 'UTC' is the same as Offset TZ (00:00).
select tzname, tzabbrev from v$timezone_names where tzname in ('CET', 'UTC');
TZNAME TZABBREV
---------- ----------
CET LMT
CET CEST
CET CET
UTC GMT
Time Zone "Z" (Zulu Time Zone, Offset: TZ 00:00) is the same as "UTC" (DST unaware), but it is an nvalid timezone region in Oracle.
SQL > select to_timestamp_tz('2017-03-26 01:52:00 Z', 'YYYY-MM-DD HH24:MI:SS TZR') from dual;
26-MAR-2017 01:52:00 +00:00
SQL > alter session set time_zone = 'Z';
ERROR:
ORA-01882: timezone region not found
In the following test, we can see Plsql CASE statement raising ORA-01830 special behaviour when returning mixed data types (date and varchar2).
-- Test Code
declare
l_date date;
l_ret varchar2(32000);
begin
l_date := date'1982-11-22';
dbms_output.put_line('l_date = '||to_char(l_date, 'DD-MON-YYYY HH24:MI:SS'));
-- CASE Statement Breaking down: OK
l_ret := l_date;
l_ret := to_char(l_date, 'DD-MON-YYYY HH24:MI:SS');
-- CASE 1: OK
l_ret := case when 1 = 1
then l_date
else to_char(l_date, 'DD-MON-YYYY HH24:MI:SS')
end;
dbms_output.put_line('Case 1: l_date = '||to_char(l_date, 'DD-MON-YYYY HH24:MI:SS'));
-- CASE 2: OK
l_ret := case when 1 = 2
then to_char(l_date, 'DD-MON-YYYY HH24:MI:SS')
else l_date
end;
dbms_output.put_line('Case 2: l_date = '||to_char(l_date, 'DD-MON-YYYY HH24:MI:SS'));
-- CASE 3: NOK
begin
l_ret := case when 1 = 1
then to_char(l_date, 'DD-MON-YYYY HH24:MI:SS')
else l_date
end;
exception when others then dbms_output.put_line('Case 3: '||SQLERRM);
end;
-- CASE 4: NOK
begin
l_ret := case when 1 = 2
then l_date
else to_char(l_date, 'DD-MON-YYYY HH24:MI:SS')
end;
exception when others then dbms_output.put_line('Case 4: '||SQLERRM);
end;
end;
/
-- Output
l_date = 22-NOV-1982 00:00:00
Case 1: l_date = 22-NOV-1982 00:00:00
Case 2: l_date = 22-NOV-1982 00:00:00
Case 3: ORA-01830: date format picture ends before converting entire input string
Case 4: ORA-01830: date format picture ends before converting entire input string
ORA-01878 in LOCALTIMESTAMP Calculations (Updated 2026-09-03)
--Europe/Paris Year 2026: Start of DST (Spring Forward): Sunday, March 29, 2026, at 02:00 AM local time.
--Clocks moved forward 1 hour to 03:00 AM (shifting from UTC+1 to UTC+2).
-- Set time_zone as Named TZ (TZR) and use current_timestamp instead of localtimestamp, also fix ORA-01878
drop table test_ltz;
create table test_ltz (id number, timestamp timestamp(6) with local time zone);
insert into test_ltz values (1, localtimestamp);
commit;
declare
l_dst_ts timestamp := timestamp'2026-03-29 03:01:14 Europe/Paris';
l_interval_dst INTERVAL DAY(4) TO SECOND(3);
l_interval_dst_15m INTERVAL DAY(4) TO SECOND(3);
l_ltz_current timestamp with local time zone;
l_ltz_calc_indirect timestamp with local time zone;
l_ltz_calc_direct timestamp with local time zone;
begin
l_ltz_current := localtimestamp;
l_interval_dst := l_ltz_current - l_dst_ts;
dbms_output.put_line('l_interval_from_dst = '||l_interval_dst);
l_interval_dst_15m := l_interval_dst + numtodsinterval(15, 'Minute');
dbms_output.put_line('l_interval_dst_15m = '||l_interval_dst_15m);
-- l_ltz_current is with timezone info, calculation with timezone info
-- No ORA-01878, possible fix
l_ltz_calc_indirect := l_ltz_current - l_interval_dst_15m;
dbms_output.put_line('l_ltz_calc_indirect 1 = '||l_ltz_calc_indirect);
l_ltz_calc_indirect := l_ltz_current - l_interval_dst_15m - interval '1' Hour;
dbms_output.put_line('l_ltz_calc_indirect 2 = '||l_ltz_calc_indirect);
-- No ORA-01878 if 1 hour earlier
l_ltz_calc_direct := localtimestamp - l_interval_dst_15m - interval '1' Hour;
dbms_output.put_line('l_ltz_calc_direct 1 = '||l_ltz_calc_direct);
-- LOCALTIMESTAMP is datatype TIMESTAMP (without timezone)
-- At first, Calculation without timezone info, then convert as TIMESTAMP WITH LOCAL TIME ZONE (with implicit sessiontimezone as default TimeZone)
-- Hit ORA-01878: specified field not found in datetime or interval
l_ltz_calc_direct := localtimestamp - l_interval_dst_15m;
dbms_output.put_line('l_ltz_calc_direct 2 = '||l_ltz_calc_direct);
end;
/
---- Output----
l_interval_from_dst = +0158 11:20:31.794
l_interval_dst_15m = +0158 11:35:31.794
l_ltz_calc_indirect 1 = 29-MAR-2026 03:46:13
l_ltz_calc_indirect 2 = 29-MAR-2026 01:46:13
l_ltz_calc_direct 1 = 29-MAR-2026 01:46:14
declare
*
ERROR at line 1:
ORA-01878: specified field not found in datetime or interval
ORA-06512: at line 31
declare
l_dst_ts timestamp := timestamp'2017-03-26 03:00:06 Europe/Paris';
l_interval_dst INTERVAL DAY(4) TO SECOND(3);
l_interval_dst_10s INTERVAL DAY(4) TO SECOND(3);
l_ltz_current timestamp with local time zone;
l_ltz_calc_indirect timestamp with local time zone;
l_ltz_calc_direct timestamp with local time zone;
begin
l_ltz_current := localtimestamp;
l_interval_dst := l_ltz_current - l_dst_ts;
dbms_output.put_line('l_interval_from_dst = '||l_interval_dst);
l_interval_dst_10s := l_interval_dst + numtodsinterval(10, 'Second');
dbms_output.put_line('l_interval_dst_10s = '||l_interval_dst_10s);
-- l_ltz_current is with timezone info, calculation with timezone info
-- No ORA-01878
for c in (select * from test_ltz where timestamp < l_ltz_current - l_interval_dst_10s)
loop
dbms_output.put_line('timestamp = '||c.timestamp);
end loop;
-- LOCALTIMESTAMP is datatype TIMESTAMP (without timezone)
-- At first, Calculation without timezone info, then convert as TIMESTAMP WITH LOCAL TIME ZONE (with implicit sessiontimezone as default TimeZone)
-- Hit ORA-01878: specified field not found in datetime or interval
for c in (select * from test_ltz where timestamp < localtimestamp - l_interval_dst_10s)
loop
dbms_output.put_line('timestamp = '||c.timestamp);
end loop;
end;
/
---- Output----
l_interval_from_dst = +3448 11:22:09.292
l_interval_dst_10s = +3448 11:22:19.292
declare
*
ERROR at line 1:
ORA-01878: specified field not found in datetime or interval
ORA-06512: at line 26
ORA-06512: at line 26
