PLSQL XML datetime to timestamp
-
function getTIMESTAMPFROMVARCHAR(p_time in VARCHAR2) return timestamp
-
is
-
l_time varchar2(255) := p_time;
-
begin
-
if (upper(substr(p_time,length(l_time),1))='Z') then
-
l_time := substr(p_time,0,length(l_time)-1) || '+00:00';
-
end if;
-
-
if (length(l_time)=25) then --2015-04-14T13:28:09+01:00
-
return to_timestamp_tz(translate(l_time,'T',' '),'YYYY-MM-DD HH24:MI:SSTZH:TZM');
-
end if;
-
if (length(l_time)=26) then --2015-10-19T14:01:13.861836
-
return to_timestamp_tz(translate(l_time,'T',' '),'YYYY-MM-DD HH24:MI:SS.FF9');
-
end if;
-
if (length(l_time)=28) then --2015-04-14T13:28:09.40+01:00
-
return to_timestamp_tz(translate(l_time,'T',' '),'YYYY-MM-DD HH24:MI:SS.FF2TZH:TZM');
-
end if;
-
if (length(l_time)=29) then
-
if (substr(l_time,20,1)='.') then
-
return to_timestamp_tz(translate(l_time,'T',' '),'YYYY-MM-DD HH24:MI:SS.FF9');
-
else --2015-04-14T13:28:09.401+01:00
-
return to_timestamp_tz(translate(l_time,'T',' '),'YYYY-MM-DD HH24:MI:SS.FF3TZH:TZM');
-
end if;
-
end if;
-
raise_application_error(-20000, 'Invalid Date Format - ' || l_time);
-
end;
timezone dst
Other Stuff I found on code2
-
select DBTIMEZONE
-
from dual;
-
-
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'), DBTIMEZONE, SESSIONTIMEZONE, TZ_OFFSET( SESSIONTIMEZONE ),localtimestamp from dual;
-
-
ALTER SESSION SET TIME_ZONE = '+0:0';
-
ALTER SESSION SET TIME_ZONE = 'Europe/London';
-
-
-
select
-
to_char(r.lastrundatetime,'DD-MON-YYYY HH24:MI:SS'),
-
to_timestamp_tz(r.lastrundatetime),
-
r.*
-
from sympscanpub_runlog r
-
where sympscanpub_runlog_id in (43,1)
-
order by lastrundatetime asc
-
;
-
-
select systimestamp at time zone 'UTC' current_time_in_utc from dual;
-
-
-
select sysdate
-
from dual;
-
-
-
select
-
systimestamp,
-
systimestamp at time zone '+1:0'
-
from dual;
-
-
create or replace function GSBTEST_1 return timestamp
-
is
-
begin
-
--X_LASTRUNDATETIME := sysdate;
-
--X_LASTRUNDATETIME_TZ := sysdate;
-
--X_LASTRUNDATETIME := systimestamp;
-
--X_LASTRUNDATETIME_TZ := systimestamp;
-
--X_LASTRUNDATETIME_TZ := systimestamp at time zone 'Europe/London';
-
--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';
-
--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';
-
return systimestamp;
-
end;
-
/
-
-
create or replace function GSBTEST_2 return timestamp with time zone
-
is
-
begin
-
--X_LASTRUNDATETIME := sysdate;
-
--X_LASTRUNDATETIME_TZ := sysdate;
-
--X_LASTRUNDATETIME := systimestamp;
-
--X_LASTRUNDATETIME_TZ := systimestamp;
-
--X_LASTRUNDATETIME_TZ := systimestamp at time zone 'Europe/London';
-
--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';
-
--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';
-
return systimestamp;
-
end;
-
/
-
-
select GSBTEST_1, GSBTEST_2 from dual;
-
-
select XXIC_SYMPSCANPUB_HWM.RJMTEST() from dual;
-
-
create table gsb ( a timestamp, b timestamp with time zone, c timestamp with local time zone);
-
-
-
insert into gsb values ( systimestamp, systimestamp, systimestamp);
-
commit;
-
-
ALTER SESSION SET TIME_ZONE = 'UTC';
-
select * from gsb;
-
select c at time zone 'Europe/Madrid' from gsb;
-
-
-
create or replace function GSBTEST_1 ( input in date) return timestamp with time zone
-
is
-
begin
-
--X_LASTRUNDATETIME := sysdate;
-
--X_LASTRUNDATETIME_TZ := sysdate;
-
--X_LASTRUNDATETIME := systimestamp;
-
--X_LASTRUNDATETIME_TZ := systimestamp;
-
--X_LASTRUNDATETIME_TZ := systimestamp at time zone 'Europe/London';
-
--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';
-
--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';
-
return to_timestamp_tz(input) at time zone 'Europe/London';
-
end;
-
/
-
-
select gsbtest_1(to_date('21-oct-15 00:00:00','DD-MON-YY HH24:MI:SS')) from dual;
-
-
-
select from_tz(to_timestamp('25-oct-15 00:59:00','DD-MON-YY HH24:MI:SS'),'Europe/London') Simple
-
,from_tz(to_timestamp('25-oct-15 00:59:00','DD-MON-YY HH24:MI:SS'),'Europe/London') at time zone 'UTC' UTC
-
,from_tz(to_timestamp('25-oct-15 00:59:00','DD-MON-YY HH24:MI:SS'),'Europe/London') at time zone 'Europe/London' London
-
--,from_tz(to_timestamp('25-oct-15 00:00:00','DD-MON-YY HH24:MI:SS'),'Europe/London') at time zone 'Europe/Madrid' Madrid
-
from dual;
-
-
select sf.base at time zone 'UTC', sf.base at time zone 'Europe/London'
-
from (
-
select from_tz(to_timestamp('25-oct-15 00:10:00','DD-MON-YY HH24:MI:SS'),'Europe/London') as base from dual
-
union all select from_tz(to_timestamp('25-oct-15 00:20:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
-
union all select from_tz(to_timestamp('25-oct-15 00:30:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
-
union all select from_tz(to_timestamp('25-oct-15 00:40:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
-
union all select from_tz(to_timestamp('25-oct-15 00:50:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
-
union all select from_tz(to_timestamp('25-oct-15 01:00:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
-
union all select from_tz(to_timestamp('25-oct-15 01:10:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
-
union all select from_tz(to_timestamp('25-oct-15 01:20:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
-
union all select from_tz(to_timestamp('25-oct-15 01:30:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
-
union all select from_tz(to_timestamp('25-oct-15 01:40:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
-
union all select from_tz(to_timestamp('25-oct-15 01:50:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
-
union all select from_tz(to_timestamp('25-oct-15 02:00:00','DD-MON-YY HH24:MI:SS'),'Europe/London') from dual
-
) sf
-
;
RJM Article Type
Quick Reference