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
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
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';
(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
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
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.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());
}
}
(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.
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
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"
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.
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
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;
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';
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;
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
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
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
select tzname, tzabbrev from v$timezone_names where tzname in ('CET', 'UTC');
TZNAME TZABBREV
---------- ----------
CET LMT
CET CEST
CET CET
UTC GMT