Thursday, 17 April 2014

Times at specific time zones

These past couple of weeks, I have been doing some work with some external APIs, so have had to do some time stamp manipulation. Here are some tips I've learnt along the way.

A quick way to get UTC time, is with the function: sys_extract_utc. With that, we can quickly get the UTC timestamp. Here is an example to return the UTC time in RFC3399/ISO8601 format:

  to_char(
    sys_extract_utc(systimestamp)
  , 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'
  )

To return that back into local time, you want to declare a variable with time zone support. So just say the string you have is: 2014-04-17T02:46:16.607Z, you'll want to declare it with a time zone attribute.

l_time timestamp with time zone;

Since Z refers +00:00, you need to set the time zone. If you call to_timezone or to_timezone_tz, without specifying the timezone, it will create the time in the local time zone, so we need to specifically set it with from_tz.

Either:

l_time := to_timestamp_tz('2014-04-17T02:46:16.607Z' || '+00:00', 'yyyy-mm-dd"T"hh24:mi.ss.ff3"Z"tzh:tzm');

(Appending the date time string with the time zone info)

Or:

l_time := to_timestamp('2014-04-17T02:46:16.607Z', 'yyyy-mm-dd"T"hh24:mi.ss.ff3"Z"');
l_time := from_tz(l_time, '+00:00');

(Setting the time zone after the fact)

Finally, to get the time stamp outputting in your local time zone, you can do:

l_time := l_time at local;

(Converting it to the time zone of the database)

Or, specifically set what time zone to convert it to:

l_time := l_time at time zone '+10:00';
l_time := l_time at time zone sessiontimezone;
l_time := l_time at time zone 'Australia/Sydney';

Where the time zone string can be a valid time zone name as from the v$timezone_names view; or the GMT offset, as per the example above.

If all you want to do is get it in the local time zone of the database, you can declare the time stamp with local time zone, and this will automatically output the time in the databases local time zone.

declare
  l_timestamp timestamp with local time zone;
begin

  l_timestamp := to_timestamp('2014-04-17T02:46:16.607Z', 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"');
  l_timestamp := from_tz(l_timestamp, '+00:00');

  dbms_output.put_line(l_timestamp );

end;

This seems like as good a time as any to spruik the fact that Application Express comes with support for local time stamps since version 4. You can read more about that here: http://joelkallman.blogspot.com.au/2010/09/automatic-time-zone-support-in.html

Resources

http://blog.watashii.com/2009/11/oracle-timezone-conversions-gmt-to-localtime/
http://orastory.wordpress.com/2007/05/15/dates-timestamps-and-iso-8601/
http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm

Thursday, 3 April 2014

Getting around large exports with RTF report templates

If you are on a slow connection like me, you may find how much of a pain it is deploying apps that house some BI publisher report templates. It only takes a few templates before your app export becomes 10MB+ in size.

Perhaps in a future release, report templates will be treated in the same way as application images, in that you can add them as a script to supporting objects so that you don't need to export the templates each and every deployment.

If you haven't seen, apex_util has a procedure to download report queries, so by using that procedure coupled with storing your files in your own maintained table, you can avoid the issue of massive application exports.

First create a table to store the templates:

create table rtf_template(
    code varchar2(25) PRIMARY KEY
  , layout BLOB);
/


Then, add your template file to the table.

We will need two application items (or 1 will do if you identify the layout with exactly the same name as the query). So I've created: REPORT_NAME and REPORT_LAYOUT

Then create an on demand process, which I've named mine as: PRINT_DOC. The code looks like this:


declare

    l_file_as_clob CLOB ;
    l_layout BLOB;
    l_asdsad CLOB;

    l_dest_offset integer := 1;
    l_src_offset integer := 1;
    l_lang_context integer := dbms_lob.default_lang_ctx;
    l_warning integer;
BEGIN


    select layout into l_layout
    from rtf_template
    where code = :REPORT_LAYOUT;
    
    dbms_lob.createtemporary(lob_loc => l_file_as_clob, cache => false);
    
    dbms_lob.converttoclob(
        dest_lob => l_file_as_clob
      , src_blob => l_layout
      , amount => dbms_lob.lobmaxsize
      , dest_offset => l_dest_offset
      , src_offset => l_src_offset
      , blob_csid => dbms_lob.default_csid
      , lang_context => l_lang_context
      , warning => l_warning);


    l_file_as_clob:= replace(l_file_as_clob,'+','%2B');
    l_file_as_clob:= replace(l_file_as_clob,'/','%2F');
    l_file_as_clob:= replace(l_file_as_clob,'=','%3D');

    --signature 2
    apex_util.download_print_document (
        p_file_name => :REPORT_NAME
      , p_content_disposition => 'ATTACHMENT'
      , p_application_id => :APP_ID
      , p_report_query_name => :REPORT_NAME
      , p_report_layout => l_file_as_clob
      , p_report_layout_type => 'rtf'
      , p_document_format => 'pdf'
    );
    
END;


Now, when ever you want a button to download a layout, you can just past the request parameter of: APPLICATION_PROCESS=PRINT_DOC, then set the two (or one) page items accordingly. A little like this:



It is a little extra work, but if it saves some headaches with deployments, why not ;-)