PLSQL Date/Timezone Notes

Submitted by code_admin on Fri, 07/20/2018 - 16:10

PLSQL XML datetime to timestamp

  1. function getTIMESTAMPFROMVARCHAR(p_time in VARCHAR2) return timestamp
  2. is
  3.   l_time varchar2(255) := p_time;
  4. begin
  5.   if (upper(substr(p_time,length(l_time),1))='Z') then
  6.     l_time := substr(p_time,0,length(l_time)-1) || '+00:00';
  7.   end if;
  8.  
  9.   if (length(l_time)=25) then --2015-04-14T13:28:09+01:00
  10.     return to_timestamp_tz(translate(l_time,'T',' '),'YYYY-MM-DD HH24:MI:SSTZH:TZM');
  11.   end if;
  12.   if (length(l_time)=26) then --2015-10-19T14:01:13.861836
  13.     return to_timestamp_tz(translate(l_time,'T',' '),'YYYY-MM-DD HH24:MI:SS.FF9');
  14.   end if;
  15.   if (length(l_time)=28) then --2015-04-14T13:28:09.40+01:00
  16.     return to_timestamp_tz(translate(l_time,'T',' '),'YYYY-MM-DD HH24:MI:SS.FF2TZH:TZM');
  17.   end if;
  18.   if (length(l_time)=29) then
  19.       if (substr(l_time,20,1)='.') then
  20.        return to_timestamp_tz(translate(l_time,'T',' '),'YYYY-MM-DD HH24:MI:SS.FF9');
  21.       else --2015-04-14T13:28:09.401+01:00
  22.         return to_timestamp_tz(translate(l_time,'T',' '),'YYYY-MM-DD HH24:MI:SS.FF3TZH:TZM');
  23.       end if;
  24.   end if;
  25.   raise_application_error(-20000, 'Invalid Date Format - ' || l_time);      
  26. end;

timezone dst

