--
-- timezone__get_date/4
--
create or replace function timezone__get_date(
  p_tz_id integer,
  p_timestamp timestamp with time zone,
  p_format character varying,
  p_append_timezone_p boolean
) returns varchar as $$

DECLARE
  v_timestamp timestamptz;
  v_abbrev text;
  v_date text;
BEGIN

  v_abbrev := '';
  if p_append_timezone_p then
    select abbrev into v_abbrev
    from   timezone_rules
    where  tz_id = p_tz_id and p_timestamp between utc_start and utc_end;
  end if;

  select to_char(p_timestamp + "interval" (
     (extract(timezone_hour from p_timestamp) * 3600 + extract(timezone_minute from p_timestamp) * 60) || 'seconds') +
         "interval" (gmt_offset || 'seconds'), p_format) || ' ' || v_abbrev
    into v_date 
  from   timezone_rules
  where  tz_id = p_tz_id and p_timestamp between utc_start and utc_end;

  if not found then
    select to_char(p_timestamp + "interval" ((extract(timezone_hour from p_timestamp) * 3600 + extract(timezone_minute from p_timestamp) * 60) || 'seconds'), p_format)
      into v_date;
  end if;

  return v_date;

END;
$$ language plpgsql;