Forum OpenACS Q&A: acs-lang / ref-timezones issues

Hi folks,

What originally started as an urge to try and see if I could get OpenACS Localization Utils (aka acs-lang) to work, or at least understand it better, led me to a question regarding ref-timezones...

acs-lang is dependent upon ref-timezones and is calling timezone__local_to_utc (among other, perhaps). This function as well as timezone__utc_to_local hasn't been ported yet, it seems.

Would someone be willing to help port this query (unclear areas in bold)?

ORACLE:

    function local_to_utc (
         tz_id      in timezones.tz_id%TYPE,
         local_time in date
    ) return date
    is
         utc_time date;
    begin
		select local_time - gmt_offset/86400 into 
utc_time
         from   timezone_rules
		where  tz_id = local_to_utc.tz_id
         and    local_time between local_start and local_end
         and    rownum = 1;

	     return utc_time;
 exception
         when no_data_found then
              return local_time;
    end;

My attempt looks like this, so far:

POSTGRESQL:

create function timezone__local_to_utc (integer, timestamp) returns 
timestamp as '
declare
  p_tz_id alias for $1;
  p_local_time for $2;
  v_utc_time timestamptz;
begin
         select p_local_time - gmt_offset/86400 into v_utc_time
         from   timezone_rules
         where  tz_id  = p_tz_id
		and    p_local_time between local_start and 
local_end
         limit 1;
              return v_utc_time

  ??? exception
         when no_data_found then ???
              return p_local_time;


end;' language 'plpgsql';

The "gmt_offset" column is of type "numeric" in Oracle but "text"(??) in PG. This causes an error when running the query in PG while dividing gmt_offset with 86400... What type to use? Thanks,