Local function for manipulating field value-CodePudding

I need to convert a bunch of “dates” into a ISO8601 timestamp in the time zone assigned per record.

What I have that is working now is this:

TO_CHAR(TO_TIMESTAMP(TO_CHAR(mp.creation_date, 'YYYY-MM-DD HH24:MI:SS')) AT TIME ZONE WL.TIMEZONE_CODE, 'YYYY-MM-DD"T"HH24:MI:SS.ff3TZH:TZM')

This is fine, but have to do it on 20 fields.

So I was wondering if it’s possible to create a local function, named something like TO_ISO8601(timestamp, time_zone). Almost like Common Table Expressions.

CodePudding user response：

If I understood you correctly, this is what you have now (though, simplified somewhat so that you'd avoid (unnecessary) datatype conversions):

SQL> with
  2  mp (creation_date) as
  3    (select to_date('2022-11-19 10:17:32', 'yyyy-mm-dd hh24:mi:ss') from dual),
  4  wl (timezone_code) as
  5    (select 'Europe/Zagreb' from dual)
  6  select to_char(cast(creation_date as timestamp) at time zone wl.timezone_code,
  7                 'yyyy-mm-dd"T"hh24:mi:ss.ff3TZH:TZM') result
  8  from mp cross join wl;

RESULT
-----------------------------
2022-11-19T10:17:32.000 01:00

If you create a function that accepts date value and timezone code:

SQL> create or replace function to_iso8601
  2    (par_value in date, par_time_zone in varchar2)
  3  return varchar2
  4  is
  5  begin
  6    return to_char(cast(par_value as timestamp) at time zone par_time_zone,
  7                 'yyyy-mm-dd"T"hh24:mi:ss.ff3TZH:TZM');
  8  end;
  9  /

Function created.

you'd then use it as

SQL> with
  2  mp (creation_date) as
  3    (select to_date('2022-11-19 10:17:32', 'yyyy-mm-dd hh24:mi:ss') from dual),
  4  wl (timezone_code) as
  5    (select 'Europe/Zagreb' from dual)
  6  select to_iso8601(mp.creation_date, wl.timezone_code) as creation_date
  7  from mp cross join wl;

CREATION_DATE
--------------------------------------------------------------------------------
2022-11-19T10:17:32.000 01:00

SQL>

If you have a read-only access, then you won't be able to create a function. But, CTE (as you mentioned) comes into rescue!

SQL> with
  2  function to_iso8601
  3    (par_value in date, par_time_zone in varchar2)
  4    return varchar2
  5    is
  6    begin
  7      return to_char(cast(par_value as timestamp) at time zone par_time_zone,
  8                   'yyyy-mm-dd"T"hh24:mi:ss.ff3TZH:TZM');
  9    end;
 10  --
 11  mp (creation_date) as
 12    (select to_date('2022-11-19 10:17:32', 'yyyy-mm-dd hh24:mi:ss') from dual),
 13  wl (timezone_code) as
 14    (select 'Europe/Zagreb' from dual)
 15  select to_iso8601(mp.creation_date, wl.timezone_code) result
 16  from mp cross join wl;
 17  /

RESULT
--------------------------------------------------------------------------------
2022-11-19T10:17:32.000 01:00

SQL>