Other Stuff I found on code2

  1. select DBTIMEZONE
  2. from dual;
  3.  
  4. select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'), DBTIMEZONE, SESSIONTIMEZONE, TZ_OFFSET( SESSIONTIMEZONE ),localtimestamp from dual;
  5.  
  6. ALTER SESSION SET TIME_ZONE = '+0:0';
  7. ALTER SESSION SET TIME_ZONE = 'Europe/London';
  8.  
  9.  
  10. select
  11.   to_char(r.lastrundatetime,'DD-MON-YYYY HH24:MI:SS'),
  12.   to_timestamp_tz(r.lastrundatetime),
  13.   r.*
  14. from sympscanpub_runlog r
  15. where sympscanpub_runlog_id in (43,1)
  16. order by lastrundatetime asc
  17. ;
  18.  
  19. select systimestamp at time zone 'UTC' current_time_in_utc from dual;
  20.  
  21.  
  22. select sysdate
  23. from dual;
  24.  
  25.  
  26. select
  27.   systimestamp,
  28.   systimestamp at time zone '+1:0'
  29. from dual;
  30.  
  31. create or replace function GSBTEST_1 return timestamp
  32. is
  33. begin
  34.   --X_LASTRUNDATETIME := sysdate;
  35.   --X_LASTRUNDATETIME_TZ := sysdate;
  36.   --X_LASTRUNDATETIME := systimestamp;
  37.   --X_LASTRUNDATETIME_TZ := systimestamp;
  38.   --X_LASTRUNDATETIME_TZ := systimestamp at time zone 'Europe/London';
  39.   --X_LASTRUNDATETIME_TZ := to_timestamp_tz(to_date('01-JAN-2015 11:11:11','DD-MON-YYYY HH24:MI:SS')) at time zone 'Europe/London';
  40.   --X_LASTRUNDATETIME_TZ := to_timestamp_tz(to_date('01-AUG-2015 11:11:11','DD-MON-YYYY HH24:MI:SS')) at time zone 'Europe/London';
  41.   return systimestamp;
  42. end;
  43. /
  44.  
  45. create or replace function GSBTEST_2 return timestamp with time zone
  46. is
  47. begin
  48.   --X_LASTRUNDATETIME := sysdate;
  49.   --X_LASTRUNDATETIME_TZ := sysdate;
  50.   --X_LASTRUNDATETIME := systimestamp;
  51.   --X_LASTRUNDATETIME_TZ := systimestamp;
  52.   --X_LASTRUNDATETIME_TZ := systimestamp at time zone 'Europe/London';
  53.   --X_LASTRUNDATETIME_TZ := to_timestamp_tz(to_date('01-JAN-2015 11:11:11','DD-MON-YYYY HH24:MI:SS')) at time zone 'Europe/London';
  54.   --X_LASTRUNDATETIME_TZ := to_timestamp_tz(to_date('01-AUG-2015 11:11:11','DD-MON-YYYY HH24:MI:SS')) at time zone 'Europe/London';
  55.   return systimestamp;
  56. end;
  57. /
  58.  
  59. select GSBTEST_1, GSBTEST_2 from dual;
  60.  
  61. select XXIC_SYMPSCANPUB_HWM.RJMTEST() from dual;
  62.  
  63. create table gsb ( a timestamp, b timestamp with time zone, c timestamp with local time zone);
  64.  
  65.  
  66. insert into gsb values ( systimestamp, systimestamp, systimestamp);
  67. commit;
  68.  
  69. ALTER SESSION SET TIME_ZONE = 'UTC';
  70. select * from gsb;
  71. select c at time zone 'Europe/Madrid' from gsb;
  72.  
  73.  
  74. create or replace function GSBTEST_1 ( input in date) return timestamp with time zone
  75. is
  76. begin
  77.   --X_LASTRUNDATETIME := sysdate;
  78.   --X_LASTRUNDATETIME_TZ := sysdate;
  79.   --X_LASTRUNDATETIME := systimestamp;
  80.   --X_LASTRUNDATETIME_TZ := systimestamp;
  81.   --X_LASTRUNDATETIME_TZ := systimestamp at time zone 'Europe/London';
  82.   --X_LASTRUNDATETIME_TZ := to_timestamp_tz(to_date('01-JAN-2015 11:11:11','DD-MON-YYYY HH24:MI:SS')) at time zone 'Europe/London';
  83.   --X_LASTRUNDATETIME_TZ := to_timestamp_tz(to_date('01-AUG-2015 11:11:11','DD-MON-YYYY HH24:MI:SS')) at time zone 'Europe/London';
  84.   return to_timestamp_tz(input) at time zone 'Europe/London';
  85. end;
  86. /
  87.  
  88. select gsbtest_1(to_date('21-oct-15 00:00:00','DD-MON-YY HH24:MI:SS')) from dual;
  89.  
  90.  
  91. select from_tz(to_timestamp('25-oct-15 00:59:00','DD-MON-YY HH24:MI:SS'),'Europe/London') Simple
  92. ,from_tz(to_timestamp('25-oct-15 00:59:00','DD-MON-YY HH24:MI:SS'),'Europe/London') at time zone 'UTC' UTC
  93. ,from_tz(to_timestamp('25-oct-15 00:59:00','DD-MON-YY HH24:MI:SS'),'Europe/London') at time zone 'Europe/London' London
  94. --,from_tz(to_timestamp('25-oct-15 00:00:00','DD-MON-YY HH24:MI:SS'),'Europe/London') at time zone 'Europe/Madrid' Madrid
  95. from dual;
  96.  
  97. select sf.base at time zone 'UTC', sf.base at time zone 'Europe/London'
  98. from (
  99. select from_tz(to_timestamp('25-oct-15 00:10:00','DD-MON-YY HH24:MI:SS'),'Europe/London') as base from dual
  100. union all select from_tz(to_timestamp('25-oct-15 00:20:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
  101. union all select from_tz(to_timestamp('25-oct-15 00:30:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
  102. union all select from_tz(to_timestamp('25-oct-15 00:40:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
  103. union all select from_tz(to_timestamp('25-oct-15 00:50:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
  104. union all select from_tz(to_timestamp('25-oct-15 01:00:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
  105. union all select from_tz(to_timestamp('25-oct-15 01:10:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
  106. union all select from_tz(to_timestamp('25-oct-15 01:20:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
  107. union all select from_tz(to_timestamp('25-oct-15 01:30:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
  108. union all select from_tz(to_timestamp('25-oct-15 01:40:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
  109. union all select from_tz(to_timestamp('25-oct-15 01:50:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
  110. union all select from_tz(to_timestamp('25-oct-15 02:00:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
  111. ) sf
  112. ;

Tags

RJM Article Type
Quick Reference