--
-- timezone__convert_to_utc/2
--
create or replace function timezone__convert_to_utc(
  p_tz_id integer,
  p_local_varchar character varying
) returns timestamptz as $$

DECLARE
  v_base_time timestamptz;
BEGIN

  select "timestamptz" (p_local_varchar || substr(gmt_offset,1,5)) into v_base_time
  from timezones
  where tz_id = p_tz_id;

  if not found then
    return "timestamptz" (p_local_varchar || '+00');
  end if;

  return "timestamptz" (p_local_varchar) - "interval" (gmt_offset || 'seconds')
  from   timezone_rules
  where  tz_id = p_tz_id and v_base_time between utc_start and utc_end;

END;
$$ language plpgsql;